Gogiro.com

This article will show you how to programmatically create an Excel spreadsheet. I wrote this in VBScript, since it is readily available and easy to translate to other languages. I wrote the code to be modular, and, with the use of a few global variables (yeah, I know), it can be easily customized to meet your needs.

Here is the complete program. Just copy everything into a text editor (or a VBScript Editor if you have one) and save it a “MyDir.vbs.”

Comments

First let’s get the comment section out of the way. This is my standard opening comment section. In it I try to cover my coding style and any assumptions. As you can see, this script requires you have at least WScript 5.6 and Microsoft Excel 2003 (this should not be an issue unless you are still running Windows XP or older).

Initialization

In the INITIALIZATION section I am defining the Constants and Global variables. Most of these variables are the ones you change to customize this script. For example, DEFAULT_PATH is the directory that will be listed and the results placed in the spreadsheet.

Now let’s take a look at the FUNCTIONS and SUBROUTINES.

Functions

The function funSaveFiles handles the saving of files. Pass the filename as a string. The Files System routines used to check for a folder and create one are called if needed. We are able to call the actual Excel Save (GetSaveAsFilename) routine to save the file we created. This is nice, since all we need to do is define the file name (strFullName), display filter (strFilter), and the window title (strTitle) – Excel will take care of the rest.



Subroutines

The subroutine subCheckScriptHost is a standard Subroutine I use in most of my code. It tests to see if WScript is running, if not it will start WScript and re-run the program for you. The main reason I choose to use WScript (as opposed to CScript) is that WScript uses MessageBox for standard output and CScript writes a line to the console window.

The subroutine subAddLineXLS   will simply add a new row of data to the spreadsheet. Just pass an array containing the data, one array object for each column in the row to be added.

The subroutine subBuildXLS will create the actual spreadsheet. I set Visible to False so the actual spreadsheet is not displayed until it is completed.  Some interesting things that are done in this subroutine are:

Add the headers by calling subAddLineXLS()

Select a range and make it an object: SetobjExcelRange = GobjExcel.Range()

Format the Object Range by setting the values objExcelRange.Interior…

The subroutine subCloseApp closes out the script and is called for normal and abnormal (errors) termination. The first thing I do is turn off error handling so if my exit routine returns an error, the script still terminates. Again, this is one of my standard subroutines.

I always like to put a footer on any report I create. The subroutine subFooter is straightforward and will create a custom footer based on a locally defined array. To customize, change the array size to the number of lines – 1. Finally, add an array object (starting at 0) for each line you want to add.

Coding Overview

A quick high level coding overview:

Check to see if we are running under WScript – subCheckScriptHost

Create New Excel File – subBuildXLS

Build and add the Header – subAddlineXLS

Add a line for each row of data – subAddLinesXLS

Create the footer – subFooter

Save the file and clean things up – subCloseApp

One more point. As you can see, I created my own Error Routine. It is very basic, as I just turn off error handling and then call my exit subroutine, if there is any error. I pass a custom message, the error number, the error description, and the error source to SubCloseApp.

In the program, here are the lines that accomplish the following (as listed above):

Check to see if we are running under WScript

If(InStr(LCase(WScript.FullName),”wscript”)=0)ThenCallsubCheckScriptHost()

Create New Excel File

Call subBuildXLS

Build and add the Header

Call subAddLineXLS(GaryHeaders)

Add a line for each row of data

For intCounter = 1 To intRows – 1

GobjExcel.Cells(GintRow, _

intCounter).Value = Trim(aryLineDetail(intCounter))

Next

Create the footer

CallsubFooter()

Save the file and clean things up

If funSaveFiles(SAVE_FILE) Then

MsgBox “Your inventory run is complete!”, vbInformation + vbOKOnly, REPORT_TITLE

Your file output will look like this:



I tried to make the program as modular as possible so you can just drop sections of code into your programs or just modify things to suit your needs.

Disclaimer

—————————

—————————

Provided “as is,” with no warranty whatsoever. Copyright © PlumChoice 2014

Questions or comments? Contact Everon at 888-244-1748. Or email Frank Lindsey at flindsey@everonit.com.



The post Tech Tips for Techs: Programmatically Creating an Excel Spreadsheet appeared first on Gogiro Internet Group.