Workbook

This page allows you to understand the Workbook object and the Workbooks collection in VBA macro programming.

In Excel, each workbook can contain several sheets which contain many cells.

If you use ThisWorkbook property of the Application object, the object return is the workbook where the Visual Basic code is running. Most of the time, this is the same as the active workbook.

There are a lot of methods, properties and events attached to the workbook object. The workbook object is, in fact, an element of the workbooks collection.

Below, several useful codes around the workbooks collxection and workbook object. You will find the main tools for handling workbooks. Knowing the object means being able to automatically create workbooks, save them, open them, protect them, delete them.

Create a new workbook

The first thing to know about workbook is how can you create a new workbook using a VBA macro.

To create a workbook, we begin by declaring new objects as binders. Then, we can use the Add method of the Workbooks collection and finally use the Set tool to match this new workbook to our declared variable.

The following procedure will show you how to create a new workbook, and how can you copy a sheet from a workbook to another using the Copy method of the sheet object.

In order not to see the execution of your screen, do not forget the good practices by deactivating the setting of your screen.

Sub CreateNewWorkbook()
    
    'Deactivated
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim WbMacro, WbNew As Workbook
    
    Set WbMacro = ActiveWorkbook
    Workbooks.Add 'Create a new workbook
    Set WbNew = ActiveWorkbook

    'Copy Paste the tab Weather Data to the new wbk
    WbMacro.Sheets("Weather Data").Copy after:=WbNew.Sheets(1)
    WbNew.Sheets(1).Delete 'Remove the Sheet1

    'Activated
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Open an existing workbook

The main method to know about the workbook are how you can create, open and save.

To open a workbook you just have to use the Open method with the path of the file as an argument.

In the example below, we write all the path of the file to open it :

Sub OpenWorkbook()

    Dim FilePath As String

    FilePath = "C:\Desktop\VBASKILLS\Startup.xls"
    Workbooks.Open (FilePath)

End Sub

In the example below, we use the GetOpenFilename method to select a file. This reduces the risks of the bug code because the user will choose his file in the system. This procedure works for Mac Os and Windows users.

Sub OpenWorkbook()

    Dim FilePath As String

    FilePath = Application.GetOpenFilename
    Workbooks.Open (FilePath)

End Sub

As seen in the article Project Explorer, you can also use the Application.FileDialog(msoFileDialogOpen) object to choose a file in the system, but it will work only for windows users.

Sub OpenWorkbook()
'works only for windows 

    Dim FilePath As String

    With Application.FileDialog(msoFileDialogOpen)
            .AllowMultiSelect = False ' To select only one file
            .Show 'Display the dialog box
            'Write the path of the file selected on B2
            FilePath = .SelectedItems(1)
    End With

    Workbooks.Open (FilePath)

End Sub

A good practice is to let the users select the file needed before the launching of the macro.

Open a text file as workbook

It is also possible to open a “.txt” file using the OpenText method if your data to analyse are in a text file.

Sub OpenWorkbookTxt()

    Workbooks.OpenText filename:="Thesaurus.txt", dataType:=xlDelimited, tab:=True

End Sub

Loop on workbooks

To loop on all workbooks open you just have to use a For Each loop on the Workbooks collection.

Sub LoopWorkbooks()
'Loop on all workbooks

    Dim Wbki As Workbook 'The counter
  
    For Each Wbki In Workbooks

        '... your instructions ...

    Next Wbki

End Sub

Save a workbook for the first time

After your macro is created a workbook, retrieved data reworked this data for your analyzes and reports, your need will be to save your work for the first time. For this, use the SaveAs method.

In the arguments of the method, you can even protect your workbook with a password.

Below the list of arguments available : FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local.

You can use all Excel type of file format such as xlAddIn (*.xla), xlCSV (*.csv), xlCSVMac (*.csv), xlOpenXMLWorkbook (*.xlsx), xlOpenXMLWorkbookMacroEnabled (*.xlsm), xlTextMac (*.txt),xlTextMSDOS (*.txt), xlXMLSpreadsheet (*.xml)…

Sub SaveWorkbook()
'Save a workbook

    Dim WbkAnalyis As Workbook 'The counter

    Set WbkAnalyis = ActiveWorkbook

    WbkAnalyis.SaveAs Filename:="Finance.xlsm" FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

Save changes of a workbook

To save the change of workbook already in the computer you can just use the Save method.

Sub SaveChangesWorkbook()
'Save the changes of a workbook

    Dim WbkAnalyis As Workbook 'The counter

    Set WbkAnalyis = ActiveWorkbook

    WbkAnalyis.Save

End Sub

Save all changes of workbooks

To save the change of several workbooks already open in the computer use the Save method with a For Each loop.

