{"id":12141,"date":"2012-06-09T18:10:27","date_gmt":"2012-06-09T18:10:27","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1810"},"modified":"2022-08-06T22:34:16","modified_gmt":"2022-08-06T22:34:16","slug":"open-workbook-using-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/06\/open-workbook-using-vba\/","title":{"rendered":"Excel Macro Tutorial : How to Open Excel Workbook using Excel Macro"},"content":{"rendered":"
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?<\/p>\n
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.<\/p>\n
Therefore as part of this, You are going to learn the following things:<\/p>\n
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<\/strong> or False<\/strong>. If the File is open then True else False.<\/p>\n 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<\/strong> else False<\/strong>.<\/p>\n 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<\/strong> and File Exists<\/strong>, then you can go ahead and use your FileOpen Statement as shown in the below Function.<\/p>\n 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.<\/p>\n Below function will first launch one File Open Dialog Box with Filter .xls , .xlsx and .xlsm<\/strong>. It means, user will be allowed to select only XLS or XLSX or XLSM File.<\/p>\n\r\n\r\nPublic Function Isopen(Myworkbook As String) As Boolean\r\n\r\n On Error Resume Next\r\n Set wBook = Workbooks(Myworkbook)\r\n If wBook Is Nothing Then\r\n Isopen = False\r\n Else\r\n Isopen = True\r\n End If\r\n Exit Function\r\n \r\nEnd Function\r\n<\/code><\/pre>\n
2. How to Check Workbook Exists or Not<\/h1>\n
\r\nPublic Function FileExist(Myworkbook As String) As Boolean\r\n Dim InputFile\r\n On Error GoTo Err\r\n InputFile = FreeFile\r\n Open Myworkbook For Input As InputFile\r\n Close InputFile\r\n FileExist = True\r\n Exit Function\r\nErr:\r\n FileExist = False\r\nEnd Function\r\n\r\n<\/code><\/pre>\n
3. Open a Workbook saved at a given path<\/h1>\n
\r\nSub Open_Workbook()\r\n \r\n'*************************************************************************************************************\r\n'* Macro written by Vishwamitra Mishra, *\r\n'* Info@vmlogger.com, www.vmlogger.com on 09 Jun, 2012 *\r\n'*************************************************************************************************************\r\n \r\n Dim Myworkbook As String\r\n \r\n ' Give the path of your Workbook\r\n Myworkbook = \"C:\\Users\\Vish\\Desktop\\Book2.xlsx\"\r\n \r\n ' Check if the File is open or Not?\r\n If Isopen(Myworkbook) = False Then\r\n ' Check if File Exists\r\n If FileExist(Myworkbook) = True Then\r\n 'Open the Workbook\r\n Workbooks.Open Filename:=Myworkbook\r\n Else\r\n MsgBox (\"File Does not Exist. Check the Path\")\r\n End If\r\n \r\n Else\r\n 'If Workbook is already Open then Activate it\r\n Application.Workbooks(Myworkbook).Activate\r\n \r\n End If\r\n \r\nEnd Sub\r\n<\/code><\/pre>\n
4. How to Open an Excel Workbook by Launching OpenFile Dialog Box<\/h1>\n
\r\n\r\nSub Open_File_Dialog_Box()\r\n\r\n NewWorkbook = Application.GetOpenFilename( _\r\n FileFilter:=\"Excel 2003 (*.xls),*.xls,Excel 2007 (*.xlsx),*.xlsx,Excel 2007 (*.xlsm),*.xlsm\", _\r\n Title:=\"Select an Excel File\", _\r\n MultiSelect:=File)\r\n If NewWorkbook = False Then\r\n Exit Sub\r\n Else\r\n Workbooks.Open Filename:=NewWorkbook\r\n End If\r\n\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n