Open files dynamically in Excel 3

[DennisNH]

I am trying to create the first part of a module that will dynamically open any files I put in a particular directory. I have some code (that works)I will add later to manipulate the contents of each file once they are opened.

This code is opening all the files, but gives a run-time error '1004' error after the last file is opened. I cannot figure out how to stop the loop without the error.

Any suggestions appreciated.

Sub ListFiles()
F = Dir("T:\Test\Data\*.XLS&quot
Do While Len(F) > 0
F = Dir()
Workbooks.Open FileName:="T:\Test\Data\" & F
Loop
End Sub

:-I




Dennis
DennisNH@aol.com

I develop applications in Access, Excel, PowerPoint, FrontPage, Word to intranet site at work. Looking to go beyond macros into VBA, and then to VB6.

 

[JohnYingling]

I think you bypassed your first file and later tried to process a "" string. Do a switcheroo on F = Dir()

Sub ListFiles()
    F = Dir("T:\Test\Data\*.XLS") ' Get 1st file
    Do While Len(F) > 0
        Workbooks.Open FileName:="T:\Test\Data\" & F
        F = Dir() ' Get next file
    Loop
End Sub

http://WWW.VBCompare.Com

 

[JVFriederick]

This seems to work OK. Best I can tell, each time the Dir function is used, it reads another file, so you would miss the first one. I added the copied notes from Dir function help at the bottom. Hope this helps.


Sub ListFiles()
F = Dir("T:\Test\Data\*.XLS", 0)
If F <> &quot;&quot; Then Workbooks.Open FileName:=&quot;T:\Test\Data\&quot; & F ' At least one file exists
Do While Len(F) <> &quot;&quot;
F = Dir()
If F = &quot;&quot; Then End
Workbooks.Open FileName:=&quot;T:\Test\Data\&quot; & F
Loop
End Sub



You must specify pathname the first time you call the Dir function, or an error occurs. If you also specify file attributes, pathname must be included.

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (&quot;&quot.

Once a zero-length string is returned, you must specify pathname in subsequent calls or an error occurs.

 

[JVFriederick]

Whoops. Didn't see your post John.

Much better idea for sure!