how to import multiple excel sheets into access

[Amitabh Saraf]

I have a large number of sheets in a single Excel workbook file. I can import
each sheet individually into an Access table using the import wizard of
Access. However the task is very time consuming. I was wondering if there
exists a
macro that can import all the worksheets from my workbook into Access with a
single command.

Thanks,

Amitabh

 

[Guest]

hi,
you can do that with a macro. use the tranferspeadsheet
function.

 

[John Nurick]

Hi Amitabh,

This can't be done with a macro; it takes VBA code. Dev Ashish MVP
posted the following in response to a similar question a few years ago:

----
You can set a reference to Excel library under Tools | References, and
then run through the sheets with Automation. (Air code)

Dim wkb as excel.Workbook
Dim sht as Excel.WorkSheet
Dim xl as Excel.Application
Set xl=new Excel.Application
xl.visible=true
Set Wkb=xl.Workbooks.Open("Something.xls")
With wkb
For Each sht in .Worksheets
Docmd.TransferSpreadsheet sht.name, <<...>>
Next
End With
----

If you go to http://groups.google.com and search for

aircode xlwb mysheetimport

you'll find a similar but less terse response.


On Mon, 27 Sep 2004 02:37:01 -0700, "Amitabh Saraf" <Amitabh