TUCOWS ARTICLE

How To Read/Write Excel Data with Macro Scheduler

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.
Published: Nov 13, 2007
Author: Marcus Tettmar
Categories:
Macros and automation
Software that can help Good for Cow Rating
Macro Scheduler Automation Tool 14.1.02
Shareware
Download Save time and money by automating your work. Record macros or use the easy script...

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

2) Use DDE

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


About Marcus Tettmar

Marcus Tettmar is founder and CEO of MJT Net Ltd, specialists in windows automation and publishers of Macro Scheduler, a leading automation tool for Microsoft Windows; and WebRecorder for Web Application Automation. Find out more at http://www.mjtnet.com

Digg This
Please login to add your comment
Leave A Comment
Name: