VBA Open Excel File – Explained with Examples!

Home/VBA/VBA Open Excel File – Explained with Examples!

VBA code to open Excel File will help you to open Excel Workbook using VBA. VBA open excel file Examples to show you use of Workbook.Open method in Excel VBA 2003, 2007, 2010, 2013.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA Code to Open an Excel File using Workbooks.Open Method

We can open Excel Workbook using Workbooks.Open Method. Following are the VBA Examples and syntax of VBA Code to Open an Excel File.

VBA Code to Open an Excel File using Workbooks.Open Method: Syntax

Here is the syntax to Open an Excel File using VBA. Here we need to pass the Workbook path to open.


Workbooks.Open("C:\temp\test.xlsx")

VBA Code to Open an Excel File using Workbooks.Open Method: Examples

The following Excel VBA example will open the test.xlsx file in the C:\temp folder. Here we are opening the workbook and setting to an object. This will help us to re use the open workbook in the program.

Sub sbVBA_To_Open_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\temp\test.xlsx")
End Sub

VBA Code to Open an Excel File Explained:
‘Starting procedure to write VBA code to open excel file
Sub sbVBA_To_Open_Workbook()

‘Declaring the wb variable as workbook
Dim wb As Workbook

‘Opening a workbook and setting to the wb object for further use
Set wb = Workbooks.Open(“C:\temp\test.xlsx”)

‘Ending the sub procedure
End Sub

VBAopen excel file: Why we are using an Object

This is is the best approach to opening and assigning workbook to an object. This will help us to re use the Opened workbook and deal with its worksheets, ranges and other objects. The following example will show you how to access the different examples of opened workbook by setting and assigning to an object.

The below VBA Code example will get the Name of the Opened Workbook
We are using the Workbook.Name property to get the workbook name of the opened workbook.

Sub sbVBA_To_Open_WorkbookName()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\temp\test.xlsx")
'This will return the workbook name
MsgBox wb.Name
End sub

The below VBA Code will get the count of worksheets in the Opened Workbook
We are using the Worksheets.Count property of workbook to get the number of worksheets in the opened workbook.

Sub sbVBA_To_Open_Workbook_Worksheets_Count()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\temp\test.xlsx")
'This will return number of worksheets in the workbook
MsgBox wb.Worksheets.Count
End sub

The below VBA Code example will get the first worksheet Name of the Opened Workbook
We are using the Worksheet.Name property of workbook to get the name of the of worksheets in the opened workbook.

Sub sbVBA_To_Open_Workbook_Worksheets_Count()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\temp\test.xlsx")
'This will return the first sheet name of the workbook
MsgBox wb.Sheets(1).Name
End sub

The below VBA Code example will get the Range C2 value of the Worksheet “Main” of the Opened Workbook
We are using the Worksheet.Range object of workbook to get the Range value of the worksheets in the opened workbook.

Sub sbVBA_To_Open_Workbook_Worksheets_Count()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\temp\test.xlsx")
'This will return the Range C2 value of the worksheet "Main"
MsgBox wb.Sheets("Main").Range("C2")
End sub
LIMITED TIME OFFER
By |April 22nd, 2014|VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. Sunil March 14, 2016 at 12:04 PM - Reply

    Thanks sir

Leave A Comment


Related pages


excel vba addresssorting in excel 2007unhide pivot tableusing excel advanced filterspeed up excel macroopen xlsx file with excel 2003useful excel templatesvba workbook activatehow to create bar chart in excel 2007vb copy fileexcel worksheet propertiesexcel 2003 remove duplicatesexcel macro if multiple conditionsvba coding in excel for beginnersmacros excel tutorialvba declarationhow to insert row on excelword vbaexcel isnumber functionexcel vba text boxrecord excel macrovb6 questions and answersmacro buttons excelproject analysis template excelcountif examplekpi dashboard examples excelhow to do vlookup in excel 2010 step by stepbasics of sas programmingexcel beginners tutorialformat vba codevariables vbainteractive dashboard excelms excel if statementsvba sort excelvba range clearhow to unhide the hide columns in excelwhere to find pivot table in excelhow to merge data from different excel filesinterview questions of testing for freshersmacro excel exampleswhat is advanced filter in excelvba char functionexcel 2007 dashboardsexcel vba breakvba connect to sql databasestacked bar chart in excel 2010excel vba proceduremaking a checkbox in excelgantt charts in excel 2007excel dashboard kpiexcel macro string concatenationvba code to unprotect excel workbookrelease plan template excelvba tutorial accesscount number of rows in excel vbaexport excel to txtmerge cell exceluse of offset function in excelexcel unprotect workbook without passworddelete record vbaunlock an excel spreadsheettrend analysis excel templatecol_index_num vlookuptext box controlwhat is the use of vlookupexcel macros advancedhow lock excel sheetexcel 2010 offset functionexcel vba switch casecountif in excel 2007how to insert row on excelexcel unshare workbookhow to arrange cells in excelexcel templates timelinesql queries excelcells.select vba