Importing excel worksheets

[Luis]

Hello.
I'm trying to create a process to import certain excel worksheets from a
Excel workbook.
The problem i'm having is that only the first worksheet is imported.
If i use five times docmd.transferspreadsheet import to import five diferent
worksheets to five diferent tables, it imports five times the first worksheet.
Can anyone give me a hint ?

I'm using the following code.

DoCmd.TransferSpreadsheet acImport, 8, "Table1", "myFile.xls", True,
worksheet1
DoCmd.TransferSpreadsheet acImport, 8, "Table2", "myFile.xls", True,
worksheet2


Thanks

Luis

 

[Jeanette Cunningham]

Luis,
here is some code from earlier threads. I haven't tried this code, but it
looks OK.
This is the start of the thread.
---------------------------
I have an excel workbook, it has sometimes 7 or more worksheets in it. I
need
to do a comparison week to week of the workbooks. I am looking for a way to
import each sheet into access in one shot where they will be seperate
tables.
It would also be nice to be able to pick which sheets I want to import and
which ones I don't like in a drop down menu maybe?


My own preference would be to use a multiselect listbox rather than a drop
down list. The form I used had a multiselect listbox and a button. The
form load event and the command button click event are shown below. The
code assumes the imported tables are given the same name as the
corresponding worksheets. The listbox is named "lstSheets." You will need
to set the Row Source Type property (Value List) and the Multi Select (I
used Extended) property of the listbox.

Private Sub Form_Load()
Dim xl As New Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Set wkb = xl.Workbooks.Open("H:\testbase.xls")

For Each wks In wkb.Sheets
lstSheets.AddItem wks.Name
Next wks

wkb.Close

Set wks = Nothing
Set wkb = Nothing
Set xl = Nothing
End Sub

Private Sub Command9_Click()
Dim itm As Variant

For Each itm In lstSheets.ItemsSelected
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
lstSheets.ItemData(itm), "H:\testbase.xls", True, lstSheets.ItemData(itm) &
"! A1:IV65536"
Next itm

End Sub

 

[Paolo]

Hi Luis,
put the name of the worksheet between quotes in this way:

DoCmd.TransferSpreadsheet acImport, 8, "Table2", "myFile.xls", True,
"worksheet2!"

HTH Paolo

 

[Luis]

Hi Paolo.

It worked perfectly! Thanks!

Hi Luis,
put the name of the worksheet between quotes in this way:

DoCmd.TransferSpreadsheet acImport, 8, "Table2", "myFile.xls", True,
"worksheet2!"

HTH Paolo