open every file one at a time
Posted by Eric Russell on July 22, 2001 9:17 PM
I have a good one....I think.... I need a macro that will open all the files in directory C:/capex2002. All the files in this directory (all 400 of them) I need to open one at a time, run some other macros to extract the data, that I have already written, close the current file, then loop to the next file. I do not have any idea how to do this, I do not want to open all the files at once as with that many files, I do not want to cause a system crash on my computer. If it helps at all, all the files have the same naming convention, XXXXCEP02.xls the XXXX will be a 4 numeric digit property number. ANY help would be greatly appreciated!!!!!
Posted by Russell on July 22, 2001 10:19 PM
Use the Dir function, something like this:
MyPath = "C:/capex2002" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is not a directory.
If (GetAttr(MyPath & MyName) And vbDirectory) <> vbDirectory Then
' Open your file (different way of doing this including using Shell)
' Do stuff with your file (which is MyPath & MyName)
' Close your file
End If
End If
MyName = Dir ' Get next entry.
Loop
Look up the Dir function in help. The second (and later) time(s) you call it, you don't need to give the directory if you are still using the same directory as you started with.
Hope this helps,
Russell
