Use VBS to populate and format Excel documents

by

The below script is an example of how to create, populate and format an Excel document from a VBS script.

The script is commented, but please feel free to comment if you have any questions.

This code was last tested June 2022 using the latest Office 365 version of Excel

What is covered in this post?

In this example code we will

  • Creating a new workbook
  • Select a sheet
  • Changing the name of the sheet
  • Adding some data in specific locations and via a loop
  • Change the font to bold and the font size
  • Freezing panes
  • Change the column widths to a specific size and the auto size to fit the contents
  • Change the text and background colors
  • Saving the document

The Result

The result of the following example code will generate an example spreadsheet that looks like this

The Code

Change the variable “strExcelPath” as required to a location you have permission to save to.

'Bind to the Excel object
Set objExcel = CreateObject("Excel.Application")
 
'Create a new workbook.
objExcel.Workbooks.Add
 
'Select the first sheet
Sheet = 1
 
'Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)
 
'Name the worksheet
objSheet.Name = "VBS_Excel_Example"
 
'Set the save location
strExcelPath = "d:\Vbs_Excel_Example.xlsx"
 
'--------------------------------------------------------
'Populate the worksheet with data
'--------------------------------------------------------
'   objSheet.Cells(row, column).Value = "Whatever"
 
'Add some titles to row 1
objSheet.Cells(1, 1).Value = "Name" 'Row 1 Column 1 (A)
objSheet.Cells(1, 2).Value = "Description" 'Row 1 Column 2 (B)
objSheet.Cells(1, 3).Value = "Something Else" 'Row 1 Column 3 (C)
 
'Add some data using a loop
For row = 2 to 10
	objSheet.Cells(row, 1).Value = "Item Name"
	objSheet.Cells(row, 2).Value = "Item Description"
	objSheet.Cells(row, 3).Value = "Item Something Else"
Next
 
'--------------------------------------------------------
' Format the spreadsheet
'--------------------------------------------------------
'Put the first row in bold
objSheet.Range("A1:C1").Font.Bold = True
 
'Change the font size of the first row to 14
objSheet.Range("A1:C1").Font.Size = 14
 
'Freeze the panes
objSheet.Range("A2").Select
objExcel.ActiveWindow.FreezePanes = True
 
'Change column A and B to use a fixed width
objExcel.Columns(1).ColumnWidth = 20
objExcel.Columns(2).ColumnWidth = 30
 
'Change column C to autofit
objExcel.Columns(3).AutoFit()
 
'Change the background colour of column A to a light yellow
objExcel.Columns(1).Interior.ColorIndex = 36
 
'Change the font colour of column C to blue
objExcel.Columns(3).Font.ColorIndex = 5
 
'--------------------------------------------------------
' Save the spreadsheet and close the workbook
'--------------------------------------------------------
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
 
'Quit Excel
objExcel.Application.Quit
 
'Clean Up
Set objSheet = Nothing
Set objExcel = Nothing

21 thoughts on “Use VBS to populate and format Excel documents”

  5. “I want to format the excel sheet to a table and i have some count in the second column, so i want to sort it with highest value at the top” – using vb script. How can i do it.. Can you help me with this.

    Reply

  6. Hi Phil, I ran your program and it works well. However, when I open the excel file, two excel workbooks are opened. The target file, as well as another Book1. Do you know how I can stop this from happening?

    Reply

    • Hi,

      What version of Excel are you using?

      I have just made a minor change to the script so it generates and xlsx file rather than an older xls file. That seems to work better for me, but I have been unable to reproduce the issue you are having. I am using Excel 2016

      Phil

      Reply

  7. I will use your script as reference to generate excel file from Sales Order / Purchase Order (or whatever report ) in Dynamics Nav 2009, then attached to email. Thanks

    Reply

  8. I use an app to analyse some data and and the create an Excel report. So my starting point is an existing excel with 5 columns and a variable number of rows. What I am looking for is to just auto-format the excel report. Ideally, just space the column widths and alternate the coloring of the rows. Is this possible with this script?

    Thanks
    Ash

    Reply

  9. Hi Phil,
    objExcel.Columns(3).AutoFit() works for column ‘C’.
    I want to Autofit entirecolumn of all worksheet.
    Can you please help?

    Reply

    • Hi,

      You can AutoFit a range of columns on a certain worksheet using the below;

      Worksheets(“Sheet1”).Columns(“A:Z”).AutoFit

      Or if you want to Auto Fit all columns in all of the worksheets this will do the job

      For Each ws In Worksheets
      ws.Columns.Autofit
      Next ws

      I hope that helps?

      Thanks
      Phil

      Reply
  10. Pingback: Excel 2003 XML format not automatically opening in Excel 2010 x64 - QuestionFocus

  11. Have tried to use some of your code above but am getting the following error on the line: Set objExcel = CreateObject(“Excel.Application”)

    Microsoft VBScript runtime error ‘800a0046’

    Permission denied: ‘CreateObject’

    Reply

  13. Hello Phil,
    Thanks a lot for you post. I created very nice looking excel, but not able to save it. Could you clarify the reason?
    Thanks.
    Ludmila

    Reply

    • Hi,

      Thanks for the comment.

      Are you able to elaborate further?
      Are you getting any errors back?

      Have you set your save location (Line 17), and do you have write permission to that location?

      Thanks
      Phil

      Reply

  15. Hello !!
    My Excel Sheet is with 50 Rows. During my process, I want to insert new row after Row# 45.
    How I can do it using VB script?

    Reply

  16. In my project, I’ve created one Excel file with 50 Rows. I’m comfortable with that.

    Now during my process, I need to insert few rows after 45th row. How can I do that using VB Scripting ?

    Reply

    • Hi,

      Hope the below example helps

      ‘Insert a single row starting at row 2
      insertStartsAtRow = 2
      objSheet.Rows(insertStartsAtRow).Insert

      ‘Insert 3 rows starting at row 4
      insertStartsAtRow = 4
      numberOfRowToInsert = 3

      insertEndsAtRow = insertStartsAtRow + numberOfRowToInsert – 1
      insertExpression = insertStartsAtRow & “:” & insertEndsAtRow

      objSheet.Rows(insertExpression).Insert

      Thanks
      Phil

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.