Excel Files

Business Users like to have data in a spreadsheet format. Since Microsoft Office is installed world-wide, it will be a common request for data in a XLS format.

Let’s make believe that we have the business problem to write information about the first five presidents to an XLS format.

The Cliché, there are many ways to skin a cat, does apply to technical solutions. I have abstracted the details behind reading and writing files by encapsulating the logic into two classes for your use.  These classes can be implemented in at least two ways.

The first solution is to create a class using object oriented principles. The attributes of the class are very specific to the problem at hand, describing US presidents. Therefore, last name and first name are attributes.

The second solution is to create a class using generic / abstract principles. The attributes of the class are not specific to the problem at hand. Therefore, spreadsheet name and definition are attributes.

While I like the first solution since it describes the problem at hand in detail, I implemented the second class since it can be applied to a wider set of business problems.

The write xls file class has the following properties and methods.

  • OpenDataFile – Open a given file in write or append mode.
  • CreateWorkSheet – Create a named worksheet with column attributes.
  • PushData – Write a line of data to the given file.
  • CloseDataFile – Close the given file.
  • RemoveFile – Remove a given file.
  • OneRec – Pipe delimited data stream to write as columns.
  • SheetName – Name given to the worksheet
  • SheetDef – Pipe delimited worksheet definition on SQL like code

The read text file class has the following properties and methods.

  • OpenDataFile – Open a given file in read mode.
  • PullData – Return a line of data if not EOF.
  • CloseDataFile – Close the given file.
  • FileExists – Does a given file exist?
  • EOF – a property that reflects if we are at the end of file.
  • RECS – the total number of records (0 = nothing read, -1 = EOF, or current count).

Both classes use ActiveX Data Objects to manipulate the XLS files.  If you need finer control over formatting, please look into the Microsoft Excel.Application object.

Last but not least, you should get very familiar with creating Excel worksheets and summarizing data with pivot tables.  These skills will make you an invaluable resource to the Business Line Community.

The table below has starter code that you can use in your next Visual Basic (VB) Script.

mod-read-xls-file.vbs Read Xls File Class
mod-write-xls-file.vbs Write Xls File Class
tst-read-xls-file.vbs Read Sample Program
tst-write-xls-file.vbs Write Sample Program
read.cmd Read Batch File
write.cmd Write Batch File
presidents.xls XLS Sample File

 

 

 

 

 

 

This is a screen shot of the read sample VB Script excuting in a MS DOS command shell.

 

 

Related posts

One Thought to “Excel Files”

  1. My partner and i still can’t quite assume that I could always be one of those studying the important guidelines found on your blog. My family and I are sincerely thankful on your generosity and for providing me the potential to pursue my own chosen profession path. Thanks for the important information I managed to get from your website.

Leave a Comment