jono19872006
08-28-2012, 01:25 AM
I have the code below which looks in a folder and formatsall the spreadsheets as per the code which is called in.
Originally the code said just Do While Len(fName) > 0- but i have also added in the "and" part as the reports which areformatted all end in the same selection of text so i wanted this as an extracheck.
However now if a file in the folder doesnt end in theright text then it doesnt format any files in the folder - at which part ofthis code would i put "on error resume" code so that if the file doesnot meet the set criteria it ignores and moves on to the next one withoutopening?
Dim fPath AsString
Dim fName AsString
If Right(fPath,1) <> "\" Then fPath = fPath + "\"
Dim wb As Workbook
'First Loop forSummary Reports (Final Reports)
'Setup - codeto start open the files
Application.ScreenUpdating = False
fPath = "ad\store\Personal\M\maginj90\MACROTEST"
fName =Dir(fPath & "*.xls") 'start a list of filenames
Do WhileLen(fName) > 0 And Right(fName, 54) = "_[User Report - 30] - excelsummary cluster report.xls"
Set wb =Workbooks.Open(fPath & fName) 'openfound file
'call macro toformat reports
CallFormat_Summary_Reports
CallRename_Summary_sheet
'end of codefor formatting, code to loop thorugh the rest of the folder
Application.DisplayAlerts = False
wb.CloseTrue 'close/save
Application.DisplayAlerts = True
fName = Dir 'get next filename
Loop
Application.ScreenUpdating = True
Originally the code said just Do While Len(fName) > 0- but i have also added in the "and" part as the reports which areformatted all end in the same selection of text so i wanted this as an extracheck.
However now if a file in the folder doesnt end in theright text then it doesnt format any files in the folder - at which part ofthis code would i put "on error resume" code so that if the file doesnot meet the set criteria it ignores and moves on to the next one withoutopening?
Dim fPath AsString
Dim fName AsString
If Right(fPath,1) <> "\" Then fPath = fPath + "\"
Dim wb As Workbook
'First Loop forSummary Reports (Final Reports)
'Setup - codeto start open the files
Application.ScreenUpdating = False
fPath = "ad\store\Personal\M\maginj90\MACROTEST"
fName =Dir(fPath & "*.xls") 'start a list of filenames
Do WhileLen(fName) > 0 And Right(fName, 54) = "_[User Report - 30] - excelsummary cluster report.xls"
Set wb =Workbooks.Open(fPath & fName) 'openfound file
'call macro toformat reports
CallFormat_Summary_Reports
CallRename_Summary_sheet
'end of codefor formatting, code to loop thorugh the rest of the folder
Application.DisplayAlerts = False
wb.CloseTrue 'close/save
Application.DisplayAlerts = True
fName = Dir 'get next filename
Loop
Application.ScreenUpdating = True