USING WINBATCH WITH MICROSOFT EXCEL

This is a series of scripts to help Winbatch users work with MS Excel via OLE/COM.

These scripts are written using: Winbatch 2010C and MS Excel 2007.

We'll learn how to open Excel via OLE/COM, create a blank workbook and insert sample data and save it, as an introduction.

You will need this sample data file before you begin: NorthWindEmployees.txt (Right-click and select 'Save As' to save to disk.)

EXCEL SCRIPT 1.0

This script takes a comma separated values (CSV) file and uses it to place data into MS Excel. This script isn't the most efficient means to do so (yes, you can import a CSV file in one fell swoop) but it's been designed to show you how to place data into an Excel cell on a worksheet, and whats involved in doing so.

First, we need to get Excel going:

   objExcel = ObjectCreate("Excel.Application")
   objExcel.Visible = @TRUE
   objExcel.Workbooks.Add

This above code starts an instance of Excel, makes it visible, then adds a blank workbook.

Next, we need to get the data, read it into a variable, so we manipulate it using the list functions:

   inputfile = DirScript():"NorthwindEmployees.txt"
   data = FileGet(inputfile)
   data = StrReplace(data, @CRLF, "|")

The last line above, removes the CRLFs at the end of each line of data, and replaces them with the pipe symbol.

Now we create a reference to the active worksheet. In MS Excel the .ActiveSheet is the one on top, that's available to the user to input data.

   objActiveSheet = objExcel.Workbooks(1).ActiveSheet

Then we need to loop through the data, and input the data across in columns. You'll notice the data in the text file has the Column Names in the first row. This is fine, as it'll let us put labels over each column so it's easy to understand. The script simply goes to the cell at the specified row, inserts the data into each column, then drops down to the next row and repeats.

   For row = 1 To ItemCount(data, "|")
      line = ItemExtract(row, data, "|")
      For col = 1 To ItemCount(line, ",")
         objActiveSheet.Cells(row, col).value = ItemExtractCSV(col, line, 0, ",")
      Next  ; column
   Next     ; row

You'll notice the use of Winbatch's ItemExtractCSV() function. This is very helpful, especially if your data is "quoted" something that many programs use when they export string data. If you export a field whose data contains a comma, it can throw off the number of fields that the data has. This way if you have data like: "Seattle, WA" the ItemExtractCSV function will understand that this is all part of a single column, not two.

Next, we rename the sheet to match the root of the input file, without its extension.

   objActiveSheet.name = FileRoot(inputfile)

Since we only opened a few OLE objects, we close them before ending the script. To close a Ole/Com Object handle, simply set the variable equal to zero.

   objActiveSheet = 0
   objExcel = 0
   Exit

Pretty easy, but if you were watching, very slow. Imagine if you had to import thousands or hundreds of thousands of records, it wouldn't be practical to do it this way. Don't worry we'll show you how to import large amounts of data in the following scripts.

EXCEL SCRIPT 1.1

Part of the beauty of using Excel, is being able to format the data in various ways. This script will show you how to do a few simple formats. The first part of this script is essentially the same as the first part of the last script.

In order to format a region of cells in Excel, we must use the .Range object:

   objActiveSheet.Range("A1:P1").EntireColumn.Autofit

This simply tells Excel, that on the ActiveSheet, from cells A1 to P1, make each column auto-fit, so that the cell will accomodate the largest value within the range and display it so it can be clearly seen.

Now, we want to color the header row so the user can clearly see the labels for each column.

   xlSolid = 1
   objActiveSheet.Range("A1:P1").Interior.ColorIndex = 34
   objActiveSheet.Range("A1:P1").Interior.Pattern = xlSolid

First we declare the variable for a "solid" pattern (this means Excel will paint the entire cell in one color) and we also tell it which color (34) to use. These values are constants defined by Excel. The WIL Type Viewer included with WinBatch can help you determine these constant values. The WIL Type Viewer is located at ...\WinBatch\System\WIL Type Viewer.exe

Now notice the line:

   objActiveSheet.Range("A1").Select

All this does is place the cursor in the first cell on the sheet. If you go back and look at the first script, or at the first part of this script, you'll notice that to insert data into a cell, it doesn't need to be selected first. As long as you specify the exact placement of the data, you can place data inside any cell on any sheet.

Once again we rename the sheet and close up and exit.

