Open and Close Excel Workbook using VBA

Home/Excel VBA/Open and Close Excel Workbook using VBA

Description:

Sometimes we may want to open and close an existing workbook using VBA. You can set the opened workbook to an object, so that it is easy to refer your workbook to do further tasks.

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


Open Excel Workbook using VBA

The following Macros will allow to open an esisting workbook using Open method.

Open Esisting Workbook in Excel VBA – Example Cases:

Open existing workbook

You can use the following code to Open an existing Workbook and save in the C drive using Open method.

Code:
Sub Open_ExistingWorkbook()
Workbooks.Open "C:\WorkbookName.xls"
'OR
'Workbooks.Open Filename:="C:\WorkbookName1.xls"
End Sub
Output:

You should see opened workbook along with existing workbook.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see opened workbook
  7. Save the file as macro enabled workbook
Set opened workbook to an object

You can set the Object to opened workbook, so that it is easy to refer to your workbook to do further tasks.

Code:
Sub Set_Open_ExistingWorkbook()
Dim wkb As Workbook
Set wkb = Workbooks.Open("C:\WorkbookName.xls")
'OR
Set wkb = Workbooks.Open(Filename:="C:\WorkbookName1.xls")
End Sub
Output:

You should see newly opened workbook along with existing workbook.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see opened workbook
  7. Save the file as macro enabled workbook

Close Excel Workbook using VBA

Close an Opened Workbook using VBA
You can use Close Method of workbook to close an opened workbook in Excel using VBA.

Close an Opened Workbook – Example Cases:

Close an Opened Workbook

You can use close method in the following way.The following code will close an opened Workbook.

Code:
Sub ExampleCloseWorkbook()
'Procedure to close an Excel worbook
'Close is an workbook method to close a workbook
ActiveWorkbook.Close
End Sub
Output:

When ever we run the above macro automatically the file will get closed.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute the procedure
  7. The file get closed, You can’t see the file
Close an Opened Workbook using object

You can set the Object to open workbook, so that it is easy to refer to your workbook to do further tasks. Using same object you can close an opened workbook.

Code:
Sub ExampleOpenAnExistingWorkbookSet()
'Declaration - declare an object for setting the workbook
Dim wkb As Workbook
'Open Workbook and set an object(wkb)
Set wkb = Workbooks.Open("C:\WorkbookName.xls")
'Close opened workbook using object
wkb.Close SaveChanges:=True
End Sub
Output:

When ever we run the above macro automatically the file will get closed.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute the macro
  7. The file has closed, You can’t see the file
LIMITED TIME OFFER
By |January 15th, 2013|Excel VBA|12 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

