Open and Close Excel Workbook using VBA

In this article we will learn about to close and open method in Excel VBA that is used for opening the close workbook and also close the open workbook.

We create command button in Excel sheet and assign macro.

To know about how to create Activex control click on link

https://www.exceltip.com/tips/activex-controls-in-microsoft-excel-2010.html

How to open Excel workbook through VBA?

Follow Below given steps and coding:-

Insert 2 Command Buttons.
First command button we use to right click to the mouse on command button.
VBE page will get open and copy the below coding and paste in the VBE page.
To open the workbook assign below mentioned macro.

Sub sOpenWorkbook()
' define variable for file name
Dim csFileName As String
' get filename from cell A1 on Sheet1
csFileName = ThisWorkbook.Sheets("Example Open and Close").Range("A1")
' open the workbook
Workbooks.Open csFileName
MsgBox csFileName & " opened"
End Sub

Second command button we use to right click to the mouse on command button.
VBE page will get open and copy the below coding and paste in the VBE page.
To open the workbook assign below mentioned macro.

Sub sCloseWorkbook()
' define variable for file name
Dim csFileName As String
' get filename from cell A1 on Sheet1
csFileName = ThisWorkbook.Sheets("Example Open and Close").Range("A1")
' close the workbookWorkbooks(Split(csFileName, "\")(UBound(Split(csFileName, "\")))).CloseMsgBox Split(csFileName, "\")(UBound(Split(csFileName, "\"))) & " closed"
End Sub

Both macros will assign to the command buttons, we have to use below macro coding.

Option Explicit
Sub sExample()Const csFileName As String = _
"C:\Test\Master.xlsx" Workbooks.Open
csFileNameWorkbooks(Split(csFileName, "\")(UBound(Split(csFileName, "\")))).Close
End Sub

Sub sOpenWorkbook()
' define variable for file name
Dim csFileName As String
' get filename from cell A1 on Sheet1
csFileName = ThisWorkbook.Sheets("Example Open and Close").Range("A1") ' open the workbook
Workbooks.Open csFileNameMsgBox csFileName & " opened"
End Sub

Sub sCloseWorkbook()
' define variable for file name
Dim csFileName As String ' get filename from cell A1 on Sheet1
csFileName = ThisWorkbook.Sheets("Example Open and Close").Range("A1")
' close the workbookWorkbooks(Split(csFileName, "\")(UBound(Split(csFileName, "\")))).CloseMsgBox Split(csFileName, "\")(UBound(Split(csFileName, "\"))) & " closed"
End Sub

Note: - You have to mention the path according your file.

Jorge Rodrigues
June 30, 2015 at 12:08 pm

This code only works if the Code Project has no password for access, isn't it?

Reply ↓
- Admin
  June 30, 2015 at 12:18 pm
  
  Yes
  
  Reply ↓