Need code to import multiple single worksheets with identical fiel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need code to import multiple single worksheets with identical fields into a
single table into an Access table. I found several examples but they all deal
with with mulitple worksheets in a single book, I have several single
worksheet workbooks with identical fields.
Thanks in advance.
 
The VBA code below was posted by my fellow MVP Joe Fallon a while ago.
Just change the TransferText line to TransferSpreadsheet, with your
actual table and sheet names, e.g.

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, "MyTable", _
"C:\MyFiles\" & strFile, True, "Sheet1$"



How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub



On Mon, 25 Jul 2005 08:33:02 -0700, In need of code <In need of
 
Back
Top