An example of using a VBS script to create, populate and format an Excel document

'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 = "c:\Vbs_Excel_Example.xls"
 
'--------------------------------------------------------
'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 " & row & " Name"
	objSheet.Cells(row, 2).Value = "Item " & row & " Description"
	objSheet.Cells(row, 3).Value = "Item " & row & " 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