Macro Execution Error

[Nithya]

Hi ,

I am using the below macro for deleting all the worksheets except two worksheets(i.e sheet1 and sheet2) from a folder which has a dump of xls files.

When I am executing this macro.., the macro is prompting to select the files manually, which i don't want to do the macro has to automatically pick all the xls from the folder and delete the unnecessary tabs.

Can any one please tell me where is the error in the below code.

Sub Delete_Trans()
    
    Dim strFldrPath As String
    
    strFldrPath = "D:\Trans"

    Dim CurrentFile As String
    Dim wb As Workbook, ws As Worksheet
    Application.ScreenUpdating = False

    CurrentFile = Dir(strFldrPath & "\" & "*.xls")

    While CurrentFile <> vbNullString
        Set wb = Workbooks.Open(strFldrPath & "\" & CurrentFile)
        If SheetExists("sheet1", wb) And SheetExists("sheet2", wb) Then
              Application.DisplayAlerts = False
          For Each ws In wb.Sheets
              If Not (ws.Name = "sheet1" Or ws.Name = "sheet2") Then
                ws.Delete
              End If
          Next ws
           Application.DisplayAlerts = True
          End If
        wb.Close True
        CurrentFile = Dir
    Wend

    Application.ScreenUpdating = True
    
End Sub


Private Function SheetExists(SheetName As String, wb As Workbook) As Boolean
    
    Dim wsCheck As Worksheet
    On Error GoTo NotFound
    Set wsCheck = wb.Sheets(SheetName)
    SheetExists = True
    Exit Function
    
NotFound:
    SheetExists = False
    
End Function

 

[Osvaldo Palmeiro]

Hi. Try this to open the xls files.

Sub OpenAllXls()
Dim path As Variant
Dim excelfile As Variant
        path = "H:\folder\"
            excelfile = Dir("*.xls")
        ChDir "F:\folder\"
    Do While excelfile <> ""
    Workbooks.Open Filename:=path & excelfile 'w/o password or
    'Workbooks.Open Filename:=path & excelfile, Password:="password" 'all files with same pwd
    excelfile = Dir
'your code to delete sheets
    Loop
End Sub