Need code to import multiple single worksheets with identical fiel

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

John Nurick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top