You might be wondering how we get some of these constant values. There are a number of ways.

WIL Type Viewer
WinBatch includes an Object Type Viewer located at ...\WinBatch\System\WIL Type Viewer.exe.
ObjectConstToArray
Is a WIl function which creates an array of Constants object names and values.
Object Browser
Many of the Office applications include an Object Browser.

Your next question is likely: how do you know to use these Methods? Excel has a great feature: the Macro Recorder. You can actually record the process then by looking at the resulting code you can determine which Methods, Properties and Constants to use in your WIL script.

EXCEL SCRIPT 1.2

This next script shows you how to open Excel, add and delete worksheets. If you've worked with Excel, you've probably seen as few as one worksheet in a workbook, to others that have dozens. If you submit an Excel workbook to your boss or to a vendor, you may not want to leave in the "extra blank sheets" since it looks slightly tacky. If you have lots of data to import, you can import a CSV file but while this is fast, it also only imports and single sheet. What if you need to show lots of data, broken up into logical groups (multiple worksheets)?

After opening Excel, we look at the workbook and determine how many worksheets it contains. If you open Excel by hand, go to the TOOLS menu, then the OPTIONS and click on the GENERAL tab. Look for SHEETS IN NEW WORKBOOK and you can set Excel to your needs. Mine defaults to 3, but this script will go through and delete all but one.

This shows how many worksheets are in the current workbook.

   wsCount = objExcel.Workbooks(1).Worksheets.count
   Message("Debug", StrCat("There are ", wsCount, " worksheets in this Workbook."))

Next we loop through the collection of worksheets, and delete them.

   For x = 1 To wsCount-1
      objExcel.Workbooks(1).Worksheets(x).delete
   Next

Note: in Excel, a workbook must contain at least 1 worksheet so you can't delete them all.

Then we setup some variable names for the new worksheets. For the moment we'll pretend we're part of a company that has several regions and we're preparing data for a report.

We want each sheet to have 10 columns and 50 rows.

   DepartmentNames = "West|Northwest|Central|Southwest|East"
   ColumnCount = 10
   RowCount = 50
   xlSolid = 1

Next we add a worksheet for each department. First, since we already have one worksheet, we don't add one for the first region. When worksheets are added, they are placed on top and they become the first item in the collection, so they are referred to in the script as Excel.Workbooks(1).Worksheets(1).

   For ws = 1 To ItemCount(DepartmentNames, "|")
      If ws <> 1 Then objExcel.Workbooks(1).Worksheets.Add

Next we name the worksheet

      objExcel.Workbooks(1).Worksheets(1).name = ItemExtract(ws, DepartmentNames, "|")

Then we want to start at row one, place the column headers on each sheet, then the data.

      row = 1
      For col = 1 To ColumnCount
         objExcel.Workbooks(1).Worksheets(1).cells(row, col).value = StrCat("Column ", col)
      Next

We start at row two for the data and go until we've got 50 rows on the sheet. We'll input a random number into each cell.

      For row = 2 To RowCount
         For col = 1 To ColumnCount
            objExcel.Workbooks(1).Worksheets(1).cells(row, col).value = Random(1000)+1
         Next  ; column
      Next     ; row

Then, once the data is filled in, we color the header row.

      objExcel.Workbooks(1).Worksheets(1).Range("A1:J1").Interior.ColorIndex = 34
      objExcel.Workbooks(1).Worksheets(1).Range("A1:J1").Interior.Pattern = xlSolid
   Next

This time, we ask the user to supply a filename, so we can show you how to save your work using OLE.

   xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")
   If FileExist(xlsFilename) Then FileDelete(xlsFilename)
   objExcel.Workbooks(1).SaveAs(xlsFilename)

My habit is to delete any previous versions of that file if they exist then save the spreadsheet.

If you sat there and watched the script run, you noticed that it was very, very slow. Be patient we'll show you how to quickly add data soon.

EXCEL SCRIPT 1.3

Since the last script showed you how to save a spreadsheet via OLE, it's handy to know how to open an existing worksheet, make changes to it and save it.

The first line simply prompts you for the path/name of the worksheet

   xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")
   objExcel = ObjectCreate("Excel.Application")
   objExcel.visible = @TRUE
   objExcel.Workbooks.Open(xlsFilename)

