Excel Import Specifications

[Guest]

How do you define an excel import specification?

I've got a series of about 50 Excel Workbooks that I have received from
another organization. Each of these workbooks has numerous worksheets, all
of which are formatted the same.

When I try to import from some of these Worksheets, the genius of the Access
Import Wizard wants to add some fields to the import, in which there is no
data. You might ask how I know there is no data, it is because I have
highlighted all the columns to the left of column G, and cleared all contents
from those columns. The only way I seem to be able to get around this is to
create a new worksheet in the workbook, copy the data from the one I want,
paste it into the new worksheet, then close the workbook. If I could figure
out how to write an import spec, I could use the transferspreadsheet action
and pass it the correct specification. Any ideas would be greatly
appreciated!!!!!!

 

[Ken Snell [MVP]]

Unfortunately, this feature does not exist in current versions of
ACCESS....and many of us wish that it did.

You can use VBA automation to get the data, but it's a bit more laborious
and time-consuming when it runs.

 

[John Nurick]

Hi Dale,

As Ken says, there's no such thing as an import spec for Excel files.
One thing that does work is to write (or have your code construct) SQL
queries that get the data from the right part of the worksheet, using
syntax like this:

Select query (i.e. linking to data on a worksheet)
SELECT *
FROM [Excel 8.0;HDR=Yes;database=C:\Folder\File.xls;].[Sheet1$G1:K99]
;

Import into existing table
INSERT INTO MyTable
SELECT F1 AS Fieldname, F2 AS NextName, F3 AS OtherName
FROM [Excel 8.0;HDR=No;database=C:\Folder\File.xls;].[Sheet1$G2:K99]
;