VBScript - FSO (File System Object)

Introduction to FSO (File System Object)

I recommend you visit my new section on FSO

FSO is a pure VBScript object. You would use FSO to open an Excel spreadsheet and read the user properties. Other methods in the script will then assign these properties to the user objects as they are created.

Creating the FSO

FSO creates an object. That top level object is like a super file that can create, open and manage a variety of file types, for example, text files or spreadsheet applications. So our first task is to create a 'master' object, in the example objFile. We will then use this 'master' object to create instances of files, for example text files or Excel spreadsheets.

Set objFSO = CreateObject("Scripting.FileSystemObject")

Creating a Shell

Set objShell = CreateObject("Wscript.Shell")

Note 1: This is a job for WSH (Windows Script Host) rather than a VBScript object

See my new section on FSO

Creating a file and writing in some text

First, we tell objFile that we want a TextFile, (as opposed to an Excel spreadsheet). Once we have our TextFile we can apply the WriteLine method and populate the file with a text string. In truth this is a little laborious, and the main use of FSO is to read data that is already in the text file, (or spreadsheet). Finally we clear the memory buffers and save the file with .Close method.

' VBScript to demonstrate creating a file
' Guy Thomas January 2004

Dim objFile, strGuyFile, strFilePath
strFilePath = "e:\files\strGuyFile.txt"
Set objFile = CreateObject("Scripting.FileSystemObject")
Set strGuyFile = objFile.CreateTextFile(strFilePath, True)
strGuyFile.WriteLine("This was made using VBScript.")
strGuyFile.Close

Open an Excel Spreadsheet

The scenario is that you have hundreds of usernames in a spreadsheet. You wish to create a script that can read those values. Your long term goal it to write the usernames into Active Directory.

If you wish to try the script below, either write your own spreadsheet, or download one I made earlier from here: GuyUsers.xls.

Important, to get the script to work, save the file to C:\Scripts\GuyUsers.xls, or alter the path statement in line 7.

'VBScript
'To open a Spreadsheet. Read a value from the 1st column, 3rd row
Dim objExcel, strPathExcel,strCN
Dim objFile, strGuyFile, intRow

intRow = 3
Set objExcel = CreateObject("Excel.Application")
strPathExcel = "e:\files\GuyUsers.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
strCN = Trim(objSheet.Cells(intRow, 1).Value)
WScript.Echo strCN

Note 1: On Line 6, CreateObject method produces an Excel application not a TextFile.

Note 2: Because we are dealing with a spreadsheet, we need to make sure we are reading the correct Worksheet.

Note 3: On Line 10 .Cells(intRow, 1) means the third row because intRow = 3 and 1 refers to the column offset.

Guy Recommends: WMI Monitor and It's Free!

Windows Management Instrumentation (WMI) is one of the hidden treasures of Microsoft operating systems. Fortunately, SolarWinds have created a Free WMI Monitor so that you can discover these gems of performance information, and thus improve your scripts. Take the guess work out of which WMI counters to use when scripting the operating system, Active Directory or Exchange Server.

Download your free copy of WMI Monitor

Guy Recommends: WMI Monitor and It's Free!

Windows Management Instrumentation (WMI) is one of the hidden treasures of Microsoft operating systems.

Fortunately, SolarWinds have created the Free WMI Monitor so that you can actually see and understand these gems of performance information. Take the guess work out of which WMI counters to use for applications like Microsoft Active Directory, SQL or Exchange Server.

Download your free copy of WMI Monitor

Please report a broken link, or an error to:

VBScript - FSO (File System Object)

Introduction to FSO (File System Object)

Creating the FSO

Creating a file and writing in some text

Open an Excel Spreadsheet

Guy Recommends: WMI Monitor and It's Free!

See Also

Guy Recommends: WMI Monitor and It's Free!