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

http://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.

 

image 4

 



2 thoughts on “Open and Close Excel Workbook using VBA

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>