macro / vba

jono19872006

Board Regular
Joined
Mar 13, 2012
Messages
184
I have got the code below which i have adapted off the internet.
It loops through a whole folder and applies my macro to whatever is in the folder.
note i have taken my macro out as it is quite long and replaced with "MY CODE" below.
Effectively about 30 reports are pulled off a system then using this i will format all with the click of a button.

However the first criteria of the macro is to delete a tab called "summary". This will always be the same for these reports.
However if already formatted reports or different excel files are in there then the macro stops working as there is no "summary" tab to delete.
Therefore I want to edit the macro to basically say only open Excel files, then if when the file is open if there are two tabs which are called X & Y then run macro else exit, how can i adjust this so it is included in the looped code below?

Option Explicit</SPAN>

Sub SLAM_Reports()</SPAN></SPAN>
Dim fPath As String</SPAN>
Dim fName As String</SPAN>
Dim wb As Workbook</SPAN>


Dim Response As VbMsgBoxResult</SPAN></SPAN>
Response = MsgBox("Do you want to continue and run this macro - any changes cannot be undone?", vbQuestion + vbYesNo)</SPAN></SPAN>
If Response = vbNo Then Exit Sub</SPAN></SPAN>


'Setup</SPAN>
Application.ScreenUpdating = False</SPAN>
fPath = "\\ad\store\Personal\M\maginj90\MACRO TEST\"</SPAN> 'remember final \ in this string</SPAN>
fName = Dir(fPath & "*.xls") 'start a list of filenames</SPAN>

Do While Len(fName) > 0</SPAN>
Set wb = Workbooks.Open(fPath & fName) 'open found file</SPAN>



MY CODE HERE</SPAN>



'End With</SPAN>
wb.Close True 'close/save</SPAN>
fName = Dir 'get next filename</SPAN>
Loop</SPAN>

Application.ScreenUpdating = True</SPAN>

MsgBox "The Macro has finished and the reports have been formatted", vbInformation, "Macro Finished"</SPAN></SPAN>

End Sub</SPAN>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Add this function to test whether a sheet exists:

Code:
Function SheetExists(wb As Workbook, strName As String) As Boolean
Dim ws As Worksheet

On Error GoTo err1:
Set ws = wb.Sheets(strName)

Set ws = Nothing

SheetExists = True

Exit Function

err1:

SheetExists = False

End Function

Then in your code add:

Code:
If SheetExists(wb,"sheetname1") = True And SheetExists(wb,"sheetname2") = True Then

MY CODE HERE

End If
 
Upvote 0
I have got the code below which i have adapted off the internet.
It loops through a whole folder and applies my macro to whatever is in the folder.
note i have taken my macro out as it is quite long and replaced with "MY CODE" below.
Effectively about 30 reports are pulled off a system then using this i will format all with the click of a button.

However the first criteria of the macro is to delete a tab called "summary". This will always be the same for these reports.
However if already formatted reports or different excel files are in there then the macro stops working as there is no "summary" tab to delete.
Therefore I want to edit the macro to basically say only open Excel files, then if when the file is open if there are two tabs which are called X & Y then run macro else exit, how can i adjust this so it is included in the looped code below?

Option Explicit</SPAN>

Sub SLAM_Reports()</SPAN></SPAN>
Dim fPath As String</SPAN>
Dim fName As String</SPAN>
Dim wb As Workbook</SPAN>


Dim Response As VbMsgBoxResult</SPAN></SPAN>
Response = MsgBox("Do you want to continue and run this macro - any changes cannot be undone?", vbQuestion + vbYesNo)</SPAN></SPAN>
If Response = vbNo Then Exit Sub</SPAN></SPAN>


'Setup</SPAN>
Application.ScreenUpdating = False</SPAN>
fPath = "\\ad\store\Personal\M\maginj90\MACRO TEST\"</SPAN> 'remember final \ in this string</SPAN>
fName = Dir(fPath & "*.xls") 'start a list of filenames</SPAN>

Do While Len(fName) > 0</SPAN>
Set wb = Workbooks.Open(fPath & fName) 'open found file</SPAN>



MY CODE HERE</SPAN>



'End With</SPAN>
wb.Close True 'close/save</SPAN>
fName = Dir 'get next filename</SPAN>
Loop</SPAN>

Application.ScreenUpdating = True</SPAN>

MsgBox "The Macro has finished and the reports have been formatted", vbInformation, "Macro Finished"</SPAN></SPAN>

End Sub</SPAN>

I think if you add a line like On error resume next just before getting to the delete portion of your macro, that should resolve it

So when it encounters a workbook without "Sumary tab" it skips and goes to the next workbook instead of throwing up an error
 
Upvote 0
I think if you add a line like On error resume next just before getting to the delete portion of your macro, that should resolve it

Note, that is very bad practice to do this. What happens if another error occurs unrelated to this? You would never notice it, and it may look like something ran fine whereas in fact it didn't.

If you use on error make sure that they are handled in some way.
 
Upvote 0
Note, that is very bad practice to do this. What happens if another error occurs unrelated to this? You would never notice it, and it may look like something ran fine whereas in fact it didn't.

If you use on error make sure that they are handled in some way.

Thanks gaj although "very bad practice" sounded a little harsh. Not good practice would ave been preferrable,
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top