and the last one above, shows you how to open it using the Open method.

Since you already know how to insert data, cell by cell, we won't do it here, but instead show you how to insert some blank rows into a particular worksheet, without having to loop through the entire collection to find the right one.

   xlDown = -4121
   objExcel.Workbooks(1).Worksheets("Northwest").Rows("10:15").Insert(::SHIFT=xlDown)

Not having to loop through the collection of worksheets is much faster, especially if you know the layout of the workbook. First we set a constant so that when Excel inserts the new rows, will shift the ones below it down. We also grab the "Northwest" worksheet by name. You noticed in previous scripts a workbook was addressed by a number like: Worksheets(1). A workBOOK is simply a collection of workSHEETS, much like an array. You can either get them by their position (number) or their name. When you open a new workbook the names are "Sheet1", "Sheet2" etc, etc. In our example we know the name of the worksheet we want, so we reference it that way.

The same principle applies to the rows. A workSHEET is simply a collection of ROWS, so using this method, we tell Excel, "Using the Northwest worksheet, insert 6 rows starting at row 10 and going down to row 15, and shift the existing rows down." This may be a little awkward at first, but the more you use it, the easier it will be to understand the logic behind it.

Since we told Excel to open a specific workbook, we don't need to specify the name when we save it. So the command:

   objExcel.Workbooks(1).Save()

Will simply save our work under the name we opened it with. Use .SaveAs to change the name. We then quit Excel and close up.

EXCEL SCRIPT 1.4

I'm not going to go into detail on this script, that's been done in the MS Access script section. Make sure you read and run those scripts before proceeding here. Once you've created the database, the table and sample data, then this script can be run.

It simply creates a new table, and populates it with data, to show you a faster way to import lots of data into a single Excel workbook with multiple sheets.

EXCEL SCRIPT 1.5

One of the most common tasks in Excel is grabbing data from another source and populating worksheets. As stated before, you can import CSV files (and other types) but they generally only fill a single worksheet, and if you repeat the process, you have several instances of Excel open each with a single worksheet. Then you have to move the worksheets into a single workbook, close all the extra instances of Excel and then format the data.

If your data is coming from MS Access, then you're in for a treat. What we'll do is create an OLE instance of Access, open a specific database, and query a table for data. We'll then insert each query into its own worksheet. We won't format the data, as we've already show you how, so you can modify your own script as an exercise.

First we need to start Access:

   ;   open access and the NWIND database...
   objAccess = ObjectCreate("Access.Application")
   dbname = "C:\data\access\Winbatch Test.mdb"
   objAccess.OpenCurrentDatabase(dbname)
   objAccess.Visible = @TRUE
   objDB = objAccess.currentdb

Next we setup the department list variable, then start Excel and open a blank workbook:

   DepartmentNames = "West|Northwest|Central|Southwest|East"
   ;   open excel, add a blank workbook...
   objExcel = ObjectCreate("Excel.Application")
   objExcel.visible = @TRUE
   objExcel.Workbooks.Add

Then we remove all but 1 worksheet:

   For x = 1 To objExcel.Workbooks(1).Worksheets.count
      objExcel.Workbooks(1).Worksheets(x).delete
   Next

Next we loop through the department names, add a new worksheet for all but the first one.

   For ws = 1 To ItemCount(DepartmentNames, "|")
      ;   ignore the first worksheet...
      If ws <> 1 Then objExcel.Workbooks(1).Worksheets.Add

Rename the excel worksheet to the department, then supply a query for each sheet and open a recordset.

      thisDept = ItemExtract(ws, DepartmentNames, "|")
      objExcel.Workbooks(1).Worksheets(1).name = thisDept
      sqlstr = StrCat("select * from Numbers")
      objRS = objDB.OpenRecordSet(sqlstr)

We then start at row one, input the column names. You'll notice that we use "col-1" to reference the column in the recordset. This is because MS Access is slightly different than Excel, and uses a zero-based subscript for its collections. In Excel the first column starts with a 1, in Access it starts with zero. This is easily compensated for.

      row = 1
      For col = 1 To objRS.fields.count
         ;   set the value of the cell to the field name...
         objExcel.Workbooks(1).Worksheets(1).cells(row, col).value = objRS.fields.item(col-1).name
      Next

