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.
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. ...