import excel files

  • Thread starter doran_doran via
  • Start date

doran_doran via


I have few hundreds of excel files (with and without headers) but the same
type of data (column a to m) that I need to import into a table (tblMaster).
I was trying to use this method to import excel files. but nothng happens
when i use following code on a button and click on the button. Please advise.


Private Sub cmdImport_Click()

Dim MyFile, MyPath, MyName, fs
MyFile = Dir("L:\Conversions\Master Trans\1\*.xls")
Set fs = CreateObject("Scripting.FileSystemObject")
Do While MyName <> ""
DoCmd.DeleteObject acTable, "tblMaster"
'DoCmd.Transfertext acImportdelim, , "NewData", myfile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblMaster"
MsgBox "import complete"
'DoCmd.OpenQuery "Append NewData to your final output"
fs.CopyFile MyFile, "L:\Conversions\Master Trans\1\Done\"
MsgBox "file have been copied to done directory"
'fs.deletfile myfile
MyName = Dir

End Sub

OfficeDev18 via

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblMaster",
Also, MyName = Dir should be MyFile = Dir

You realize, don't you, that you're not saving any of your data in Access,
and that all you're accomplishing is to copy the spreadsheets into the \Done
folder. Why don't you just do that in Windows if that's all you're interested
in? Why bother with Access?


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