How To Read/Write Excel Data with Macro Scheduler
|Published:||Nov 13, 2007|
|Software that can help||Good for||Cow Rating|
|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:
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:
To write data out to a CSV file simply use the WriteLn command:
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:
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:
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:
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
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