12 Comments

  1. Faraz A. Qureshi March 25, 2014 at 1:08 AM - Reply

    Outstanding collection of examples for sure!

    • PNRao March 27, 2014 at 9:43 AM - Reply

      Thank you Faraz!
      Regards-PNRao!

  2. kkchan July 18, 2015 at 12:51 PM - Reply

    Thank you!

    • PNRao July 18, 2015 at 1:56 PM - Reply

      You are welcome!Thanks-PNRao!

  3. lokesh reddy July 22, 2015 at 6:41 AM - Reply

    Hi,

    This is the best site to learn excel and VBA with example .Thankyou for providing for us

  4. jaydev November 4, 2015 at 12:03 PM - Reply

    thanks a lot….

    • PNRao November 7, 2015 at 11:24 AM - Reply

      Welcome Jaydev!
      Thanks-PNRao!

  5. Divya June 30, 2016 at 12:25 AM - Reply

    Hi, Can you tell me what is the coding for bring the data in our desired row using macro after created the userform.

    Example,

    Everyday i am entering data in excel manually.One day i created userform to enter data in that excel.after done everything(create userform&coding),when i am run that userform,the information which i put in the userform overrights the existing line which i typed manually.

    The userform data’s doesn’t start from blank cell or row.it’s over right the existing cell
    I need the assistance for this please
    Thanks

  6. Guzman September 9, 2016 at 11:29 PM - Reply

    I have one question about closing workbook.

    We can translate “Close an Opened Workbook using object” to this.

    Set wkb = Workbooks.Open(“C:\WorkbookName.xls”)
    ‘Close opened workbook using object
    Workbooks.Open(“C:\WorkbookName.xls”).close

    This line confuses me:
    Workbooks.Open(“C:\WorkbookName.xls”).close

    Any explanation? You have an open and close method.

  7. Gamini October 21, 2016 at 11:11 AM - Reply

    Hi Rao,

    Can you kindly tell me how to save and close the workbook by the filename. I tried but, I couldn’t get it.

  8. Raja March 8, 2017 at 5:26 PM - Reply

    Home:

    Private Sub cmdClose_Click()
    ThisWorkbook.Close
    End Sub

    Private Sub CmdForm_Click()
    frmEmpDetails.Show
    End Sub

    Sheets:

    Private Sub UserForm_Click()

    End Sub

    Private Sub prComboBoxFill()
    TRows = Worksheets(“Data”).Range(“A”).CurrentRegion.Rows.Count
    ComboBox1.Clear
    For i = 2 To TRows
    ComboBox1.AddItem Worksheets(“Data”).Cells(i, 1).Value

    Next i
    End Sub
    —————–
    Private Sub cmdSearch_Click()
    binNew = False
    TxtEmpNo.Text = “”
    txtEmpName.Text = “”
    txtEmpAdd1.Text = “”
    txtEmpAdd2.Text = “”
    txtEmpAdd3.Text = “”

    TRows = Worksheets(“Data”).Range(“A”).CurrentRegion.Rows.Count
    For i = 2 To TRows
    If Val(Trim(Worksheets(“Data”).Cells(i, 1).Value)) = Val(Trim(ComboBox1.Text)) Then

    TxtEmpNo.Text = Worksheets(“Data”).Cells(i, 1).Value
    txtEmpName.Text = Worksheets(“Data”).Cells(i, 2).Value
    TxtAdd1.Text = Worksheets(“Data”).Cells(i, 3).Value
    TxtAdd2.Text = Worksheets(“Data”).Cells(i, 4).Value
    TxtAdd3.Text = Worksheets(“Data”).Cells(i, 5).Value

    Exit For
    Exit If
    Next i
    If TxtEmpNo.Text = “” Then
    Else
    cmdSave.Enabled = True
    cmdDelete.Enabled = True
    End If
    End Sub
    ———————-
    Private Sub cmdNew_Click()
    blnNew = True
    TxtEmpNo.Text = “”
    txtEmpName.Text = “”
    txtEmpAdd1.Text = “”
    txtEmpAdd2.Text = “”
    txtEmpAdd3.Text = “”

    cmdClose.Caption = “Cancel”
    cmdNew.Enabled = False
    cmdDelete.Enabled = False
    End Sub
    ————————
    Private Sub cmdSave_Click()
    If Trim(TxtEmpNo.Text) = “” Then
    MsgBox “Enter Emp. No. “, vbCritical, “Save”
    TxtEmpNo.SetFocus
    Exit Sub
    End If
    Call prSave

    End Sub
    —————————-
    Private Sub prSave()
    If blnNew = True Then
    THows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
    With Worksheets(“Data”).Range(“A1”)
    .Offset(THows, 0).Value = TxtEmpNo.Text
    .Offset(THows, 1).Value = txtEmpName.Text
    .Offset(THows, 2).Value = TxtempAddr1.Text
    .Offset(THows, 3).Value = TxtempAddr2.Text
    .Offset(THows, 4).Value = TxtempAddr3.Text
    End With
    TxtEmpNo.Text = “”
    txtEmpName.Text = “”
    TxtempAddr1.Text = “”
    TxtempAddr2.Text = “”
    TxtempAddr3.Text = “”
    TxtempAddr4.Text = “”
    Call prComboBoxFill
    Else
    For i = 2 To TRows
    If Trim(Worksheets(“Data”).Cells(i, 1).Value) = Trim(ComboBox1.Text) Then
    Worksheets(“Data”).Cells(i, 1).Value = TxtEmpNo.Text
    Worksheets(“Data”).Cells(i, 2).Value = txtEmpName.Text
    Worksheets(“Data”).Cells(i, 3).Value = TxtempAddr1.Text
    Worksheets(“Data”).Cells(i, 4).Value = TxtempAddr2.Text
    Worksheets(“Data”).Cells(i, 5).Value = TxtempAddr3.Text
    TxtEmpNo.Text = “”
    txtEmpName.Text = “”
    TxtempAddr1.Text = “”
    TxtempAddr2.Text = “”
    TxtempAddr3.Text = “”
    Exit For
    End If
    Next i
    End If
    blnNew = False

    End Sub
    ———————-

    Private Sub cmdDelete_Click()
    TRows = Worksheets(“Data”).Range(“A1”).CurrentRegion.Rows.Count
    Dim strDel
    strDel = MagBox(“Delete ?”, vbYesNo, “Delete”)
    If strDel = vbYes Then
    For i = 2 To TRows
    If Trims(Worksheets(“Data”).Cells(i, 1).Value) = Trim(ComboBox1.Text) Then

    ‘ sheet1.range(i & “:” & i).Delete
    Worksheets(“Data”).Range(i & “:” & i).Delete

    TxtEmpNo.Text = “”
    txtEmpName.Text = “”
    TxtempAddr1.Text = “”
    TxtempAddr2.Text = “”
    TxtempAddr3.Text = “”
    TxtempAddr4.Text = “”
    Call prCoboBoxFill
    Exit For
    End If
    Next i
    If Trim(ComboBox1.Text) = “” Then
    cmdSave.Enabled = False
    cmdDelete.Enabled = False
    Else
    cmdSave.Enabled = True
    cmdDelete.Enabled = True
    End If
    End If
    End Sub
    ————————-
    Private Sub cmdClose_Click()
    If cmdClose.Caption = “Close” Then
    Unload e
    Else
    cmdClose.Caption = “Close”
    cmdNew.Enabled = True
    cmdDelete.Enabled = True

    End If
    End Sub
    ————————–

