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

.

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 Workbook is already open and you are trying to open it again, then your code may throw an exception. To overcome this issue, you need to check this.

Therefore as part of this, You are going to learn the 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 the 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@vmlogger.com, www.vmlogger.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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. lakshya gupta

    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

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest