Activate workbook using VBA in Excel ~ Workbook.Activate

The Activate method of the Workbook class is used to put on top the selected workbook. Here I will show different ways in which to specify which workbook you want to activate.

As an example, your macro in your Excel workbook adds a new workbook which automatically appears on top. To get the Excel workbook that contains the macro on top again, refer to it as ThisWorkbook and call the Activate method:

Dim wb As Workbook: Set wb = Workbooks.Add
ThisWorkbook.Activate	

In the documentation the more exact but somewhat mysterious phrasing Activates the first window associated with the workbook is used. To understand this we must be aware that the same workbook can be open in multiple windows, which allows you to work in different places in the workbook (sheets, ranges) at the same time.

Activate workbook by filename

If you know the filename you can use that to identify the workbook to be put in front.

Workbooks("Demo.xlsm").Activate

Activate workbook with variable name

Using a variable name instead of the string literal make your code more flexible. Additionally having a Workbook variable wb allows you to do more than one action on the selected workbook.

Dim strWorkbook As String: strWorkbook = "Demo.xlsm"
Dim wb As Workbook
Set wb = Workbooks(strWorkbook)
wb.Activate	

Activate workbook with full path

As the image above shows the workbooks to be identified by the filename with extension - excluding path. Code to get select the workbook to activate then comes in two steps:

1. Determine the filename to identify the workbook;
2. Apply the Activate method.

below code uses the below FileName function wich takes the required part from the full name including path on disk.

Dim strFile As String: strFile = "C:\temp\test.xlsm"
Dim strFileName As String
strFileName = FileName(File:=strFile)
Workbooks(strFileName).Activate

Public Function FileName(File As String) As String
    '?FileName("c:\a\b\c.txt") -> c.txt
    FileName = Right(File, Len(File) - InStrRev(File, "\"))
End Function

Activate workbook with wildcard

Sometimes, you know the filename follows a certain pattern, but contains a veriable part, e.g. a weeknumber. Identification in collections needs to be exact, you can't use wildcards directly.

Dim wb As Workbook
For Each wb In Workbooks	
	If wb.Name Like "Dem*.xlsm" Then
		wb.Activate
		Exit For
	End If
Next

Activate workbook and sheet

To activate both a workbook and a sheet on it, you basically can do one an the other.

Dim wb As Workbook
Set wb = Workbooks(Index:="data123.xlsx")
wb.Activate
Dim ws As Worksheet
Set ws = wb.Sheets(Index:="Countries")
ws.Activate

Or maybe a bit nicer by showing the sheet belongs to the workbook using the With structure:

Dim wb As Workbook
Set wb = Workbooks(Index:="data123.xlsx")
With wb
	.Activate
	.Sheets(Index:="Countries").Activate
End With