O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Importing Data from Excel

Problem

Importing data from Excel is a straightforward task using the standard Office tools, but I want to process the information before storing it in my database.

Solution

One way you can import data from Excel is to use ADO and treat the Excel workbook as a database. Within the workbook, each worksheet is a table, and the first row can contain the field names. Data types are a little tricky, but, realistically, your VBA program can treat the data as strings. Excel will recognize the data according to its usual rules. Values that contain valid numbers will be treated as numbers, and the same goes for valid dates. All other values will be treated as strings.

The key to the import process is the ADO connection string. The following connection string uses the workbook C:\AccessData\Chapter7-18.xls. Setting the HDR field to Yes indicates that the first row of the worksheet contains the column headings. Setting the IMEX field to 1 specifies that values in columns containing a mixture of text and numbers should be treated as text:

	InTable.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	    "Data Source=C:\AccessData\Chapter7-18.xls;" & _
	    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

While this connection string grants you access to the workbook, you have to address the individual worksheets by their names, as you would if they were tables. However, you need to format the names properly in order for the database driver to locate the right worksheets. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required