import excel files

[doran_doran via AccessMonster.com]

HI,

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.

Thanks


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
Loop

End Sub

 

[OfficeDev18 via AccessMonster.com]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblMaster",
MyFile
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?

Sam