open files
Posted by Eric-Russell on August 26, 2001 6:54 PM
I have to open a number of files listed in a directory one at a time and run a macro that I have written in order to correct some formula and formatting problems on a spreadsheet I created. I need to open them one at a time as there are more than 350 indivdual files (one for each location). The naming convention is the same for all files XXXXCEP02.xls (XXXX is the four digit property number) The property numbers are sporadic ranging from 0001 to 0975, but for example 0002 is not a property number. So an x+1 varaible would not work and returns an error when filename 0002CEP02.xls is not in the directory to open. I desperatly need help. The directory path is P:\2002\BUD\filename. I have the balance of the macro to make the corrections, I just need it to open each file indivdually, run my macro, save/close and open next file using a loop statement. PLEASE HELP.
Posted by Ivan F Moala on August 26, 2001 8:59 PM
Eric here is one way to do this.
1) Place all your files in a seperate dir
2) run this code with your mods
3) move files back
code;
Sub LoadFiles()
Dim F
Dim x As Double
x = 0
F = Dir("C:\Excelfiless\*.XLS")
'If F = "" Then MsgBox "Directory doesn't exist!": GoTo Ex
Application.ScreenUpdating = False
On Error GoTo FileErr
Do While Len(F) > 0
'Do your stuff here
'
'This should be last
x = x + 1
F = Dir()
Loop
MsgBox x & " Files have been updated"
GoTo Ex
Exit Sub
FileErr:
MsgBox Err.Number & Chr(13) & _
Err.Description, _
vbMsgBoxHelpButton, _
"File load Error", _
Err.HelpFile, _
Err.HelpContext
Ex:
Application.ScreenUpdating = True
End Sub
Ivan
