CODEWITHSUNDEEP
vbaexcel
Manya Mohan
Manya Mohan

Reputation: 41

Check if workbook exists, if yes then check if its open. if open then activate, if closed then open it

I am working on a VBA program where I need to do following:

When the button is clicked (the macro is run):

  1. Check if the MS EXCEL workbook exists in the folder. If not, then give a message that 'workbook does not exist' and VBA program should end.

  2. If workbook exists, then check if workbook closed or open. If its closed, then open the workbook and VBA program should move with fruther steps.

  3. If the sheet is open then activate the workbook and VBA program should move with fruther steps.

I have written this so far but its not working:

Sub test()
    Dim WbookCheck As Workbook

    On Error Resume Next
    Set WbookCheck = Workbooks("Weekly Report.xls")
    On Error GoTo 0
     filepaths = "c:\clients\work\Weekly Report.xls"
    If Dir("filepaths") = False Then
        MsgBox "Please save the latest file under the name 'US Sector Flow Weekly Report' and run the macro again"
        Exit Sub
    ElseIf WbookCheck Is Nothing Then
        Workbooks.Open "c:\clients\work\Weekly Report.xls"
    Else
        WbookCheck.Activate
    End If
Workbooks("Weekly Report.xls").Activate

Sheets("This week").Select
    Sheets("This week").Copy Before:=Workbooks( _
        "Consolidated.xls").Sheets(1)

End Sub

Upvotes: 0

Views: 28216

Answers (1)

Tim Williams
Tim Williams

Reputation: 166296

Sub test()

    Dim WbookCheck As Workbook

    On Error Resume Next
    Set WbookCheck = Workbooks("Weekly Report.xls")
    On Error GoTo 0

    If WbookCheck Is Nothing then 'not open....

        filepaths = "c:\clients\work\Weekly Report.xls"

        If Dir(filepaths) = "" Then
            MsgBox "Please save the latest file under the name" & _
              " 'US Sector Flow Weekly Report' and run the macro again"
            Exit Sub
        Else
            'file exists - open it
            Set WbookCheck = Workbooks.Open(filepaths)
        End If
    End If

    with WbookCheck
        .Activate
        .Sheets("This week").Copy _
                Before:=Workbooks("Consolidated.xls").Sheets(1)
    end with

End Sub

Upvotes: 5

Related Questions