Excel: load a (CSV) file, print it and exit Excel

WSH scripts provides a simple interface to access Office application like Microsoft Excel from a script. Below I like to demonstrate how to access Excel from a VBScript program. The script shall launch Excel, load a predefined file, print the content of the first worksheet and terminates Excel. To access Excel, you may use the following command in VBScript:

Set objXL = WScript.CreateObject ("Excel.Application")

This command retrieves an object reference to the Excel Application object. Because we omit here a version number, any installed Excel version (95/97/2000) will be invoked. If you like to invoke Excel 97 for instance, you must add also the internal version number as:

Set objXL = WScript.CreateObject ("Excel.Application.8")

Afterwards you can use the object variable objXL to access the Application object and its sub-objects from the script. The following sequence sets the properties of the Excel window:

objXL.WindowState = vbNormal ' Normal
objXL.Height = 300 ' height
objXL.Width = 400 ' width
objXL.Left = 40 ' X-Position
objXL.Top = 20 ' Y-Position
objXL.Visible = true ' show window

Then you may load a file (either an XLS file or a file with another format). Below I try to load a Comma Separated Value (CSV) file.

Set objWb = objXl.WorkBooks.Open(file)

The variable file must contain the whole path and the name of the import file. Because Excel loads Workbooks, we must access also the Open method provided by the WorkBooks object. According to the Excel object model, this object is a sub-object of the Application object. Therefore we can use objXL.WorkBooks to address the WorkBooks object.

After loading the file, we shall set the workbook as the current workbook. This may be done using the following command:

Set objWb = objXL.ActiveWorkBook.WorkSheets("Test")
objWb.Activate ' not absolutely necessary (for CSV)

I assumed that the worksheet was named "Test" (which is the case, if we import a file Test.csv). Otherwise you must insert the name of your worksheet. The Activate method causes that the worksheet will be visible in the foreground. You can use the Cells object to access cells within a workbook. The following lines show how to read a few cells:

WScript.Echo CStr(objWb.Cells(1, 1).Value) & vbTab & _
             CStr(objWb.Cells(1, 2).Value)

To print out the content of the current worksheet, you must use the PrintOut method using the following command:

objXl.ActiveSheet.PrintOut

The next line quits Excel:

objXl.Quit

That's all. Further details may be found in the following listing. This listing contains also a function to retrieve the path of the script file. This path is used to load the Excel file (which must be located in the same folder as the VBScript file).

***********************************************
' File:     ExcelCSV.vbs (WSH sample in VBScript) 
' Author:   (c) Günter Born
'
' Launch Excel, load a worksheet (CSV file),
' select the current worksheet, read some
' values back into the script, print the CSV content
' and terminate.
'
' A sample derived from my books:
' Inside Windows Scripting Host, MS Press Germany
'
' Check out Born's Windows Scripting Host Bazaar at:
' http://www.borncity.de
'
' In no way shall the author be liable for any
' losses or damages resulting from the use of this
' program. Use AS-IS at your own risk.
'
' The code is the property of the author. You may
' use the code and modify it, as far as this header
' remains intact. Further updates and other samples
' may be found on my site mentioned above.
'************************************************
Option Explicit
Const vbNormal = 1         ' window style

DIM objXL, objWb, objR     ' Excel object variables
DIM Title, Text, tmp, i, j, file, name

Title = "WSH sample - by G. Born"

' here you may set the name of the file to be imported 
' Important: the file must be located in the script's folder
' file = "Test.xls"      ' a XLS file
file = "Test.csv"        ' a CSV file

' here you may change the name of the worksheet, you like
' to access. I have inserted the name "Test", because the
' file above was a CSV-File, in this case Excel uses the
' file name also to name the worksheet, where the CSV values 
' are imported. 
name = "Test"        ' set the Worksheet-name used for import

' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

' set the Excel window properties (not absolutely necessary)
objXL.WindowState = vbNormal ' Normal
objXL.Height = 300           ' height
objXL.Width = 400            ' width
objXL.Left = 40              ' X-Position
objXL.Top = 20               ' Y-Position
objXL.Visible = true         ' show window

' Load the Excel file from the script's folder
Set objWb = objXl.WorkBooks.Open(GetPath+file)

' Get the loaded worksheet object
Set objWb = objXL.ActiveWorkBook.WorkSheets("Test")
objWb.Activate               ' not absolutely necessary (for CSV)

WScript.Echo "We have loaded the worksheet"

' demonstrate how to read the column header values
Text = "Worksheet " & objWb.name & vbCRLF
Text = Text & "Column titles" & vbCRLF
Text = Text & CStr(objWb.Cells(1, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(1, 2).Value) & vbCRLF

' show some cell values (using the "hard coded method")
Text = Text & CStr(objWb.Cells(2, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(2, 2).Value) & vbCRLF
Text = Text & CStr(objWb.Cells(3, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(3, 2).Value) & vbCRLF
Text = Text & CStr(objWb.Cells(4, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(4, 2).Value) & vbCRLF

' Show results
MsgBox Text, vbOkOnly+ vbInformation, Title

objXl.ActiveSheet.PrintOut    ' print Worksheet

WScript.Echo "We are printing, close after printing"

objXl.Quit                    ' Quit Excel 
Set objXL = Nothing

'##########################
Function GetPath
' Retrieve the script path
 DIM path
 path = WScript.ScriptFullName  ' Script name
 GetPath = Left(path, InstrRev(path, "\"))
End Function
'*** End

A Note about the CSV file: Excel can import Text files, which are stored in the comma separated value (CSV) format. Such a file contains the data in lines, whilst the fields are separated by commas, tabs, semicolons and so on. In normal cases an import filter shall detect the separator for the fields within each record (line). Unfortunately there is a problem with Excel. Depending on the local Windows settings the separator may be either a semikolon or a comma. In German Excel the CSV file must contain a semicolon as a separator, to import the fields within the records into different columns of the worksheet. This format is used also from Excel, if you export a file into the CSV format. Below is a simple CSV sample file containing two fields and a few records:

Name;ID
Born;123
Miller;723
Myers;123
McFyer;1234
Thommy;7

The first line contains the column titles, which will be overtaken into the worksheet. If you use a comma as a separator instead, all entries are going into the first column of the Excel worksheet. Unfortunately we can't specify the separator as a parameters of the Open method, if we call this method from the WSH. This is because VBScript doesn't support named parameters for method calls. Within Excel you can use the import wizard to import a text file (and specify the separator character). This may be recorded also into a variable. But I haven't found a way to use these command from a WSH script.

To overcome this limit, I have developed another sample which uses a tricky way to import any CSV file into Excel from WSH scripts (the script is shown in the next sample). But there is a restriction, this sample will run only in Microsoft Excel 2000 (not in Excel 97).

Additional information about the WSH may be found in my German WSH book Inside Windows Scripting Host, published by Microsoft Press Germany.

Back