check excel before importing

[eighthman11]

Hello everyone. Using Access 2003 on Windows XP
I have an excel spreadsheet I recieve from a customer that I import to
an Access table. This customer has the habit of changing the excel
format.

Is there a way I can grab the first row of the spreadsheet (which
would be the column names) and insert them into a table to check them
against the known column names that make the spreadsheet valid. Before
I attempt the import of the data to a Access Table. Any help
appreciated. Thanks

 

[Larry Linson]

You can Link to the Excel spreadsheet to obtain information from it. File |
Get External Data | Link . . . instead of File | Get External Data | Import.
The VBA operation to check in Help is Transfer Spreadsheet.

Larry Linson
Microsoft Office Access MVP

 

[Clifford Bass]

Hi,

Here is one idea: How about linking to it first? Then you can do a
select into a recordset in code and inspect the names of the fields. Then if
fine, do an append query from the linked table. Then drop the link.

If by changing the format, you mean rearranging the fields, then as
long as you have all the necessary fields, you could still use your append
query, regardless of column position in the spreadsheet. If the user changes
row names it gets more interesting. In which case you may want to decide if
you want to get into mapping/remapping of field names.

Clifford Bass

 

[eighthman11]

Thanks Cliff and Larry,

I thought about linking the spreadsheet, but there are several
problems with linking. First of all the spreadsheet first row is the
column headings but many of the excel column headings are not valid to
be Access Column names. I could link the speadsheet using the option
that the first row is not column headings but that screws up the data
types to several of the columns. The second problem is everytime they
send me the spreadsheet they give the workbook sheet a different
name. This has also caused me a problem. That is why I went with
"DoCmd.TransferSpreadsheet acImport"

Before sending in this topic I did a search and discovered several
people with the same problem I had although many of the solutions were
to advance for me I did come up with a way to solve my problem which I
would like to share with others who may not be as advance with VB code
like me.

I created a table called "ExcelMasterColumns" with 23 columns (F1
thru F23) the number of columns in the spreadsheet. I have one record
in the table consisting of all 23 column headings that are in the
spreadsheet.
I then do a "DoCmd.TransferSpreadsheet acImport" using the range
A1 to AB1 of the spreadsheet the user is trying to import to get the
column headings and insert them into a table I called
"ExcelColumnsValidate".
All that is left is to do a query that matches up the columns of the
two tables. If everything matches the file is good; if not the file
is bad.
I hope this might help somebody. It may not be totally fullproof
but its simple and a pretty good check.

 

[Clifford Bass]

Hi,

You are welcome. It sounds like a good solution! Thanks for sharing it.

Clifford Bass