import multiple Excel worksheet into a main Access table

[KC Classic]

Good Afternoon,

I am working with serial number data that is split out by customer on
separate worksheets within an Excel workbook. I would like to import (or
link) all worksheets to a single Access table. There are probably 20
separate worksheets. The import wizard only allows one worksheet at a time
to my knowledge. All Excel worksheets have the exact same format.

I have imported each separate worksheet into an Access database and then cut
and then cut and pasted the data into a single main table but would like to
automate, if possible. Would be able to query and analyze data much more
efficiently if the import process would work.

Is there an easy way to import all worksheets to a single table in Access?

Thanks in advance.

 

[Tom Wickerath]

Hi KC,

All Excel worksheets have the exact same format.

In that case, then you have a good shot at automating the import of the
data. It will be helpful if each Excel file includes a named range, that
precisely identifies the range of interest.

You need to break this problem into separate operations. First, you'll need
some way to identify the Excel files to import. This might be all files
within a certain folder, it might be Excel filenames that match some
pre-determined pattern, you might even store the actual paths to the Excel
files in a table, or perhaps open a Common File dialog (use the API code
method) with the multi-select option. But you need some way of telling your
code which files to operate upon.

Instead of importing as separate tables, or establishing separate linked
tables, once you know all of the files (and filepaths) involved, you can
write code that loops through each one, doing a sequential append query. Here
are two examples of appending records:

Appending Records (contributed in newsgroup post by Bob Hairgrove)
The syntax for INSERT INTO would be one of the following:

(a) INSERT INTO [some table] (<list of column names>) VALUES (<list of
values>);

(b) INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...

For your spreadsheet data, I think you would want to use the second (b) form.

The SELECT can be written against the Excel file directly, like this:

SELECT *
FROM [excel 8.0;DATABASE= Full path to Excel File].NamedRange;

or

SELECT Field1, Field2, Field3…
FROM [excel 8.0;DATABASE= Full path to Excel File].NamedRange;

where NamedRange is a previously established named range within the
spreadsheet, and Field1, Field2, Field3 are valid header rows within the
named range.

So, now, in a VBA procedure, you just need to set a string variable, perhaps
name it strSQL, that allows you to modify the SQL statement on each pass
through your loop. Here is a high-level outline of some untested "air code".
You'd still have plenty to fill in:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
dim strPath As String

Set db = CurrentDB()

Do Until rs.EOF = True
strPath = rs("FilePath")
strSQL = "SELECT Field1, Field2, Field3…" _
& "FROM [excel 8.0;DATABASE= '" & strPath & "'].NamedRange;"
db.Execute "INSERT INTO [some table] " _
& "(<list of columns>) " & strSQL, dbFailOnError

rs.MoveNext
Loop

Appropriate ExitProc and ProcError blocks follow here, with code to destory
recordset variable, and set the db variable to Nothing.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________