Sub SaveAllWorkbooks()

    Dim wb As Workbook

    For Each wb In Workbooks
        wb.Save
    Next wb

End Sub

Save all workbooks and check the ReadOnly status

The code below uses a loop For Each Next on open Excel workbooks and checks the status of read-only.

If the file is not read-only, then it saves the workbook. When the workbook is read-only, you can’t do any changes. To override the read-only, simply re-save the workbook in a different location, thus creating a copy.

Sub SaveWorkbookNotReadOnly()
'Save all workbooks which are not in read-only

    Dim Wbk As Workbook
 
    'Loop on all workbooks
    For Each Wbk In Application.Workbooks
        If Not Wbk.ReadOnly Then ' Condition on Read Only
            Wbk.Save 'Save the workbook
        End If
    Next Wbk

End Sub

Close the workbook and choose to save or not

To close a workbook, already save in a path location of your compute, nothing easier, use the Close method. As argument, the close method can save the workbook if needed using the boolean value True on the parameter SaveChanges.

If you are working on a VBA code which needs to open other Excel workbooks to retrieve data, if you do not change anything in the Excel workbook opened previously, then it is better to close this workbook without saving it, so you will save execution time especially if the latter is a big file.

As seen in good practices, it is also necessary to deactivate Excel alerts, doing that your code is not interrupted by a message indicating if you are sure that you do not want to save your workbook.

Sub CloseWorkbookWithoutSave()
'Close all workbooks without save

    Application.DisplayAlerts = False
    
    Dim WbNew As Workbook
    
    Workbooks.Add
    Set WbNew = ActiveWorkbook
    WbNew.Close SaveChanges:=False 'The False cancelled the saving

    Application.DisplayAlerts = True
    
End Sub

Protect & unprotect the workbook

The read-only file restricts changes being made, but this can easily be counteracted by saving it again.

The best security method is to use a password on a workbook with the VBA Protect method. As the Excel sheet, you can use a password to protect all the workbook.

Sub ProtectWorkbook()
'Lock the workbook with a password

    Dim WbkMacro As Workbook
    
    Set WbkMacro = ActiveWorkbook

    WbkMacro.Protect Password:="azerty123", _
       Structure:=True, Windows:=True

End Sub

To unprotect the file, use the method Unprotect and the password.

Sub UnprotectWorkbook()
'UnLock the workbook with a password

    Dim WbkMacro As Workbook
    
    Set WbkMacro = ActiveWorkbook

    WbkMacro.UnProtect Password:="azerty123"

End Sub

Protect all workbooks

To protect all workbooks, we just need to add a For Each… Next loop in the procedure above.

Sub ProtectAllWorkbooks()
'Lock all workbooks with a password

    Dim Wbki As Workbook
  
    For Each Wbki In Workbooks
        Wbki.Protect Password:="azerty123", _
           Structure:=True, Windows:=True
    Next Wbki

End Sub

Test if the workbook has a password

When you work on a workbook, it can be useful to test if the workbook is protected or not by a password using the property HasPassword of the object.

Sub CheckIfPasswordOnWorkbook()
'Check if the workbook has a password

    Dim WbkMacro As Workbook
    
    Set WbkMacro = ActiveWorkbook

    If WbkMacro.HasPassword = True Then 
        MsgBox "The Workbook is protected by a password."
    End If

End Sub

Print the workbook

To print the entire workbook, use the PrintOut method. This method take has arguments the following values : From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFilename, IgnorePrintAreas.

Sub PrintWorkbook()
'Print all the workbook

    Dim Wbki As Workbook 'The counter
    
    Set Wbki = ActiveWorkbook

    Wbki.PrintOut

End Sub

Divided each sheet in a new workbook

Based on filters, sometimes you need to divide your work with one sheet by workbook for the analysis required. It can be due to a perimeter, a location, an activity sector or other criteria.

So after having made your analysis, you can split the file into several files.

Sub CreateOneWorkbookforOneSheet()
  
    Dim Sh As Sheet
    Dim Wbk As Workbook
    
    'Loop on all sheets in your workbook using ThiWorkbook property
    For Each Sh In ThisWorkbook.Worksheets

        Set Wbk = Workbooks.Add
        '... others instructions ...
        Sh.Copy Before:= Wbk.Sheets(1) 'Copy your data
        Wbk.Sheets(1).Delete 'Delete a sheet
        Wbk.SaveAs "C:\Users\Desktop\Accounting\" & Sh.Name & ".xlsx" 'Save
        Wbk.Close 'Close the workbook already saved

    Next Sh

End Sub

Launch automatically a procedure with workbook action

Some specific macros can launch automatically depending on the actions.

These are called events.

For example, the instructions written in a macro having the name App_WorkbookOpen will automatically launch the instructions of the procedure when the file is opened. For more details, see the Automatic Sub article in the Tips category.