1. Introduction #

VBScript can be used in most Office applications, most commonly in Excel and Word. It’s a scripting language that can be used to work with the applications outside of a UI. Compared to using clicks via selectors, it can save a lot of maintenance time, as selectors sometimes change their names during updates. Furthermore, a lot of functionality can be done with a few lines of code, rather than multiple actions and selectors, to click through the UI to accomplish the same task.

Name	Note	Link
Scripts in general	Active Directory, Group Policy, Data, Office etc	https://www.vbsedit.com/scripts/
Scripts for Office	Various scripts for Word, Excel and PowerPoint	https://www.vbsedit.com/scripts/office/

Example of script for Excel:

Example of script for Word:

2. Excel #

Description	Script
Attach to running Excel process	Set objExcel = GetObject(, “Excel.Application”) objExcel.visible=true
Start a new Excel process	Set objExcel = CreateObject(”Excel.Application”) objExcel.visible=true
Open workbook from file path Attach to specific Workbook Attach to specific Sheet Attach to active Workbook Attach to active Sheet	Set objWorkbook = objExcel.Workbooks.Open(“C:\folder1\Folder2\MyExcel.xlsx”) Set objWorkbook = objExcel.Workbooks(“Folder1”) Set objSheet = objWorkbook.Sheets(“Sheet1”) Set objWorkbook = objExcel.ActiveWorkbook Set objSheet = objWorkbook.ActiveSheet
Close open Workbook	objExcel.Application.DisplayAlerts = False objWorkbook.Close
Create sheet	objWorkbook.Sheets.Add.Name = “New Sheet”
Delete sheet	objExcel.Application.DisplayAlerts = False objSheet.Delete objExcel .Application.DisplayAlerts = true
Save copy of excel sheet as different path/file type	objWorkbook.SaveCopyAs “C:\Users\rpahelp\Documents\Test.csv”
To specific cell	objSheet.Cells(row, column)
For cell ranges	objSheet.Range(“A1:C10”)
Write in cell	objSheet.Cells(row, column).Value = “Test”
Change Font Bold italic underline	objSheet.Cells(row, column).Font.Bold = true objSheet.Cells(row, column).Font.FontStyle = “Bold Italic” objSheet.Range(“A1:D1”).Font.Name = “Arial” objSheet .Range(“A1:D1”).Font.Size = “18”
Format cell value to another format type (eg text / number) in a column**	objSheet.Range(“A1”).EntireColumn.NumberFormat = “@” objSheet.Range(“A1”).EntireRow.Interior.ColorIndex = 45 objSheet.Range(“D1”).NumberFormat = “General”
Conditional Formatting***	objSheet.Range(“A1:A5”).FormatConditions.Delete objSheet.Range(“A1:A5”).FormatConditions.Add 1, 7, “0” objSheet.Range(“A1:A5”).FormatConditions(1) .Interior.Colorindex = 3
Color cell*	objSheet.Range(“A1”).Interior.Color = RGB(255, 0, 0) objSheet.Range(“A2”).Font.Colorindex = 4
Remove formatting from cells	objSheet.Range(“A1: %FirstFreeColumn% : %FirstFreeRow% “).clearformats
Delete rows, note that there is a range in the brackets	objSheet.Rows(“1:1”).EntireRow.Delete
Auto-align columns	for col=1 to %FirstFreeColumn% objSheet.columns(col).AutoFit() next
Copy cells to another sheet	objSheet.Range(“A1: %FirstFreeColumn% : %FirstFreeRow% “).Copy objSheet2.Range(“A1”)
Copy cells from Excel file to Excel file	Set objWorkbook2 = objExcel.Workbooks.open(“C:\Users\henrik\Documents\Mappe2.xlsx”) objWorkbook.Worksheets(“Sheet1”).Range(“A1:A2”).copy objWorkbook2.Worksheets(“Sheet1” ).Range(“A1”).pastespecial
Simple sorting of a range based on a column	set range = objSheet.Range(“A2:Z100”) set varKey = objSheet.Range(“A2:Z100”) range.Sort(varKey)
Save open Excel workbook as CSV semicolon separated	Dim objExcel, objWorkbook Set objExcel = GetObject(, “Excel.Application”) Set objWorkbook = objExcel.Workbooks(“Folder1”) objExcel.visible=true objWorkbook.SaveAs ” %FilePathCSV% “, 6, , , , , , , , , , True objWorkbook.Close False objExcel.Quit

*Cell Color

Color index if you use Colorindex instead of RGB.

**Number format values

	General
	0
	$#,##0.00;[Red] $#,##0.00
	_($* #,##0.00_);_($* (#,##0.00);_($* “-“??_);_(@_)
	m/d/yy
	[$-F400]h:mm:ss am/pm
	0.00%
	# ?/?
	0.00E+00
	@

***Conditional formatting

There are a few options for formatting:

xlFormatConditionType

xlCellValue	1
xlExpression	2

xlFormatConditionOperator

xlBetween	1
xlNotBetween	2
xlEqual	3
xlNotEqual	4
xlGreater	5
xlLess	6
xlGreaterEqual	7
xlLessEqual	8

Example

In the code itself, the order is important, in relation to which rule is at the top, and the one with the first priority, marked in red. Note that you can set a fixed value yourself or refer to a cell, see green marking. You cannot mix ConditionType ( xlCellValue, xlExpression ), as none of the formatting will take effect in that case.

After the VBScript, it looks like this if you open Manage Rules…

3. Word #

Please note that in the case of Word, some functionalities do not work depending on which language pack you have installed. The following has been tested on both a Danish and an English installation, and works on both.

Description	Script
Attach to running Word process	Set objWord = GetObject(,”Word.Application”) objWord.Visible = True
Start a new Word process	Set objWord = CreateObject(”Word.Application”) objWord.visible=true
Create new Word document	Set objDoc = objWord.Documents.Add()
Attach to running Word document	Set objDoc = objWord.ActiveDocument
Attach to page content	Set objSelection = objWord.Selection
Open specific file	Set objDoc = objWord.Documents.Open(“c:\scripts\word\testdoc.doc”)
Save document as	objDoc.SaveAs(“C:\Scripts\Word\testdoc.doc”)
Save document	objDoc.Save
Close Word	objWord.Quit
Close Word without saving	objDoc.Saved = TRUE objWord.Quit
Write in the document	objSelection.TypeText “Test”
Line break in document	objSelection.TypeParagraph()
Change Font Bold italic underline	objSelection .Font.Bold = true objSelection .Font.Name = “Arial” objSelection .Font.Size = “18”
Line spacing	objSelection.ParagraphFormat.LineSpacing = 10
Add image	Set objShape = objDoc.Shapes objShape.AddPicture(“C:\Scripts\Logo.jpg”)