CODEWITHSUNDEEP
vbaexcel
Lucas Crostarosa
Lucas Crostarosa

Reputation: 1192

Referencing External Workbook VBA through Variable and If Statement

Currently my code looks like this and it works fine

    Dim WorkbookVar As Workbook
    Dim Path As String
    Path = "C:\Path.xlsx"
    Set WorkbookVariable = Workbooks.Open (Filename:=MCDistroNumberPath)

But I would like it to go a step further and have an if statement saying If this workbook is already open Then skip it and start running the rest of my macro.

How would I go about doing that?

Upvotes: 0

Views: 404

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Consider putting it into its own function like below.

Public Function GetOrOpenWorkbook(ByVal sFullPath As String) As Workbook

    Dim wbReturn As Workbook

    On Error Resume Next
        Set wbReturn = Workbooks(Dir(sFullPath))
    On Error GoTo 0

    If wbReturn Is Nothing Then
        Set wbReturn = Workbooks.Open(sFullPath)
    End If

    Set GetOrOpenWorkbook = wbReturn

End Function

Sub test()

    Dim wb As Workbook

    Set wb = GetOrOpenWorkbook("C:\Path\Path.xlsx")

End Sub

The Dir function will return just the file name without the path. If a workbook with that name is already open, it will return that workbook. If not, it attempts to open it.

Upvotes: 3

Related Questions