REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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.

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
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

15 Comments

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

    Outstanding collection of examples for sure!

  2. PNRao March 27, 2014 at 9:43 AM

    Thank you Faraz!
    Regards-PNRao!

  3. kkchan July 18, 2015 at 12:51 PM

    Thank you!

  4. PNRao July 18, 2015 at 1:56 PM

    You are welcome!Thanks-PNRao!

  5. lokesh reddy July 22, 2015 at 6:41 AM

    Hi,

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

  6. jaydev November 4, 2015 at 12:03 PM

    thanks a lot….

  7. PNRao November 7, 2015 at 11:24 AM

    Welcome Jaydev!
    Thanks-PNRao!

  8. Divya June 30, 2016 at 12:25 AM

    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

  9. Guzman September 9, 2016 at 11:29 PM

    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.

  10. Gamini October 21, 2016 at 11:11 AM

    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.

  11. PNRao October 23, 2016 at 9:26 AM
  12. Raja March 8, 2017 at 5:26 PM

    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
    ————————–

  13. sap hana professional December 13, 2017 at 6:07 PM

    Thank you so much for the post

  14. jenifer January 28, 2018 at 11:00 AM

    hi,
    i want to read data present after custom word in the text file.Can you help me!

  15. fff May 23, 2019 at 8:44 PM

    if you copy and paste and it doesent work its because of the ” around the workbook name you need to re-enter the quotes or excel wont work

Leave A Comment