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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a new module from Insert menu
- Copy the above code and Paste in the code window
- Press F5 to see the output
- You should see opened workbook
- 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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a new module from Insert menu
- Copy the above code and Paste in the code window
- Press F5 to see the output
- You should see opened workbook
- Save the file as macro enabled workbook
Close Excel 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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a new module from Insert menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute the procedure
- 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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a new module from Insert menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute the macro
- The file has closed, You can’t see the file
Outstanding collection of examples for sure!
Thank you Faraz!
Regards-PNRao!
Thank you!
You are welcome!Thanks-PNRao!
Hi,
This is the best site to learn excel and VBA with example .Thankyou for providing for us
thanks a lot….
Welcome Jaydev!
Thanks-PNRao!
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
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.
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.
Please refer this post:
http://analysistabs.com/excel-vba/save-workbook-to-specific-folder/
Thanks-PNRao!
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
————————–
Thank you so much for the post
hi,
i want to read data present after custom word in the text file.Can you help me!
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