PDA

View Full Version : [SOLVED] Setting a filename as a variable



th1chsn
10-29-2007, 01:14 PM
Hello! I'm hoping that there are some people on this forum that might know how to help me. What I'm trying to do is look in a directory and get the filename of the first file, do some things to it, then get the next one, etc.

Here's the code I have. I get up to the point where the code knows how many files are in the directory. What I'm trying to do is declare s to equal the name of the first file. I thought s = .Filename would work but it doesn't.


Dim s as String
With Application.FileSearch
.NewSearch
.LookIn = "C:\User\Files"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute > 0 Then 'Only do this if there are files in the directory
For Count = 1 To .FoundFiles.Count ' Loop through all.
s = .Filename
Next Count
End If
End With
Does anyone know how to do this?

Charlize
10-29-2007, 02:32 PM
use
Dir and hit F1 in vba Editor.

Bob Phillips
10-29-2007, 02:35 PM
Dim s As String
Dim cnt As Long
With Application.FileSearch
.NewSearch
.LookIn = "C:\test\"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute > 0 Then 'Only do this if there are files in the directory
For cnt = 1 To .FoundFiles.Count ' Loop through all.
s = .FoundFiles(cnt)
Next cnt
End If
End With

th1chsn
10-30-2007, 07:00 AM
Thanks for the help!

Zack Barresse
10-30-2007, 07:12 AM
In regards to what Charlize was talking about, the FileSearch object was taken out of the Office 2007 OM. This was one of my favorite objects too. So, in other words, if you want your solution to be cross-compatible with Excel 2007 and up, you'll need to use either the Dir() or FileSystemObject solution types. Although I almost didn't go to 2007 because of it (petty, maybe, but I can be stubborn ;) ).