import fields from multiple excel files...

  • Thread starter Thread starter losdosplebitos
  • Start date Start date
L

losdosplebitos

hi there,
I have 120+ excel files form wich i need to import some fields, say
H14:N14 to an Access table,
ive used DoCmd.TransferSpreadsheet succesfully in one file, but i
havent been able to make it iterate over my 120 files,
im sure this is kinda trivial, and ive found a couple of related
posts, but being the newbie i am, i havent been able to put together
the code to do this,
You'll make a man very happy if you can help me!
:)
 
Okay here is how you do it. You use the Dir function to loop through all the
Excel files in a specific directory. Then you use the return value of the
Dir function to build the filename string for the TransferDatabase method.

Const conFile As String = "C:\SomeExcelFiles\" 'Use your real path name
Dim strFilePath As String
Dim FileName As String
strFileName = Dir(conFile & "*.xls") 'you can use whatever name filter
you want
Do While Len(strFileName) > 0
strFilePath = conFile & strFileName
docmd.TransferSpreadsheet acImport, ,"TableName", _
strFilePath, False,"H14:N14"
strFileName = Dir()
Loop
 
Im getting Compile error Variable not defined on strFileName =
Dir(conFile & "*.xls")
Ive got that same err msg last time I tried with Dir() :(
 
Im getting Compile error Variable not defined on strFileName =
Dir(conFile & "*.xls")
Ive got that same err msg last time I tried with Dir()   :(
Click to expand...

Const conFile As String = "C:\SomeExcelFiles\" 'Use your real path
name
Dim strFilePath As String
Dim strFileName As String '<---- Added the "str" prefix. now it
should compile and work.
strFileName = Dir(conFile & "*.xls") 'you can use whatever name
filter
you want
Do While Len(strFileName) > 0
strFilePath = conFile & strFileName
docmd.TransferSpreadsheet acImport, ,"TableName", _
strFilePath, False,"H14:N14"
strFileName = Dir()
Loop
 
Back
Top