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>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

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
Top