VBScript Excel Tutorial 11: Mastering Excel Objects

Have you ever needed to perform repetitive Excel tasks like importing data, generating reports, or reconciling information between systems?

Manually working in Excel for these business-critical activities can be slow and error-prone.

That‘s why Excel automation is so valuable.

And in this VBScript Excel tutorial, you‘ll learn how to leverage Excel objects in VBScript code to programmatically create, read, write, and modify Excel files.

You‘ll be able to instantly format hundreds of rows, transfer data between systems, integrate Excel into apps – all without endless pointing and clicking.

According to Microsoft, over 1 billion people actively use Office apps like Excel. Yet most rely entirely on manual processes instead of automation.

So learning these VBScript skills will give you a major edge. Let‘s dive in!

Why Use VBScript for Excel Automation?

Before looking at the Excel object model, why choose VBScript as your automation language?

Here are some key benefits:

  • Native Windows scripting: VBScript ships built-in on Windows systems so you can run scripts right away without new infrastructure.

  • Lightweight syntax: The code is simple enough for non-developers to adopt for task automation.

  • Interacts easily with Office apps: VBScript provides direct access to manipulate Excel, Word, Outlook, and more.

  • Alternative to VBA: VBScript scripts can do nearly everything VBA macros can within Office documents. Often easier since it‘s separate from those file formats.

  • Scalable automation: Call VBScripts from batch files, scheduled tasks, etc. for large scale processing across the enterprise.

VBScript dominates Windows scripting even decades after its release due to these factors. It‘s the perfect on-ramp for both user-level and organization-wide Excel automation.

With that background, let‘s explore Excel‘s object model…

Excel Object Model Overview

The key to unlocking Excel automation is by interacting with its object model:

Excel object model diagram

As you can see above, Excel exposes:

  • Objects – Self-contained entities like Workbook, Worksheet, Chart
  • Properties – Attributes of those objects like Name, Visible, Index
  • Methods – Actions that can be performed like Open, Copy, Delete

VBScript uses this object model to manipulate Excel programatically.

Some key objects you‘ll work with include:

  • Application – Represents the Excel application itself
  • Workbook – An Excel file document
  • Worksheet – A single sheet within an Excel workbook
  • Range – Cell or cells in a worksheet

Within these objects are tons of useful properties and methods for reading, writing, and formatting Excel content.

Now let‘s walk through hands-on examples…

Creating New Excel Documents

To start automating, we first need to launch Excel itself.

The Application object represents a running Excel instance. Create it with:

Set excelObj = CreateObject("Excel.Application")

Next we‘ll add a new blank Workbook: 

Set wb = excelObj.Workbooks.Add

Workbooks contains all currently open Excel files. Its Add method creates a fresh workbook object we can now reference to populate data.

wb.Sheets(1).Cells(1,1).Value = "My Data"

We access cells through index notation – this writes "My Data" into row 1 column 1 on Sheet 1.

Finally save the document and quit Excel:

wb.SaveAs("C:\Reports\Report1.xlsx")
wb.Close 

excelObj.Quit  
Set excelObj = Nothing

And we‘ve successfully created an Excel file from scratch!

Opening and Reading Excel Files

Automation often involves processing existing Excel documents rather than creating new ones.

Launch Excel again:

Set excelObj = CreateObject("Excel.Application")

Then use Workbooks.Open to load files:

Set wb = excelObj.Workbooks.Open("C:\Files\myData.xlsx")

This opens myData.xlsx into memory for reading and manipulation.

Reading cell contents is easy through index notation again:

data = wb.Sheets(1).Cells(2,1).Value 

MsgBox "Cell (2,1) contains: " & data

This pops up the value from row 2 column 1 of Sheet 1.

Some other handy methods:

rowCount = wb.Sheets(1).UsedRange.Rows.Count 

colCount = wb.Sheets(1).UsedRange.Columns.Count

Set sourceRange = wb.Sheets(1).Range("A1:B10") ‘Select A1:B10

When finished, close and release object references:

wb.Close
excelObj.Quit  

Set excelObj = Nothing

And that covers the basics of reading Excel files with VBScript!

Writing Data to Excel Documents

In addition to extracting data, we can write values into Excel files.

After opening a workbook, access cells through index notation again:

wb.Sheets(1).Cells(1,1).Value = "Updated Data"

wb.Sheets(1).Cells(2,2).Value = Date()

This writes directly into the specified cells.

To insert a new row:

wb.Sheets(1).Rows(5).Insert

This shifts everything down 1 row from row 5 onward.

Some other useful write operations:

Set targetRange = wb.Sheets(1).Range("C1:D1") 
sourceRange.Copy Destination:=targetRange ‘Copy values 

wb.Sheets(1).Columns(5).AutoFit() ‘Auto-sizes column 5

With wb.Sheets(1).Rows(5).Font
    .Name = "Arial"
    .Size = 12
    .Bold = True  
End With ‘Format row 5 font

Most Excel formatting and cell writing tasks are supported.

Once again, don‘t forget to save and close up objects when finished.

Manipulating Sheets in a Workbook

So far we‘ve focused on cells and ranges inside existing sheets. But we can also modify the workbook structure itself.

To add a new worksheet:

Set ws = wb.Sheets.Add 

ws.Name = "MyNewSheet"

This appends a sheet titled "MyNewSheet".

To delete sheets:

wb.Sheets("MyOldSheet").Delete

Copying existing sheets:

wb.Sheets("Template").Copy After:=wb.Sheets(1)

This duplicates the Template sheet and inserts the copy after Sheet 1.

The options for adding, reordering and removing sheets are very flexible.

Transferring Data Across Workbooks

A common need is consolidating data across multiple Excel documents.

First open both workbook files:

Set sourceWb = excelObj.Workbooks.Open("C:\Files\Source.xlsx")   

Set targetWb = excelObj.Workbooks.Open("C:\Files\Target.xlsx")

Then copy the desired range from source and paste into target:

Set sourceRange = sourceWb.Sheets(1).Range("A1:B10")   

targetWb.Sheets(1).Range("C1").PasteSpecial psSource:=sourceRange

The PasteSpecial method transfers cell contents between workbooks.

Don‘t forget to save and close all your file changes once the transfer is complete!

VBScript vs VBA for Excel Automation

Now that you‘ve seen Excel automation capabilities with VBScript, how does it compare with VBA macros for Excel scripting?

While there is some overlap, here are the core differences:

VBScript VBA Macros
Location External .vbs scripts Embedded in Excel docs
Development Text editors VBA editor
Execution Windows Script Host Called internally from Excel
Deployment Distribute .vbs files Share macro-enabled Excel files
Security Lower, external scripts Higher, runs locally in Excel
Version Control Easier, script files Harder, encoded in files

In summary:

  • VBScript – Better for workflow automation across files.
  • VBA Macros – Better for self-contained file automation.

Determine which approach meets your specific automation needs.

Conclusion and Next Steps

In this VBScript Excel tutorial, you learned:

  • Excel‘s underlying object model
  • How to create, read, write, and format Excel files
  • Manipulate worksheets and transfer data between workbooks
  • Alternatives like VBA macros for Excel automation

These examples demonstrate VBScript‘s extensive capabilities for interacting with Excel dynamically.

You‘re now equipped to eliminate repetitive Excel tasks through the power of code!

To take this further:

  • Apply the concepts to model your own Excel processes
  • Look into the full Excel object model for advanced functions
  • Call scripts from batch files or Windows Task Scheduler
  • Explore interacting with other Office apps like Word and Outlook

Let me know in the comments if you have any other questions!

Read More Topics