import fields from multiple excel files...

[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!

 

[Klatuu]

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

 

[losdosplebitos]

Im getting Compile error Variable not defined on strFileName =
Dir(conFile & "*.xls")
Ive got that same err msg last time I tried with Dir()

 

[Douglas J. Steele]

Have you used Dir for your own purposes anywhere, like a field name, a form
name, a function or anything else?

Dir is a reserved word, so you shouldn't use it. For a comprehensive list of
names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[losdosplebitos]

Have you used Dir for your own purposes anywhere, like a field name, a form
name, a function or anything else?

Dir is a reserved word, so you shouldn't use it. For a comprehensive list of
names to avoid, see what Allen Browne has athttp://www.allenbrowne.com/AppIssueBadWord.html

No, was trying to get a list of xls files in the folder.

 

[pietlinden]

Im getting Compile error Variable not defined on strFileName =
Dir(conFile & "*.xls")
Ive got that same err msg last time I tried with Dir()  

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