VBA Workbook Object

VBA Workbook Object in Excel

Workbook Object in MS Excel VBA. Workbook is an object in Microsoft Excel represents a member of the Workbooks collection. Workbooks collection contains list of all workbook objects. Again each workbook consists of different objects like Worksheets, Ranges, cells, Charts, shapes, chart sheets, etc.

VBA Workbook Object Complete Reference Tutorial

Let us see a complete guide on VBA Workbook object. So let’s start learning about complete VBA Workbook object explained with examples in this tutorial.

Task Description Action performed (Sample Example)
Workbook Reference
  1. ActiveWorkbook Reference
  2. ThisWorkbook Reference
  3. Reference Workbook by Index
  4. Reference Workbook by Object
  5. Workbooks in Workbooks Collection Reference
  6. Reference Workbook Explicitly(By Name)
Declare Assign Workbook variable ‘Variable Declaration
Dim oWorkbook As Workbook

‘Define Object Variable
Set oWorkbook = Workbooks(“VBA Tutorial.xlsm”)
Create new Workbook Workbooks.Add
Create new Workbook with Name ActiveWorkbbok.SaveAs Filename:=”VBA Tutorial.xlsm”
Open Workbook Workbooks.Open (“D:\VBA Tutorial.xlsm”)
Open Workbook as Read Only Workbooks.Open Filename:=”D:\VBA Tutorial.xlsm”, ReadOnly:=True
Activate Workbook Workbooks(“VBA Tutorial.xlsm”).Activate
Rename Workbook Name “D:\VBAF1\Old_File.xlsx” As “D:\VBAF1\New_File.xlsx”
Copy Workbook ‘Variable declaration
Dim oFSO As FileSystemObject
Dim sFilePath As String
Dim dFilePath As String

‘Source & Destination File Path
sFilePath = “D:\VBAF1\VBA Functionsa.xlsm”
dFilePath = “D:\Blog\VBA Functionsa.xlsm”

‘Reference Workbook by Object named oWorkbook
Set oFSO = CreateObject(“Scripting.FileSystemObject”)

‘Copy Workbook
oFSO.CopyFile sFilePath, dFilePath, True
Save Workbook Workbooks(“VBA Tutorial.xlsm”).Save
SaveAs Workbook ActiveWorkbook.SaveAs Filename:=”D:\VBAF1\New_File.xlsx”
List all open Workbooks ‘VBA List Open Workbooks in Excel
Sub VBA_List_All_Open_Workbooks()

‘Variable declaration
Dim xWorkbook As Workbook
Dim sWorkbookName As String
Dim iCount As Integer

‘Intialise value to a variable
iCount = 2

Sheets(“WB_Names”).Range(“A1”) = “Names of Available Workbooks”

‘Loop through all workbooks
For Each xWorkbook In Application.Workbooks
Sheets(“WB_Names”).Range(“A” & iCount) = xWorkbook.Name & vbCrLf

‘Increase value
iCount = iCount + 1
Next

End Sub
List Open Unsaved Workbooks ‘Variable declaration
Dim xWorkbook As Workbook

‘Loop through all workbooks
For Each xWorkbook In Application.Workbooks

‘Check Workbook is current Workbook or not
If xWorkbook.Name <> ThisWorkbook.Name Then
‘Check if the file names has an extension

If InStr(Right(xWorkbook.Name, 5), “.xl”) > 0 Then
‘Save and Close Workbook
xWorkbook.Close SaveChanges:=True
Else
Debug.Print xWorkbook.Name
End If
End If
Next
Check Workbook Exists ‘Variable declaration
Dim sFilePath As String

‘Value assigned to a variable
sFilePath = “D:\FolderName\Sample.xlsx”

‘Check Workbook exists or not
If Dir(sFilePath) = “” Then
‘Workbook is not available
MsgBox “File doesn’t exist.”, vbCritical, “File is not available”
Else
‘Workbook is available
MsgBox “File exists.”, vbInformation, “File available”
End If
Active & Current Workbook Name ActiveWorkbook.Name
ThisWorkbook.Name
Check for Open Workbook ‘Variable declaration
Dim oWorkbook As Workbook

For Each oWorkbook In Workbooks
If oWorkbook.Name = “Test_Workbook.xls” Then
MsgBox “Specified Workbook is open.”, vbInformation, “VBAF1 : Automation Made Easy”
Else
MsgBox “Specified Workbook is not open.”, vbCritical, “VBAF1 : Automation Made Easy”
End If
Next
Difference Between ActiveWorkbook and ThisWorkbook ActiveWorkbook represents the Workbook in active window which has focus on the screen.
ThisWorkbook represents the current Workbook in which the current VBA code is running or executing.
Get Workbook Path & Location ActiveWorkbook.Path
ActiveWorkbook.FullName
Protect Workbook ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=”12345″
UnProtect Workbook ActiveWorkbook.Unprotect Password:=”12345″
RunAutoMacros Workbook With ActiveWorkbook
.RunAutoMacros xlAutoOpen
.Open
End With
Access Workbook by Index Workbooks (1)
Create backup of current workbook ActiveWorkbook.SaveCopyAs “D:\VBAF1\New_File.xlsx”
Close Workbook and Save ActiveWorkbook.Close SaveChanges:=True
Close Workbook without Save ActiveWorkbook.Close SaveChanges:=False
Delete Workbook Kill “D:\VBAF1\Old_File.xlsx”
Close All Workbooks without prompt ‘Variable Declaration
Dim oWorkbook As Workbook

Application.ScreenUpdating = False
For Each oWorkbook In Application.Workbooks
If oWorkbook.Name <> ThisWorkbook.Name Then
oWorkbook.Close
End If
Next
Application.ScreenUpdating = True
Delete All Workbooks in a folder On Error Resume Next
Kill “D:\VBAF1\*.xl*”

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

3 thoughts on “VBA Workbook Object in Excel”

  1. organic

    Howdy very cool blog!! Man .. Excellent .. Superb .. I will bookmark your site and take the feeds additionallyI’m satisfied to find numerous useful information here within the put up, we need work out extra strategies on this regard, thanks for sharing. . . . . .

  2. how to get help in windows 10

    This is very interesting, You’re a very skilled blogger. I have joined your feed and look forward to seeking more of your excellent post.
    Also, I’ve shared your site in my social networks!

  3. Jesus

    We are a bunch of volunteers and starting a brand new scheme
    in our community. Your website offered us with valuable info to work on. You have performed an impressive task and our whole community can be
    grateful to you.

Leave a Reply