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
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
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?
