Multiple spreadsheet import

  • Thread starter Thread starter skc
  • Start date Start date
S

skc

I want to import around 30 spreadsheets into one table
appending each time.

The spreadsheets have the same columns and may have
duplicate data, but I just want a quick way of importing
them all into one table but appending each time.

Can someone help?

Using Office 2000 Pro.

skc
 
Hi skc,

Microsoft MVP Joe Fallon has posted the skeleton code below for
importing data from all files in a folder. It assumes text files but
will work for Excel worksheets if you replace the DoCmd.TransferText
statement with the appropriate DoCmd.TransferSpreadsheet - provided that
you only need to import one worksheet from each workbook and that all
the worksheets have the same name (e.g. "Sheet1").

If those conditions don't hold (e.g. importing multiple sheets from one
workbook) it's still possible but more difficult.

'CODE STARTS

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

'CODE ENDS
 
Back
Top