Now, that we have the data, we want to start at column A, row 2 so we simply tell Excel to place the data from the recordset into the worksheet at this point, all at once.

      objExcel.Workbooks(1).Worksheets(1).Range("A2").CopyFromRecordset(objRS)
      ;   close up the recordset, range and active sheet references...
      objRS = 0
   Next      ; next customer

And the repeat the process until we're done with all the departments. We then close up Access:

   objDB = 0
   objAccess = 0

Then prompt for a filename to save our Excel spreadsheet:

   ;   prompt the user for the filename...
   xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")
   ;   delete the file if it already exists...
   If FileExist(xlsFilename) Then FileDelete(xlsFilename)
   ;   save the workbook...
   objExcel.Workbooks(1).SaveAs(xlsFilename)
   ;   quit excel and close the reference to the application...
   objExcel.quit
   objExcel = 0
   Return

Then quit Excel and close up. As compared to the previous scripts that was very fast, and using double the amount of data.

Make sure you read the .CopyFromRecordset() method in the Excel VBA help file. You'll see that it also works with an ADO recordset.

EXCEL SCRIPT 1.6

The more you use Excel, the more likely you are to record your own macros so you can repeat tedious tasks. If you don't know how to do this we'll give you some preliminary instructions, but make sure you consult the Excel help file on how to do this.

One of the things about macros is that if you don't know how to code the solution, recording the macro will do it for you. Once you have a macro you can run it as often as you need to. The problem can be that, though you have an Excel macro recorded how do you get Winbatch to run it? Another option might be to edit the macro and translate it into Winbatch commands, but what if you're new to Winbatch and don't know how to do this? Or you may get a macro from a friend, that's perfect for what you need, but now you need Winbatch to run it.

First open up the Excel spreadsheet we just saved, called "MyExcelFile.xls" and go up to the TOOLS menu and click on RECORD A NEW MACRO. As soon as the STOP button shows up, end the macro. Now go back to the TOOLS menu and hit MACRO, then MACROS, hilight the macro you just recorded and hit EDIT. The VBA editor will open and you'll see the macro you just recorded. Simply hilight the whole thing and hit delete, then cut & paste the following code into its place:

	Sub WinbatchMacroTest(HelloStr)
	'
	' WinbatchMacroTest Macro
	' Macro recorded 8/8/2003 by Owner
	'
	'
	    Range("A1:J1").Select
	    With Selection.Interior
	        .ColorIndex = 6
	        .Pattern = xlSolid
	    End With
	    Range("A1").Select
	
	     MsgBox HelloStr
	
	End Sub

Hit CTL-S to save it, then hit ALT-Q to quit the VBA editor. Close and save "MyExcelFile.xls" to your hard disk.

Now we can run our Winbatch script. As before we start Excel, and open a specific workbook. This time we're going to supply a parameter to the macro, just as if you were using a UDF in Winbatch.

 macroParameter = "Hello from Winbatch!"

We now tell Excel to run the named macro (or function) and supply the parameter we specified. The macro should execute on the current active sheet and then display a message box from Excel with our parameter. This is handy if you need to supply a value to the macro so that it can use it for input.

   ;   run the macro from the Run method of the application object...
   objExcel.Run("WinbatchMacroTest", macroParameter)
   ;
   ;   setup the reference to the current workbook and save it...
   objExcel.Workbooks(1).Save()

Once we're done, click OK on the message box and Excel will save the formatted data and quit.

This is very handy if you already know how to code in Visual Basic, but are still learning Winbatch. This way you can utilize your existing macro code to carry out commands, while you learn to code in Winbatch.

Note: blank workbooks do not contain any macros! So make sure you only use this script on workbooks with existing macros.

#DefineFunction CreateAndAppendField(tdf, FieldName, FieldType, FieldLength)
   fld = tdf.CreateField(FieldName, FieldType, FieldLength)
   If FieldType == 10 || FieldType == 12 Then fld.AllowZeroLength = ObjectType("BOOL", -1)
   tdf.Fields.Append(fld)
   fld = 0
   Return
#EndFunction

GoSub OpenExcelInsertData
;gosub OpenExcelInsertFormatData
;goSub OpenExcelRemoveAndReplaceWorksheets
;gosub OpenExcelFileInsertRow
;gosub CreateAndInsertNumbers
;gosub AccessToExcelCopyRS
;gosub RunExcelMacro

Exit

