Need code to import multiple single worksheets with identical fiel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

In need of code - 25 Jul 2005 16:33 GMT

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.

Reply to this Message

John Nurick - 25 Jul 2005 21:37 GMT

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

>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.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message