Excel: 16 Connecting Worksheets, Workbooks, and External Data

Setting Up SQL Server, XML, OLE DB, and ODBC, Connections

Can connect with a variety of data sources from the From Other Sources icon on the Data tab. When you choose this option, you are presented with five choices. • SQL Server is Microsoft's structured query language database. This runs more smoothly when the data set is to big to be runned with Excel and Access. To connect, you need the Server name, a userid and a password. • Analysis Services is Mircosoft's cube functionality currently marketed as SQL Server Analysis Services. A cube database represents data along three or more dimensions. • XML stands for Extensible Markup Language. This is a simple text file that includes both data and tags used to identify the data. • OLE DB stands for Object Linking and Embedding for Database. This is a Microsoft interface written in COM environment that allows Windows-based applications to access a variety of database types. • Microsoft Query is an older technology that used Open Database Connectivity. If your company has implemented a nonMicrosoft platform, ODBC is the interface that allows other programs such as Excel to connect to the database. You must have a connect string in order to access the other system's data. The Microsoft Query also provides a method for Excel to build SQL queries against Access database. A. Connecting to XML Data CSV Comma-separated value You can create and edit XML files by using Notepad because each field (<fieldname> and </fieldname>tags) in the XML file contains a field identifier. This allows you to intelligently import only certain fields into the spreadsheet. XSD file is a definition document You may have more than one file in XSL that are used to transform the data With the VBA Editor, you can retrieve the schema and save it to allow Excel to do more XML tricks: • Click the VBA Editor icon on the Developer tab, then the VBA Editor appears • In the VBA Editor, press Ctrl + G to display the Immediate pane • In the plane, type this line: Print activeworkbook.XmlMaps(1)Schemas(1).XML • Copy the text into Notepad file and save as test.xsd in the same directory as the XML file to enable additional XML features • You will have your result with WordMap turned on. B. Connecting Using Microsoft Query The From Access icon allows you to retrieve all fields or predefined query. You may want to join Access tables, filter records, or select only a subset of fields from a query. To build a new query against a table in an Access database, follow these steps: • In Excel, select Data, Get External Data, From Other Sources, From Microsoft Query, then the Choose Data Source dialog appears. • Select MS Access Database, click okay, then the Select Database dialog appears • The Select Database dialog, select the Access database, then the Query Wizard dialog appears • Choose to include particular fields from any table or query in the database. You choose the fields on the left side of the dialog and click the > button to move the to the right side of the dialog. Selecting fields to be included in the query. • In the Query Wizard, define filters for the query. The filter is defined as only items where the inventory is greater than X, ect. • In the Query Wizard, specify up tot three sort fields for the query. Specify sort criteria • In the Query Wizard, specify that you want to return the data to MS Excel. Then you have an Import dialog. The Microsoft Query option enables you to retrieve only the records with more than five items in the inventory. With a few records, the query runs significantly faster.

Connecting to Data on a Web Page

Link your excel worksheet to webpage A. Setting Up a Connection to a Web Page To set up a connection between a worksheet and a web page: Find a selection of the worksheet that has several blank rows and columns You can return many rows or columns of data depending on the size of the selected sections of the web page On the Data tab, Get External Data Group, select From Web. Excel opens the New Web Query dialog. The dialog can open up your homepage from Internet Explorer. The yellow boxes with arrows will indicate the tops of various tables on the page Use the address bar to navigate the selected web page Click the Options button in the upper right hand corner of the New Web Query dialog. The Web Query Options dialog appears. Select whether the webpage formatting results in either text only, HTML or unformatted. Click Import to confirm the output location of the webpage In the Import Data dialog, click properties to set up automatic refreshing for the webpage The Connection Properties/ External Data Range Properties to dialog appears to control the refresh rate for the web query. Refresh the data every time you open the data for a brand new start! B. Managing Properties for Web Queries After you retrieve a web query, you can select a single cell in the query and select Properties from the Connections group on the Data tab. In the External Data Range Properties dialog appears the Data Formatting and Layout section, you can choose options to preserve cell formatting and adjusting column widths, including adding more rows.

Setting Up a Connection to a Text File

To load data from a text file into Excel by using connection group: On the Data tab, Get External Data group, select From Text icon. Then the Import Tex File dialog appears Browse to and select your text file Excel launches the familiar Text Import Wizard, where you can specify that the text is either delimited or fixed width Select Delimited then click Next Delimited text is text in which each column is separated by a character such as a comma or a tab Fixed-width data is where each field is neatly lined up when viewed in a monospace front such as courier new. In step 2 of the wizard, change the Excel default tab character between fields to a comma, or shown In step 3, specify the field type for each field and whether certain fields should be skipped. If you have a column of numbers where a leading zero needs to be preserved select Text as the field type for the ZIP code field Click the Advanced button to specify the characters used for thousands and decimal separators. You can also specify that the minus appear after the number. Click Finish. The Import Data dialog appears, specify a starting cell for the data, click the properties button The Properties dialog appears. Clear the Prompt for File Name checkbox because the file name will be the same, including in the same location folder. Accept the location for the import, Excel brings in all the records from the text file.

Connect two Worksheets

