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.

 

image 4

 

Users are saying about us...

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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube