Find sheet and error handler

[trprasad78]

Hi all,

i have macro to run in excel file, before run that macro it has to check whether it have sheet name called "Costing Delivery" if that sheet is not their, macro should not run in that file.

if "Costing Delivery" is their, it has to run the Macro if not it has to go to next (open next file and run the macro)

some times macro not running to all the excel files, if costing delivery sheet not found it get exit from enter program but it has to continue till file is nil


correct the macro in between two lines below (=====)

'==========================================
Sub Create_ExportSheet()
'Check Sheet="Costing Delivery" is their if not it will exit.

On Error Resume Next
Dim wsTest2 As Worksheet
Const strSheetName As String = "Costing Delivery"

Set wsTest2 = Nothing


On Error Resume Next
On Error GoTo 0


On Error Resume Next
'Check Sheet ("Export") is their, if not sheet will be added
Dim wsTest As Worksheet
Const sSheetName As String = "Export"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(sSheetName)
On Error GoTo 0

If wsTest Is Nothing Then
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sSheetName
End If
'=========================================
'Add Header
Worksheets("Export").Select
If Range("A2") = "Name" Then
Range("a2").Select
Else

and macro continues

below i am giving macro which used for run the macro in all my sub folders just for your understanding, no changes is required for below code , its working fine.

Sub RunMacroInSubfolders()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    Dim objFile As Object
    Dim MyFolder As String
    Dim wkbOpen As Workbook
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim CalcMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
   
    'Change the path accordingly
    MyFolder = "F:\Excel Schooling\Kaar_New\Dec'16"
   
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(MyFolder)
    Set wkb = ActiveWorkbook
    Set wks = ActiveSheet
   
    For Each objSubFolder In objFolder.SubFolders
        For Each objFile In objSubFolder.Files
            Set wkbOpen = Workbooks.Open(objFile.path)
            'Your code here
            Call Create_ExportSheet
           
           
            wkbOpen.Close savechanges:=True
        Next objFile
    Next objSubFolder
   
    With Application
        .Calculation = CalcMode
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    MsgBox "Completed...", vbInformation
   
End Sub

 

[Monty]

Hello Prasad.

Below code will check if Sheet name ( "Costing Delivery") exist or not..

Sub Test()
Call sheetExists("Costing Delivery")
End sub

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function

Hope this helps..Let me know happy to help you.

 

[trprasad78]

I found the issue , actual issue with following code its running only folder which have excel file, if its find the folder is empty it exit from the program.

but it actually if folder is empty it has to move to another folder and subfolders

please correct the following macro , it has move next if the folder is empty.

Sub RunMacroInSubfolders()

   Dim objFSO AsObject
   Dim objFolder AsObject
   Dim objSubFolder AsObject
   Dim objFile AsObject
   Dim MyFolder AsString
   Dim wkbOpen As Workbook
   Dim wkb As Workbook
   Dim wks As Worksheet
   Dim CalcMode AsLong

   With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
   EndWith
   
   'Change the path accordingly   MyFolder = "F:\Excel Schooling\Kaar_New\Dec'16"
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(MyFolder)
   Set wkb = ActiveWorkbook
   Set wks = ActiveSheet
   
   ForEach objSubFolder In objFolder.SubFolders
       ForEach objFile In objSubFolder.Files
           Set wkbOpen = Workbooks.Open(objFile.path)
           'Your code here           Call Create_ExportSheet
           
           
            wkbOpen.Close savechanges:=True
       Next objFile
   Next objSubFolder
   
   With Application
        .Calculation = CalcMode
        .ScreenUpdating = True
        .EnableEvents = True
   EndWith

    MsgBox "Completed...", vbInformation
   
EndSub
[\CODE]