Creating and Using Connection Files

The information used to create query tables can be stored to a file on disk, called a connection file, and reused. Connection files provide several advantages when used in conjunction with QueryTables. They allow you to create a variety of query tables, store them to disk, and distribute them with your applications. Users can also use connection files directly by selecting them from the Existing Connections dialog on the Data tab of the Ribbon. Finally, if some detail of the data source changes — for example, the server name where a relational database is located, you can simply edit the connection file to reflect the new information and redistribute it. QueryTables based on the connection file will then be updated automatically with the new information.

Unfortunately, connection files do not present a very consistent story. There are more than half a dozen potential file types available, and most of them can be used for multiple types of source data. Each of the three data sources covered in this chapter (databases, web queries, and text files) requires a different type of connection file. This section focuses on Office Data Connect (.odc) and Web Query (.iqy) files. Office Data Connect files store connection information for databases, and Web Query files store connection information for web queries.

Connection files are simply text files with a file extension that identifies their type and contents, formatted according to what that type of connection requires. Connection files can be stored and used from any accessible directory on a computer, but connection files that you want to be visible automatically in the Get External Data O Existing Connections list in the Excel user interface must be located in one of the following places:

□ The C:\...\My Documents\My Data Sources\ folder under the profile of the currently logged-in user.

□ The C:\ProgramFiles\CommonFiles\ODBC\DataSources\ folder.

□ The C:\Program Files\Microsoft Office\Office12\Queries\ folder.

□ A custom location that has been defined by your network administrator through the use of Office policy settings.

Office Data Connect Files

An Office Data Connect (ODC) file contains XML data that specifies all of the information required to recreate an external connection to a database. See Chapter 12 for an introduction to XML if you aren't familiar with it. Although an ODC file contains XML data, it is not a well-formed XML file, so if you need to edit its contents, you may find it easier to use a text editor as opposed to an XML editor.

You can save the connection information for a QueryTable or ListObject to an ODC file by using the SaveAsODC method. The following line of code demonstrates how to save an ODC file that represents the first QueryTable created earlier in this chapter:

Sheet1.QueryTables(1).SaveAsODC "C:\Files\CustomersTable.odc"

ODC files can only be saved from QueryTables of ListObjects that were created through OLEDB. Because utilizing parameter queries requires the use of ODBC, you cannot save an ODC file for a QueryTable or ListObject that utilizes a parameter query.

Upon opening the CustomersTable.odc file in a text editor, you will see a lot of content, most of which is beyond the scope of this chapter. All the data you would potentially need to edit is contained within the two XML elements located near the top of the file. An abbreviated version of these two elements is as follows:

<xml id=docprops>

<o:DocumentProperties >

<o:Name>Connection</o:Name> </o:DocumentProperties> </xml>

<xml id=msodc>

<odc:OfficeDataConnection >

<odc:Connection odc:Type="OLEDB"> <odc:ConnectionString>

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Files\Northwind 2007.accdb;... </odc:ConnectionString>

<odc:CommandType>Table</odc:CommandType> <odc:CommandText>Customers</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection> </xml>

The first XML element, called the docprops element, contains the name that will be displayed to the user in the Existing Connections dialog. This is automatically given the same name as the connection from which the ODC file was derived. Because you did not explicitly name your connection when you created it, VBA gave it the default name Connection. This is what you see stored in the Name element within the XML docprops element. You can simply edit this value to give the ODC file a more meaningful description.

The second XML element, called the msodc element, contains the connection string, command text, and command type used to create the QueryTable. The connection string that you see in the preceding example shows just the arguments you specified when you created the original QueryTable. If you open the ODC file yourself, you will see more than a dozen additional connection string arguments. These are simply the default values that OLEDB uses for any arguments you haven't specified.

Again, however, a connection string is simply plain text, so if you needed to modify the path to the database for all users of your application, you could simply type a new path into the ConnectionString element, save the file, and distribute it to everyone using your application. Any QueryTables built from the connection file would automatically update themselves with the new information.

To use the CustomersTable.odc file, you need to attach it to your QueryTable in the following manner:

qryTable.SourceConnectionFile = "C:\Files\CustomersTable.odc" qryTable.RobustConnect = xlAlways

Be sure the QueryTable to which you are attaching the ODC file is the same QueryTable from which it was created. Once you have done this, the QueryTable will use the information contained in the specified ODC file to obtain its connection information whenever it needs to be refreshed.

It would be nice if you could simply create a new QueryTable using the ODC file as the data source in the first place. However, because the Connection argument of the QueryTables.Add method is required and does not accept an ODC file as its value, you must create the QueryTable initially in the manner shown in the previous section, and then attach the QueryTable to the ODC file afterward.

Web Query Files

Web Query (IQY) files are far simpler and easier to understand than ODC files. The process of generating an IQY file from an existing Web Query is a bit convoluted. There's no automated method of creating them like there is with ODC files. Creating an IQY file from an existing Web Query requires the following steps:

1. Open the Workbook Connections dialog using the Data O Manage Connections O Connections button.

2. In the Workbook Connections dialog, select the connection that corresponds to your Web Query and click the Properties button. This will display the Connection Properties dialog.

3. In the Connection Properties dialog, select the Definition tab and click the Edit Query button. This will display the Edit Web Query dialog.

4. You will see a toolbar across the top of the Edit Web Query dialog. The second button from the left on this toolbar is the Save Query button. Click this button and you will be prompted by a Save Workspace dialog to save your Web Query as an IQY file.

If you perform these steps on the connection for the Wall Street Journal Web Query created in the previous section, an IQY file with the following contents will be generated:

WEB 1

http://online.wsj.com/public/page/markets.html?mod=hpp_us_indexes

Selection=19 Formatting=All

PreFormattedTextToColumns=False

ConsecutiveDelimitersAsOne=False

SingleBlockTextImport=False

DisableDateRecognition=False

DisableRedirections=True

Only three entries in this file are important for your purposes. These are the URL, Selection, and Formatting entries. In fact, you could delete everything in the IQY file other than these three entries and the query would perform exactly as expected.

The URL is self-explanatory. It's the same URL used in the connection string when you originally created the Web Query. The Selection line specifies what the query should retrieve. This will be either the string value EntirePage or a number indicating the specific table you wish to retrieve. In this case the number 19 was generated, because that is the index number of the table specified when the Web Query was created.

The Formatting line specifies how the query should be formatted on the worksheet. Its potential values are All, RTF, or None, which correspond to the similarly named settings for the WebFormatting argument described in the previous section.

In addition to simplicity, IQY files have one additional advantage over ODC files for VBA programmers: you can create a new QueryTable directly from an IQY file using VBA. You could re-create the Wall Street Journal QueryTable example using the preceding IQY file in the following manner:

Sub CreateWebQueryFromIQY()

Dim qryTable As QueryTable Dim rngDestination As Range Dim strConnection As String

' Define the connection string and destination range. strConnection = "FINDER;C:\Files\Wall Street Journal Query.iqy" Set rngDestination = Sheet7.Range("A1")

' Create the QueryTable.

Set qryTable = Sheet7.QueryTables.Add(strConnection, rngDestination)

' Populate the QueryTable. qryTable.Refresh False

End Sub

When creating a QueryTable from an IQY file, you pass FINDER as the first argument to the connection string and the full path and filename of the IQY file as the second argument. Notice that you don't need to set any additional properties of the QueryTable after creating it and prior to refreshing it. This is because all this information is contained in the IQY file.

+1 -1

Responses

Post a comment