Dear Beginners,
In this article you are going to learn How to open an Excel Workbook using VBA. Before opening an already saved Excel Workbook, We need to confirm whether that Workbook is already opened or not. Why it is required to check whether that file is open or not?

Answer is, In case that Workbook is already open and you are trying to open it again, then your code may throw en exception. To overcome this issue, you need to check this.
Therefore as part of this You are going to learn following things:

1. Check if Workbook is open ?
2. How to Check File (Workbook) Exists or Not
3. Open a Workbook from a given path
4. How to Open an Excel Workbook by Launching OpenFile Dialog Box

How to Check Workbook is open?

Below is the VBA Code for checking whether File is open or Not. This Function takes Workbook Complete Path as Input and Returns a Boolean Flag : True or False. If the File is open then True else False.

Public Function Isopen(Myworkbook As String) As Boolean

    On Error Resume Next
    Set wBook = Workbooks(Myworkbook)
    If wBook Is Nothing Then
        Isopen = False
        Isopen = True
    End If
    Exit Function
End Function

2. How to Check Workbook Exists or Not

Before We Open a Workbook given at a path, you need to check whether that file exists or not. Below Function takes Complete Path of the Workbook as Input and Returns a Boolean Flag. If the File Exists, it returns True else False.

Public Function FileExist(Myworkbook As String) As Boolean
    Dim InputFile
    On Error GoTo Err
    InputFile = FreeFile
    Open Myworkbook For Input As InputFile
    Close InputFile
    FileExist = True
    Exit Function
    FileExist = False
End Function

3. Open a Workbook saved at a given path

Finally you have reached at your final Task i.e. Opening Excel Workbook. Once you got the positive response from both the above functions, means File is NOT Opened and File Exists, then you can go ahead and use your FileOpen Statement as shown in the below Function.

Sub Open_Workbook()
'*  Macro written by Vishwamitra Mishra,                                                                     *
'*  [email protected], on 09 Jun, 2012                                        *
    Dim Myworkbook As String
    ' Give the path of your Workbook
    Myworkbook = "C:\Users\Vish\Desktop\Book2.xlsx"
    ' Check if the File is open or Not?
    If Isopen(Myworkbook) = False Then
        ' Check if File Exists
        If FileExist(Myworkbook) = True Then
            'Open the Workbook
            Workbooks.Open Filename:=Myworkbook
            MsgBox ("File Does not Exist. Check the Path")
        End If
        'If Workbook is already Open then Activate it
    End If
End Sub

4. How to Open an Excel Workbook by Launching OpenFile Dialog Box

Sometimes you DO NOT know the path of the File which you want to open, then you can use the FileOpen Dialog Box, Where you can select the File from any directory in the System and open it.

Below function will first launch one File Open Dialog Box with Filter .xls , .xlsx and .xlsm. It means, user will be allowed to select only XLS or XLSX or XLSM File.

Sub Open_File_Dialog_Box()

    NewWorkbook = Application.GetOpenFilename( _
            FileFilter:="Excel 2003 (*.xls),*.xls,Excel 2007 (*.xlsx),*.xlsx,Excel 2007 (*.xlsm),*.xlsm", _
            Title:="Select an Excel File", _
    If NewWorkbook = False Then
        Exit Sub
        Workbooks.Open Filename:=NewWorkbook
    End If

End Sub

Open Excel Workbook - File Open Dialog

Open Excel Workbook - File Open Dialog

To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

To Check out more Excel Tips and Tricks, visit Excel Tips and Tricks


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!