;   Excel Script 1.0
:OpenExcelInsertData
objExcel = ObjectCreate("Excel.Application")
objExcel.visible = @TRUE
objExcel.Workbooks.Add
;
inputfile = "c:\NorthwindEmployees.txt"
data = FileGet(inputfile)
data = StrReplace(data, @CRLF, "|")
;
objActiveSheet = objExcel.Workbooks(1).ActiveSheet
;
For row = 1 To ItemCount(data, "|")
   line = ItemExtract(row, data, "|")
   For col = 1 To ItemCount(line, ",")
      objActiveSheet.cells(row, col).value = ItemExtractCSV(col, line, 0, ",")
   Next  ; column
Next     ; row
;
objActiveSheet.name = FileRoot(inputfile)
;
objActiveSheet = 0
objExcel = 0
Return

;   Excel Script 1.1
:OpenExcelInsertFormatData
objExcel = ObjectCreate("Excel.Application")
objExcel.visible = @TRUE
objExcel.Workbooks.Add
;
inputfile = "c:\NorthwindEmployees.txt"
data = FileGet(inputfile)
data = StrReplace(data, @CRLF, "|")
;
objActiveSheet = objExcel.Workbooks(1).ActiveSheet
;
For row = 1 To ItemCount(data, "|")
   line = ItemExtract(row, data, "|")
   For col = 1 To ItemCount(line, ",")
      objActiveSheet.cells(row, col).value = ItemExtractCSV(col, line, 0, ",")
   Next  ; column
Next     ; row
;
objActiveSheet.Range("A1:P1").EntireColumn.Autofit
;
xlSolid = 1
;
objActiveSheet.Range("A1:P1").Interior.ColorIndex = 34
objActiveSheet.Range("A1:P1").Interior.Pattern = xlSolid
;
objActiveSheet.Range("A1").Select
;
objActiveSheet.name = FileRoot(inputfile)
;
objActiveSheet = 0
objExcel = 0
Return

;   Excel Script 1.2
:OpenExcelRemoveAndReplaceWorksheets
objExcel = ObjectCreate("Excel.Application")
objExcel.visible = @TRUE
objExcel.Workbooks.Add
;
wsCount = objExcel.Workbooks(1).Worksheets.count
Message("Debug", StrCat("There are ", wsCount, " worksheets in this Workbook."))
;
For x = 1 To wsCount-1
   objExcel.Workbooks(1).Worksheets(x).delete
Next
;
DepartmentNames = "West|Northwest|Central|Southwest|East"
ColumnCount = 10
RowCount = 50
xlSolid = 1
;
For ws = 1 To ItemCount(DepartmentNames, "|")
   If ws <> 1 Then objExcel.Workbooks(1).Worksheets.Add
   objExcel.Workbooks(1).Worksheets(1).name = ItemExtract(ws, DepartmentNames, "|")
   row = 1
   For col = 1 To ColumnCount
      objExcel.Workbooks(1).Worksheets(1).cells(row, col).value = StrCat("Column ", col)
   Next
   For row = 2 To RowCount
      For col = 1 To ColumnCount
         objExcel.Workbooks(1).Worksheets(1).cells(row, col).value = Random(1000)+1
      Next  ; column
   Next     ; row
   objExcel.Workbooks(1).Worksheets(1).Range("A1:J1").Interior.ColorIndex = 34
   objExcel.Workbooks(1).Worksheets(1).Range("A1:J1").Interior.Pattern = xlSolid
Next
;
xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")
If FileExist(xlsFilename) Then FileDelete(xlsFilename)
objExcel.Workbooks(1).SaveAs(xlsFilename)
;
objExcel.quit
objExcel = 0
Return

;   Excel Script 1.3
:OpenExcelFileInsertRow

xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")

objExcel = ObjectCreate("Excel.Application")
objExcel.visible = @TRUE
objExcel.Workbooks.Open(xlsFilename)
;
xlDown = -4121
;
objExcel.Workbooks(1).Worksheets("Northwest").Rows("10:15").Insert(::SHIFT=xlDown)
objExcel.Workbooks(1).Save()
;
objExcel.quit
objExcel = 0
Return

;   Excel Script 1.4
:CreateAndInsertNumbers
   dbname = "C:\data\access\Winbatch Test.mdb"
