Import Excel into Access using VBA

[Guest]

I have an Access database that I need to update regularly from an SQL
database. For reasons that I won't go in to, I have to export the data from
the SQL into Excel format. I have tried saving the s/s and linking to it, but
Access misinterprets a column of text as a number field in the database. The
majority of the data is numeric but the link fails and misses out some data
such as 5a.
If I import the data into an existing table, all is OK.
What I want to be able to do is write a sub or proc to automatically import
the data from a s/s of known name into a table of known name. I will of
course empty out the old contents of the table first. I have been unable to
find out how to do this using Help, and have had a browse of the usergroups
to no avail.
Please can someone help me.

TJ

 

[Guest]

Trevor,

I know an MVP or real programmer will answer you question with increased
detail. However, if you put this into a button click command on a form you
will get what you asked for.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTable",
"C:\File.xls"

 

[Guest]

Steve. Thanks for your reply. I had just found the Transfer Spreadsheet
thingy in a previous post about exporting excel, and was just about to
investigate same. I'll report back on success or otherwise.
Thanks again

TrevorJ

 

[Guest]

Steven.
I dont care if you are not a 'real programmer' It works just fine and dandy
for me . I just had to add a 'True' to the end of the line because of
column titles on the S/s

Thanks a mill

TrevorJ

 

[Guest]

Hey, just glad I was able to help someone...

Steven.
I dont care if you are not a 'real programmer' It works just fine and dandy
for me . I just had to add a 'True' to the end of the line because of
column titles on the S/s

Thanks a mill

TrevorJ