Opening Multiple Files

  • Thread starter Thread starter Dave Ruhl
  • Start date Start date
D

Dave Ruhl

Thanks to help I've received here, I know how to use the
Windows Open File dialog to open a single file or get a
folder name.

What I'm looking for now is a way to loop through all of
the files in a user-selected folder. I'm importing data
from about 25 Excel files every 2 weeks (into Access
2002).

Each batch of files is saved in a separate, new folder,
and I want my user to be able to select that folder and
have Access import the data from each file. Once I get a
file name I know how to import the data, I just need to
know how I can get each file name in the selected
folder. Thanks !
 
Dim strFile As String
Dim strFolder As String

strFolder = "C:\Worksheets\20040701\"
strFile = Dir(strFolder & "*.xls")
Do While Len(strFile) > 0

' Put your code to do whatever you want with the file.
' Note that strFile will only contain the file name: no path.
' You need to refer to it as strFolder & strFile

strFile = Dir()
Loop
 
You can take the following code, and modify it.

You can simply pass it a starring dir, and it returns all file names. Note
that this routine also returns all sub-folders and file names also.

I assume that you grabbed the code to popup up the windows folder dialog?

You can find that here:

http://www.mvps.org/access/api/index.html

You can then use the following code to grab all the file names, and then you
process them.

So, if you do this right, your users get a nice window dialog to browse to a
folder..and then you process that folder...

Also, if you don't need the ability grab sub-folders, then a simple code
loop with the dir command will do the trick, and you don't need my routine
below, but the routine below is quite neat, since it returns all files, and
this includes sub-folders.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub
 
Thanks for the response Albert, I did get the open file
api code from the site you mentioned, and I got
everything to work using Doug Steele's code (from his
reply - which I saw before yours). Thanks again - you
guys are really great !
 
Back
Top