;
   objAccess = ObjectCreate("Access.Application")
   objAccess.OpenCurrentDatabase(dbname)
   objAccess.Visible = @TRUE
   objDB = objAccess.CurrentDb
   objtdf   = objDB.CreateTableDef("Numbers")
;
   ;   set the constants for the field types...
   dbLong = 4
;
   BoxOpen("MS Access","")
   BoxText("Creating Numbers Table...")
   For x = 1 To 10
      CreateAndAppendField(objtdf, StrCat("Column", x), dbLong, 4)
   Next
;
   objDB.TableDefs.Append(objtdf)
;
   tdf = 0
;
   objRS = objDB.OpenRecordset("Numbers")
   For x = 1 To 100
      objRS.addnew
      For y = 1 To 10
         objRS.fields(StrCat("Column", y)).value = Random(999)+1
         BoxText("Creating record # %x% of 100 and column # %y% of 10...")
      Next
      objRS.update
   Next
   objRS.close
   BoxShut()
;
   objDB = 0
   Access.quit
;
   objtdf = 0
   objAccess = 0
;
Return

;   Excel Script 1.5
:AccessToExcelCopyRS
;   open access and the NWIND database...
objAccess = ObjectCreate("Access.Application")
dbname = "C:\data\access\Winbatch Test.mdb"
objAccess.OpenCurrentDatabase(dbname)
objAccess.Visible = @TRUE
objDB = objAccess.currentdb
;
DepartmentNames = "West|Northwest|Central|Southwest|East"
;
;   open excel, add a blank workbook...
objExcel = ObjectCreate("Excel.Application")
objExcel.Visible = @TRUE
objExcel.Workbooks.Add
;
;   loop thru the worksheets, reference each ws individually,
;   delete it and close the reference...
For x = 1 To objExcel.Workbooks(1).Worksheets.count
   objExcel.Workbooks(1).Worksheets(x).delete
Next
;
;   loop thru the unique id's...
;   add a new worksheet for each one...
;   build a SQL string based on each unique id...
;   open the recordset...
For ws = 1 To ItemCount(DepartmentNames, "|")
;   ignore the first worksheet...
   If ws <> 1 Then objExcel.Workbooks(1).Worksheets.Add
;   extract the department name for this worksheet...
   thisDept = ItemExtract(ws, DepartmentNames, "|")
;   set the worksheet's name to the department name...
   objExcel.Workbooks(1).Worksheets(1).name = thisDept
;   build the SQL for the query...
   sqlstr = StrCat("select * from Numbers")
;   open the recordset...
   rs = objDB.OpenRecordSet(sqlstr)
;   start at row 1..
   row = 1
;   loop thru the collection of fields and insert the name
;   of each field in the first cell of each column...
   For col = 1 To objRS.fields.count
;   set the value of the cell to the field name...
      objExcel.Workbooks(1).Worksheets(1).cells(row, col).value = objRS.fields.item(col-1).name
   Next
;   set the range from where to copy the Access data...
   objExcel.Workbooks(1).Worksheets(1).Range("A2").CopyFromRecordset(objRS)
;   close up the recordset, range and active sheet references...
   objRS = 0
Next      ; next customer
;
;   close up the database and Access
objDB = 0
objAccess = 0
;
;   prompt the user for the filename...
xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")
;   delete the file if it already exists...
If FileExist(xlsFilename) Then FileDelete(xlsFilename)
;   save the workbook...
objExcel.Workbooks(1).SaveAs(xlsFilename)
;   quit excel and close the reference to the application...
objExcel.quit
objExcel = 0
Return

;   Excel Script 1.6
:RunExcelMacro
;   open excel, and an existing workbook...
objExcel = ObjectCreate("Excel.Application")
objExcel.visible = @TRUE
;   setup the filename and open it...
xlFile = "C:\test\MyExcelFile.xls"
objExcel.Workbooks.Open(xlFile)
;
macroParameter = "Hello from Winbatch!"
;   run the macro from the Run method of the application object...
objExcel.Run("WinbatchMacroTest", macroParameter)
;
;   setup the reference to the current workbook and save it...
objExcel.Workbooks(1).Save()
;
objExcel.quit
objExcel = 0
Return

Article ID:   W16785

File Created: 2019:08:14:08:51:54

Last Updated: 2019:08:14:08:51:54

Database Search

TechHome