import fields from multiple excel files...

[losdosp...@gmail.com]

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

--
Dave Hargis, Microsoft Access MVP

[losdosp...@gmail.com]

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

<losdosp...@gmail.com> wrote in message
news:e6a6d7d2-590b-4345...@a70g2000hsh.googlegroups.com...

[losdosp...@gmail.com]

On 9 jun, 19:09, "Douglas J. Steele"

<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> 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
>
> --
> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> (no private e-mails, please)

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

[pietl...@hotmail.com]

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.