Leave A Comment


Related pages


excel workbook examplesexcel how to unhideexcel cylinder chartexcel and vba tutorialconstruction cost estimator excelhow to connect sql to excelexcel vba command buttonmacro in excel with exampleexcel hyperlink to websiteunprotect excel vba codeapplication worksheetfunction countadml ddl sqlvba fsoexcel hlookupwhat does vlookup meanweekly report format excelsales trend analysis excelrept functionmeeting minute template excelexcel vba vbcrlffrequently asked interview questions and answers for freshersvba excel loopvba excel programming examplesexcel how to delete duplicateshow to use vlookup in excel 2010 step by stepvlookup formulasvba direxcel short cuthow do i hide a column in excelvba paste from clipboardvlookup tutorial for beginnersadvanced excel exerciseslook up function in excelvba run commandvba developer resume sampleexcel developer modepassword protecting an excel spreadsheetvba code open filehow to remove protection from excel sheetvba countifsproject planning freewareexcel add row shortcuthow do i add the developer tab to the ribbonexcel vba current rowdelete entire row vbaproper formula in excelexcel estimate templatevba enumerationcreate checkbox in excel 2010excel if statement with 2 conditionscombobox additemsql query for beginnershow do you count rows in excelvb array exampleexcel substitute examplehow to merge cell in excel 2010lookup excel vbavba operatorsvba excel checkboxexcel vba fileproject dashboard excelunhide sheets macrovba filter criteriavba delete worksheetprograming in excelexcel metrics dashboardpassword protection excel 2010multiple if excel formulaunlock excel workbook passwordexcel macro save filevba workbookexcel 2003 gantt chart templatevba counta