Importing Excel Spreadsheets Into Access 97

[Brian Graham]

There's an old Access app here that I've been updating. The original "programmer" set things up poorly and every month the end-user has been loading multiple spreadsheets by creating new tables and copying an old form to look at the new data. As a result the production database has 160+ tables and another 160+ forms.

I've managed to concatenate all the data in a logical fashion - not easily mind you; the column names differed slightly every month due to extra spaces etc.

Now I'm trying to automate the import process. DoCmd.TransferSpreadsheet works well, except that I'm getting import errors caused by dissimilar data being in columns. Ie, the first 9 rows of a column contain numerics, the 10th alphanumeric.

So, I'm really looking for a way to ensure the spreadsheet headings are accurate, and to insert a dummy row just below them. I can delete the row via Access later.

Any Suggestions?? (Again, Access97..)
--
Brian

[Brian Graham]

As a work-around I have created an Excel macro to update the headings and insert a dummy row. Not ideal as it puts the onus on the user, but it works..

I'd still rather do it all from Access..
--
Brian

Brian Graham<Gr*****@psac-afpc.com> September 1, 2005 11:41 am >>>

There's an old Access app here that I've been updating. The original "programmer" set things up poorly and every month the end-user has been loading multiple spreadsheets by creating new tables and copying an old form to look at the new data. As a result the production database has 160+ tables and another 160+ forms.

I've managed to concatenate all the data in a logical fashion - not easily mind you; the column names differed slightly every month due to extra spaces etc.

Now I'm trying to automate the import process. DoCmd.TransferSpreadsheet works well, except that I'm getting import errors caused by dissimilar data being in columns. Ie, the first 9 rows of a column contain numerics, the 10th alphanumeric.

So, I'm really looking for a way to ensure the spreadsheet headings are accurate, and to insert a dummy row just below them. I can delete the row via Access later.

Any Suggestions?? (Again, Access97..)
--
Brian