Hey John! How's it going? I think David is referring to this post, which I
put up earlier today:
You want to import everything into the same table?
Option Compare Database
Sub Import()
Dim strFile As String
Dim strPath As String
Dim blnHasFieldNames As Boolean
Dim strTable As String
strPath = "C:\Documents and Settings\ThinkPad\Desktop\Test\"
blnHasFieldNames = True ' Or set to False
strTablename = "Table1"
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
strFile = Dir()
Loop
End Sub
David wants to know how to import several sheets from several Excel files in
the same folder. The code I posted imports the first sheet, and only the
first sheet, from each Excel file. How can a person import all sheets in all
Excel files in a certain folder?
I tried this:
For i = 1 To Workbooks(MyWorkBook).Sheets.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
Next i
Didn't work! Any thoughts on how to do this?
Thanks,
Ryan---
John W. Vinson said:
want to use the code given by ryguy7272 but not sure how to run code starting
with sub
Would normaly us a button on a form to run macros- can you give steps
required to run sub with button?
Copy the code into the command button's Click event. View the form properties;
select the command button; be sure that the Click event box is either blank or
[Event Procedure]; click the ... icon by it and choose Code Builder.
Access will give you two lines for free:
Private Sub buttonname_Click()
End Sub
Copy and paste Ruralguy's code between the two lines (well, leave out the Sub
and End Sub lines if they exist).
