Importing Access Data

Let's look at how Excel brings in external data by recording a quick macro to import data from an Access database.

Create a new macro named GetAccessData. We're going to import the Extended Employees list (query) from the Northwind 2007 database onto Sheet1 in a new Excel workbook.

1. Select the Data ribbon.

2. Select From Access from the Get External Data section of the Data ribbon.

3. Navigate to wherever you have the Northwind database stored.

Note The files for these examples can be found in the Source Code/Download section of this book's home page at www.apress.com.

Note There is a new version of Northwind in Access 2007 that uses a file extension of *.accdb for Access databases.

4. In the Select Table dialog box, choose Employees Extended, and click OK.

5. In the Import Data dialog box, you have choices of how you want to view the data (table, PivotTable, or PivotChart) and where you want to put the data, as well as advanced options. For now, just accept the defaults by clicking the OK button.

The code generated from this looks like Listing 2-1.

Listing 2-1. Macro-Generated Data Access Code Sub GetAccessData()

' GetAccessData Macro

' Code created by Excel 2007 Macro Recorder

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( w

"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password= ;User ID=Admin;" w

& "Data Source=C:\projects\Excel2007Book\Files\Northwind 2007.accdb;Mod" w , w

"e=Share Deny Write;Extended Properties= ;" w

& "Jet OLEDB:System database= Jet OLEDB:Registry Path= ;" w

& "Jet OLEDB:Database Password= w

"Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;" w

& "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transaction" w

"s=1;Jet OLEDB:New Database Password= ;" w

& "Jet OLEDB:Create System Database=False;" w

& "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't C" w

"opy Locale on Compact=False;" w

& "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;" w

& "Jet OLEDB:Support Complex Data=Fa" w

, "lse"), Destination:=Range("$A$1")).QueryTable

.CommandType = xlCmdTable

.CommandText = Array("Employees Extended")

.RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True

.SourceDataFile = "C:\projects\Excel2007Book\Files\Northwind 2007a.accdb" .ListObject.DisplayName = "Table_Northwind_2007a.accdb" .Refresh BackgroundQuery:=False

End With End Sub

The SourceType and Source settings of the ListObject.Add method tell whether the data is from an Excel sheet (xlSrcRange = 1) or an external source (xlSrcExternal = 0). When the SourceType is external, the source is an array of string values specifying a connection to the source data.

Buried at the end of our lengthy source data string is this line of code: Destination:=Range("$A$1")).QueryTable

A QueryTable object is a worksheet table that is created any time data is returned from an external data source like an Access or SQL Server database. Table 2-1 lists the members of the QueryTable object and describes them.

Table 2-1. QueryTable Object Members

QueryTable Object Members Description

CommandType

CommandText RowNumbers

FillAdjacentFormulas

PreserveFormatting

RefreshOnFileOpen

BackgroundQuery RefreshStyle

Returns/sets one of the xlCmdType constants. The xlCommandType constants define whether an SQL statement, cube, or OLE dB data source will be requested. The default value is xlCmdSQL.

Returns/sets the command string for the data source.

True if row numbers are added as the first column of the query table.

True if formulas to the right of the query table are automatically updated whenever the query table is refreshed.

True if formatting common to the first five rows of data are applied to new rows in the query table.

True if the PivotTable cache or query table is automatically updated whenever the workbook is opened.

True if queries for the query table are performed in the background.

Returns/sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset (returned by a query).

Continued

CHAPTER 2 ■ DATA IN, DATA OUT Table 2-1. Continued

QueryTable Object Members Description

SavePassword

True if password information in an ODBC connection string is saved with the query. False if the password is removed.

SaveData

True if data for the query table report is saved with the workbook. False if the report definition is saved and nothing else.

AdjustColumnWidth

True if the column widths are automatically adjusted for the best fit each time you refresh the specified query table. False if they are not.

RefreshPeriod

Returns/sets the number of minutes between refreshes.

PreserveColumnInfo

True if column sorting, filtering, and layout information is preserved when a query table is refreshed. Default value is False.

SourceDataFile

Returns/sets a String value that indicates the source data file for a query table.

ListObject.DisplayName

Property of ListObject. Creates or returns a named range for the inserted data.

Refresh

Causes Excel to connect to the data source, execute the SQL query again, and return data to the range that contains the QueryTable object. The QueryTable object doesn't communicate with the data source once data is inserted unless this method is called.

0 0

Responses

Post a comment