To see two worksheets of the same workbook side by side Click New Window in the Layout tab Title will be :2 Click Arrange Window, vertical or horizontal There are four ways to achieve this goal: 1. Type a formula such as =Revenue!F6 in Cell B5 2. Build the formulas using the mouse 3. Right-drag Cells 4. Copy, paste, link here A. Creating Links Using Paste Options Menu Ctrl + C Ctrl + V Ctrl + O N for Paste Link Copy the cells Paste the cells in another tab Paste Options menu appears Press ctrl Press N for Paste Link B. Creating Links Using Right-Drag Menu Alternate Drag and Drop Menu Offers a fast way to copy cells, link cells, change formulas to values and more Select the cells Hover over the edge of the selection rectangle until you see the four headed arrow or the hand icon. Right click and begin to drag to the other window Release the right mouse button Select Link Here C. Building a Link by Using the Mouse In the cell, you enter the equal sign and click the cell to connect with the other worksheet. Type the equal sign, then click the desired worksheet tab Using the moose, click a cell to get the value from the cell. Excel builds the formula ='Expense Summary'! F8 in the formula bar You can press the Enter Key or add other cells to the formula D. Links to External Workbooks Default to Absolute References Open both workbooks You can connect a worksheet to a workbook with the Building a Link by Using the Mouse-method. ='[2 Practice Book .xlsx]16.A'!$F$7 '[workbookname]worksheetname'!celladdress $F$8 to copy this cell turn it into relative reference E. Building a Formula by Typing =Worksheetname!celladdress ='Worksheet name'!celladdress Whenever the worksheet name contains space or certain special characters =[Workbook.xlsm]Worksheetname!Celladdress External link ='[Work book.xlsm]Worksheet name'!Celladdress External link contains space =SUM('C:\ [Workbook name.xlsm]Worksheetname' !Celladdress:Celladdress) linking a closed workbook Reference Data May!A2+June!A2 May! is the worksheet named A2 is the cell you want to look up in May June! is the second worksheet you want A2 is the cell you want to look up in June Referencing a range of cells.... [workbook name.xls]worksheet name!C1:C34 in other worksheets: worksheet!A1:D4 in other workbook: c:\my documents[test.xls]Sheet1!A2:A5 c:\folder[filename.xls]sheetname!celladdress:celladdress Across several worksheets: sheet1:sheet5! A12 F. Creating Links to Unsaved Workbooks "Is it OK to Save with References to Unsaved Documents?" Save the workbook then save the linking workbook G. Using the Links Tab on the Trust Center Establish a link from Workbook A to Workbook B Save and close workbook A Make changes to workbook B Save and close workbook B On a future day, open workbook B Open workbook A The new values entered in workbook B go through the flow of workbook A. If you attempt to open workbook A before workbook B, then Select Enable Content to allow the links to update if opening workbook A before B. H. Opening Workbooks with Links to Closed Workbooks Open the linking workbook, update the links to the other workbook Allow the workbooks to update if you have created both workbooks, even though you have made revisions to the linked workbook from the linking workbook. I. Dealing with Missing Linked Workbooks Click continue: when you received a linking workbook, cannot access to the linked workbook, excel alerts you that the links cannot be updated. This occurs when the workbook were renamed, moved or deleted Edit Links button Change Source button to indicate that the linked workbook has a new name or location Break Link button to change all linked formulas to their current values J. Preventing the Update Links Dialog from Appearing To suppress the Update Links Dialog: On the Data tab, in the Manage Connection group, select Edit Links to Files In the lower-left corner of the dialog that appears, click the Startup Prompt button Select Don't Display the Alert and Don't Update Automatic Links This will prevent others from receiving this message After electronically sending the file to colleagues, reset it!

Setting Up a Connection to an Access Database

To set up a table with large data sets from Access: On the Data tab, in the Get External Data group, select From Access Select the .mdb file to which you want to link. You are then given an opportunity to choose any one table or query from the database. Each query is listed in the Type column as VIEW. Choose whether your data should be imported as a table or used in a pivot table. With an additional option, you can have table imported to a regular table or have the data used as the data source for a pivot table report. Then the data is imported with default formatting.

Managing Connections

You can refresh all connections, refresh the current query, or view properties for a connection. You can have a summary of the web, text, Access, or ODBC connections in your workbook. The Workbook Connections dialog provides all the connections, including external links in the workbook to view. However, links to other workbooks are not included. You have to select Edit Links to Files from the Manage Connections group to display the Edit Links dialog where you can check the status of any workbook links as well as maintain the link location, break the links, or adjust the startup prompt to link between workbooks. To create a connection with an external workbook: Select Data, Get External Data, From Other Sources, From Microsoft Query. In the Choose Data Source dialog that appears, select Excel files. In the Select Workbook dialog, select the Excel file and click OK. If you receive a message that the data source contains no visible tables, perform steps 3 and 4. Otherwise continue on with step 5. Click Options in the Query Wizard - Choose Columns dialog Choose System Tables from the Otns dialog an click ok In the next three Query Wizard dialgos, select your fields, the filter, and the sort In the final step, choose the option to view the query in MS Query for the dialog Select the Group Records check box and click OK. This will tell the query to return a summary data set, totaling numeric fields for each unique combination of key fields. You specify which fields to group and sum in the next two steps. Select the Sum of Quantity heading n the lower half of the screen. Click the Sigma button in the toolbar to toggle through Sum, Min, Max, and Normal. Select Sum Select File, Return Data to MS Excel. You now have a refreshable data connection to an external Excel workbook that has 1,000,000 records.