Excel Macro Tutorial : How to Open Excel Workbook using Excel Macro

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
    Else
        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
Err:
    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,                                                                     *
'*  Info@learnexcelmacro.com, www.learnexcelmacro.com 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
        Else
            MsgBox ("File Does not Exist. Check the Path")
        End If

    Else
        'If Workbook is already Open then Activate it
        Application.Workbooks(Myworkbook).Activate

    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", _
            MultiSelect:=File)
    If NewWorkbook = False Then
        Exit Sub
    Else
        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

Related posts:

  1. Auto Open Macro / Auto Run Macro Sometimes in Excel Macro, you need to execute a Macro...
  2. Excel Macro Tutorial : Excel Form Dear Beginner, In this article I am going to explain...
  3. How to get list of All files in a Folder and Sub-folders By using File System Object, we can get list of...
  4. Excel Macro Tutorial : VBA Spin Button In the previous Article How to write Excel Macro –...
  5. How to Disable Right Click in Excel Workbook Some times for some security reason or some other reason,...

One Response to “Excel Macro Tutorial : How to Open Excel Workbook using Excel Macro”

  1. lakshya gupta says:

    thanks for sharing this important information , but i like to know how to open multiple excel file saved with different name in same folder and i have copy data from all files and save in new excel workbook, this is require to consolidate data in one excel workbook. plz advice can this macro be created using record macro

Leave a Reply

Comments

comments