Listing Files in a Directory

[alexkeel79]

I'm working on a script to import all of the files in a given directory into my access DB. Basically what I am trying to do is get a list of all the files in the given directory and then loop through all of those files and import their content into the DB. I found an example to be used for Excel VBA that I thought would transfer but I was wrong. Here is the code I tried:

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scription.Folder
Dim FileItem As Scripting.File
Dim r As Long

Set FSO = New Scription.FileSystemObject
Set SourceFolder = FSO.GetFolder("O:\Risk Migration\CSV Files\ACC")
r = 5
For Each FileItem In SourceFolder.Files
MsgBox FileItem.Name, vbOKOnly
Next FileItem

Any ideas?

thx...

 

[RoyVidar]

and what is the problem with the code?

(you did reference Microsoft Scripting Runtime, did you?)

Roy-Vidar

 

[lameid]

I always used the dir function. Although I would also move each file after I imported it using name. But that was Access 97 not sure it still works...

strpath = "" 'set the path to work with here
strFilename = Dir(strPath & "*.*", vbNormal)
While strFilename <> ""
'Import file
Name (StrPath & strfilename) (StrDestPath & strfilename)
strFilename = Dir(strPath & "*.*", vbNormal)

Wend

 

[alexkeel79]

I figured it out. I needed to reference Microsoft Scripting Runtime library. For future reference here is the link that I used:

http://www.exceltip.com/st/List_fil...Runtime_using_VBA_in_Microsoft_Excel/446.html