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>
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>