error

[jono19872006]

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?

[VBA] 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[/VBA]

[Bob Phillips]

[VBA]
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 While Len(fName) > 0

If Right(fName, 54) = "_[User Report - 30] - excelsummary cluster report.xls" Then

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
End If
Loop

Application.ScreenUpdating = True
[/VBA]

[jono19872006]

Hi thanks - this is what i wanted, however when i try and run this code, it crashes out in excel before even opening a file to format, is there a better way that i could use this type of macro?

[Bob Phillips]

You need to put it in a procedure

[VBA]
Sub MyCode
... the code
End Sub[/VBA]

and then run that procedure.

[jono19872006]

a procedure for which part?

[Bob Phillips]

The code. Procedure equals macro (roughly speaking).

[jono19872006]

The code above is an extract, basically i loop through four folders (as per above) calling different macros for the formats. The code is in a procedure i have just cut out the loop part.

It was working before, but excel doesnt seem to be able to process that code for some reason?

[Bob Phillips]

All I have done is to move the test for a string ending to within the loop. If you make it part of the loop control, the loop ends as soon as it finds a name that does not end with that.

Adapt your actual code in the same way.

[Teeroy]

I think getting the next fname needs to be outside the IF statement i.e.
[VBA]
End If
fName = Dir 'get next filename
[/VBA]
currently fname only changes if the filename satisfies the IF. Otherwise it keeps looping on the same file (that doesn't satisfy).

Doing the same thing over and over and getting nowhere, sounds like going to work on a bad day