SHardy
asked on
Excel97 - Iterate Files
Could someone help me please???
I want to create a file with the sole purpose of copying and filtering information from other files. All the files that contain the source data will be of exactly the same format. These will be the only files in the source directory. This source directory will be given as a direct input on the new file.
What I need to know is how to I open each file from the source directory in turn using VBA code? I do not want to supply each individual file name in the new file. I would like it to automatically open EVERY file in the directory indicated. Thus, any new files WILL be included.
Thanks.
I want to create a file with the sole purpose of copying and filtering information from other files. All the files that contain the source data will be of exactly the same format. These will be the only files in the source directory. This source directory will be given as a direct input on the new file.
What I need to know is how to I open each file from the source directory in turn using VBA code? I do not want to supply each individual file name in the new file. I would like it to automatically open EVERY file in the directory indicated. Thus, any new files WILL be included.
Thanks.
ASKER
What I was after was to open them in turn. I think I have come up with something now. I found the "FileSearch" class in the object browser, and have setup a small test as follows:
Sub test()
sourcepth = [SourcePath]
Sheets("SourceFiles").Sele ct
Range("A4:A65536").ClearCo ntents
Range("A4").Select
With Application.FileSearch
.NewSearch
.LookIn = sourcepth
.SearchSubFolders = True
.FileName = ""
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
End With
With Application.FileSearch
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Offset(1, 0).Select
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
This gives me a list of files from the folder that I can then work through one at a time. This should do for what I was after, but if you have any different suggestions then please feel free.
Thanks.
Sub test()
sourcepth = [SourcePath]
Sheets("SourceFiles").Sele
Range("A4:A65536").ClearCo
Range("A4").Select
With Application.FileSearch
.NewSearch
.LookIn = sourcepth
.SearchSubFolders = True
.FileName = ""
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
End With
With Application.FileSearch
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Offset(1, 0).Select
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
This gives me a list of files from the folder that I can then work through one at a time. This should do for what I was after, but if you have any different suggestions then please feel free.
Thanks.
that's what i had in mind ;) so you solved it yourself
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
that method uses the native VB functions instead of above but that should result in the same
Good Luck
Good Luck
Sub GetExcelFilesDir()
Dim strFilesInDir As String
Dim i As Integer
strFilesInDir = inputbox("Name of the directory to look in", "PathName", "C:\My Documents")
strFilesInDir = Dir(strFilesInDir & "\*.xls") 'insert your directory name
'here, you can use wildcards
'get different file types
Sheets(1).Select
i = 1
Do Until strFilesInDir = ""
Cells(i, 1).Value = strFilesInDir
i = i + 1
strFilesInDir = Dir
Loop
End Sub
This will ask for the path name first
you can ask a 0-point Q in the community support to refund your points and let me have this PAQ by accepting my comment as answer ;)
Dim strFilesInDir As String
Dim i As Integer
strFilesInDir = inputbox("Name of the directory to look in", "PathName", "C:\My Documents")
strFilesInDir = Dir(strFilesInDir & "\*.xls") 'insert your directory name
'here, you can use wildcards
'get different file types
Sheets(1).Select
i = 1
Do Until strFilesInDir = ""
Cells(i, 1).Value = strFilesInDir
i = i + 1
strFilesInDir = Dir
Loop
End Sub
This will ask for the path name first
you can ask a 0-point Q in the community support to refund your points and let me have this PAQ by accepting my comment as answer ;)
Points reduced per questioners request.
Computer101
E-E Moderator
Computer101
E-E Moderator
ASKER
Bruintje,
Thanks for your comments, and I preferred your method anyway so you can still have some points.
Cheers.
Thanks for your comments, and I preferred your method anyway so you can still have some points.
Cheers.
Thanks for that, there are always more ways to solve the same problem
ASKER
Shame they're not always that easy to find.
do you need this in one loop? i mean have all the files open at once or do walk through them in turn?