UPDATE: 25/09/2012 – This article was written in 2007 before Macro Scheduler contained native Excel functions and native database access. Furthermore, since this article was written Excel no longer supports DDE. This article has therefore been superseded and should only be used as a reference for use with older versions of Macro Scheduler and/or older versions of Excel. For the latest version (currently v13) look at the native Excel functions (XLOpen, XLGetCell, XLSetCell etc) and the native database functions. For an example of querying Excel using the native database functions see this post.
There are a number of ways that Macro Scheduler can interact with Excel to retrieve or modify data. I’ll introduce you to three methods here.
1) Forget Excel!
Yes, sometimes you can forget about Excel. Often people actually just want to read from/write to a CSV (Comma Separated Values) file. Because Excel is on so many people’s computers and is the default file association for CSV files it seems many people think CSV files are Excel files. Not so. If the file you want to read is a .CSV file then it’s really just a text file containing the data. Each line represents a row of data and each column is separated by commas (or sometimes semicolons – but it could be anything else). Open the CSV file up into Notepad and you will see something like this:
"id","description","quantity","price" 5343,"earl gray tea",32,5.64 2435,"decaffeinated coffee",26,4.67 1433,"chocolate cookies",12,3.98
String values may or may not be delimited by quote characters, as in my example above.
We can forget about Excel if we want to read this data in with Macro Scheduler. We can instead just go direct and use the ReadFile or ReadLn functions which are used to access text files. This post shows you how to read a text file in line by line. The only part missing is to extract each field or column from each line (record). Well, as with the ReadFile loop in example 2 of this post, we can use the Separate command, and specify the comma character as the delimiter:
Let>comma=, Separate>line,comma,fields
This would give us fields_1, fields_2, fields_3 and fields_4 containing each item in the line. Combining that code into my ReadFile loop example to show each field would give:
ReadFile>c:\\\temp\\\test.txt,TheFileData
Separate>TheFileData,CRLF,Lines
Let>comma=,
Let>k=1
Repeat>k
Let>CurrentLine=Lines_%k%
//extract each field
Separate>CurrentLine,comma,fields
MessageModal>%fields_1% %fields_2% %fields_3%
%fields_4%
Let>k=k+1
Until>k>Lines_Count
Want to remove the quotes from the string fields? Use StringReplace:
StringReplace>fields_2,",,fields_2
To write data out to a CSV file simply use the WriteLn command:
Let>LineToWrite=1234,cornflakes,1,1.99 WriteLn>filename.csv,result,LineToWrite
Update: Here’s another way to read CSV files which also solves the problem of quoted field values containing commas.
2) Use DDE
EDIT: DDE is no longer supported in the latest versions of Excel. See editor’s note at top.
DDE is an abbreviation for Dynamic Data Exchange and is an old protocol for interfacing with applications. It has largely been superseded by other methods such as COM but Microsoft Excel still supports it and it provides a simple way to access cells in Excel directly.
Use the DDERequest command to retrieve an item of data from Excel. Excel must be running and the spreadsheet must already be open. We could easily make our macro open the worksheet for us using the ExecuteFile command (or Run Program). We then use DDERequest something like this:
DDERequest>Excel,c:\\\documents\\\example.xls,R1C1,
myVariable,60
The first parameter is the Server name, which is always just Excel for Microsoft Excel. The second parameter, the DDE “Topic”, should be set to the filename of the spreadsheet (which must be open). The third parameter specifies the cell to retrieve. This is constructed RnCn (Row n, Column n). Use numeric values for both Row and Column. So Cell A1 would be R1C1. Cell B1 would be R1C2. Cell D14 would be R14C4, etc. The next parameter is the variable name to store the result in. And finally we specify a timeout value in seconds. If the command fails to locate within that amount of time it will timeout and the result variable would contain “DDE_TIMEOUT”.
The second parameter can also contain a sheet name when you want to access data on a particular worksheet. The Sheet name is added after the filename, separated from the filename by a colon. So parameter two would become: Filename:Sheetname
One thing to note is that Excel often appears to append a CRLF sequence to the end of the data. Therefore we should remove it as follows:
StringReplace>myVariable,CRLF,,myVariable
Take a look at the “Extract From Excel” sample script which comes with Macro Scheduler for an example which uses the DDE method to retrieve data from Excel and paste it into Notepad.
To modify a cell value, or write a value to Excel we use the DDEPoke command as follows:
DDEPoke>Excel,c:\\\documents\\\example.xls,R2C3,
56.75
Again, the spreadsheet must be open for this to work. The above puts the value 56.75 into cell C2 (row 2, column 3). As with DDERequest you can add a worksheet name in parameter two if needed (Filename:SheetName).
For another example see:
Exchange Data with Microsoft Excel The Easy Way
3) VBScript via COM
VBScript offers the most flexible and powerful method of interacting with Microsoft products. Not only can we modify data using this method we can also control almost any part of Excel. Anything that you can program or record in Excel can be converted into VBScript code and used inside a Macro Scheduler script.
Here is an example script with some functions you can use to retrieve and modify Excel cells:
//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
End Sub
'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
GetCell = xlSheet.Cells(Row, Column).Value
End Function
'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND
//Do the business
VBRun>OpenExcelFile,%SCRIPT_DIR%\example.xls
VBEval>GetCell("Sheet1",5,4),theValue
MessageModal>Cell value: %thevalue%
VBEval>SetCell("Sheet1",28,2,998),nul
//VBRun>CloseExcel
This code is constructed in such a way to allow you to call the VBScript functions from regular MacroScript code and therefore retrieve the data to regular variables.
For another example see:
Automate Excel with VBScript
You can also run Excel macros:
Run Excel Macros and VBA Routines
And, as stated above, you can do a whole heap of other things. Search the forums for Excel to find other examples. Converting from Excel VBA to Macro Scheduler VBScript is a topic for another day but hopefully you can get an idea from these simple examples.
___
You may also be interested in: Accessing Databases
