how to paste a copied Excel range into a table in Access via VBA?transferspreadsheet inadequate

[Kate]

Hi, I have been using transferspreadsheet to
import a named range from Excel. Unfortunately,
as so many have discovered, this results in import
errors if the 26th row contains a text value and
the first 25 were either null or numeric. I have
been losing data due to this. Office 2003 SP3.

A workaround would be to select the range in
Excel, and then paste it into an Access table with
the correct field types. This works manually, but
I sometimes have dozens of spreadsheets to import,
and I'd like to automate it.

I have no problem with the code for selecting and
copying the range in Excel; but I don't have a
clue as to how to paste it into my Access table
(which I can open and add a new record to, no
problem).

Can anyone offer advice on this?

Thanks so much,
Kate

 

[Ron2006]

What I have done is to open the spreadsheet from Access using
automation and then go to the appropriate tab, cell A2 and then insert
a row, I then place a 0 or X or a date into each cell across the
spreadsheet depending on what the data is supposed to be.
Then I close the spreadsheet,
Perform the transferspreadsheet
and then run a query that deletes the bogus record from the imported
table.


Ron

 

[Kate]

Ron, that's not a bad idea! Thank you,
Kate