Macro Recording and Automating Windows with Macro Scheduler – Tips & News
Edauthier posted a script to the forum which invokes Merlin the Wizard with a little message for you. I had to make a few small changes. I also compiled it into an EXE.
Here’s the code. Download a zip file containing the EXE. Just run it. Enjoy.
Received this nice email today, and just had to share it. Emphasis mine:
Dear Marcus
Having visited your web site over the past few months and analysed the on-line demo’s, I was convinced that your product would help me in shortening the time I had to spend on one particular aspect of my business, so much so that I purchased AppNavigator and Macro Scheduler primarily for this one task.
This task, meant me spending on average at least 1-2 days per month, and having just ran the same task using your product I am delighted to state that it was completed in under 1 hour with no complications – other than one small glitch (my fault) which was superbly addressed by your support team. This is all the more impressive when you consider that I am not a programmer and have never written a line of code in my life.
The task that used to take me 1-2 days a month to do manually is now fully automated at night. Put another way, from my perspective the product has already paid for itself and the savings will be ongoing.
Furthermore the time I have saved will also be productive and so produce further benefits, and this is without considering the human aspect of tedium with repetitive tasks, which should really never be overlooked.
I hate to tempt the god’s by saying this (just in case you decide to hike the pricing!) but your product is indeed excellent value for money, and straightforward to use.
I have now identified other tasks within the business where your products will once again save me time, and further enhance the “investment” in your software.
Often in life we are quick to criticise and slow to praise, so hopefully this letter of appreciation will help redress this imbalance.
In thanks and appreciation.
Yours Sincerely,
Gerry Pentleton
ERA UK
Did you know there’s now a way you can get a free copy of Macro Scheduler Std (Home/Non-commercial Edition)? And it doesn’t involve cracks! We’ve teamed up with TrialPay to offer Macro Scheduler Standard for free.
TrialPay is a new service that offers you software for free as a reward for signing up for a special offer from one of their partners. Here’s how it works: Large companies like Gap, Cingular, Discover, Amex, Vonage, Blockbuster, eBay and TiVo will pay a reward for new customers. In the old days this went to some marketing firm or unhelpful sales assistant, but now, with TrialPay, you can effectively claim this reward for yourself, for being a new customer.
TrialPay has partnered with 100s of companies to use their reward money as a way to pay for stuff that you actually want. Sign up for a Discover card, a new mobile phone from Cingular, a new eBay account or Blockbuster online, etc. And to say thanks, they will buy a copy of Macro Scheduler for you.
So if you can’t spare the cash to purchase Macro Scheduler, or you know someone who needs a copy but can’t bear to part with the funds, this could be a great option. And there’s no need to go looking for software cracks. You can now get a legitimate copy for free!
Feel free to forward this offer to anyone you think might benefit from it.
We had a customer email us the other day having a problem getting his macro to work reliably. He wanted to start an application, so he had written a macro to left click on the application’s shortcut on the desktop. But sometimes the icon moved. He wanted to know how to reliably know where the icon was.
Reality check. There’s an easier way! You don’t need to know where the icon is.
Let’s just think about this for a second. What is a shortcut? As its name suggests it’s a shortcut to something. In this case the shortcut was to an application. We need to start the application. So why not start it directly? Why click on the shortcut at all?
Right clicking on the shortcut and selecting properties tells us the path of the application in the “Target” field. Usually all we need to do is copy that and stick it in a Run Program command:
Run Program>C:\Program Files\FileZilla\FileZilla.exe
Occasionally an application needs to start in a different folder. In the shortcut properties you’ll see a field called “Start in”. If necessary copy the value there and put it in a Change Directory command. So we end up with:
Change Directory>C:\Program Files\FileZilla
Run Program>C:\Program Files\FileZilla\FileZilla.exe
You can even run a shortcut itself:
ExecuteFile>C:\Documents And Settings\Marcus\Desktop\FileZilla.lnk
This way the shortcut is executed just as if you clicked it and all the attributes of the shortcut are used.
No mouse clicks needed!
If you really *do* want to find an icon on the desktop reliably, use Image Recognition.
Or you can even use keystrokes to invoke a shortcut.
SetFocus>Program Manager
Send>FileZilla
Press Enter
But for just starting an application, all this is rather unnecessary. Run the application directly.
Looking at our referrer logs it seems we get quite a few visitors linking through from our listing at:
http://www.itlocation.com/en/software/prd46729,,.htm
But I notice there’s no user review yet. Care to add one?
Wikipedia lacks a “Macro Scheduler” page. It should be neutral (i.e. not written by me). Anyone can create one. Want to start it? Go to: http://en.wikipedia.org/wiki/Macro_Scheduler and get stuck in. Anyone can contribute.
Many thanks to Ferry Halekor for successfully translating Macro Scheduler to Dutch. He’s now about half way through translating the help file. Ferry has also made a Dutch website especially for Macro Scheduler. If you read Dutch, check it out.
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
Looping through a text file is commonly achieved with the ReadLn command in a loop, like the example in the help file:
Let>k=1 Label>start ReadLn>c:\\\temp\\\test.txt,k,line If>line=##EOF##,finish MessageModal>line Let>k=k+1 Goto>start Label>finish
This uses a very simple loop and loops through the file one line at a time using the ReadLn command and a counter variable.
An alternative method is to read the entire file into memory and then put the lines into an array. This means seeking through the file becomes faster, and it becomes easier to change individual lines. Use the ReadFile command to read the entire file content into a variable:
ReadFile>c:\\\temp\\\test.txt,TheFileData
We now have a variable called TheFileData containing all the lines in the file. Each line will be separated by a Carriage Return, Line Feed combination (CRLF). So we can now split the file into an array using the Separate command, specifying CRLF as the delimiter:
Separate>TheFileData,CRLF,Lines
Lines_Count now tells us how many lines there are and we can access each line with Lines_1, Lines_2, etc. So here’s the new version of the full script:
ReadFile>c:\\\temp\\\test.txt,TheFileData Separate>TheFileData,CRLF,Lines Let>k=1 Repeat>k Let>CurrentLine=Lines_%k% MessageModal>CurrentLine Let>k=k+1 Until>k>Lines_Count
When trying to execute a third party application, such as Macro Scheduler, from VBA in Office 2007 using the Shell function the following error is returned:
“Invalid procedure call or argument”
Sometimes you might want to run a Macro Scheduler macro from Office using this method and get this error. Searching the web reveals many people reporting the same error when trying to run other applications, including other Office apps! E.g.:
Shell “c:\my macros\macroA.exe”
I suspect there’s an issue with spaces in the path but trying to embed extra quotes with chr(34) doesn’t seem to help. The Shell function also doesn’t provide a way to specify command line parameters other than including them in the entire command string.
The solution is to use the ShellExecute API function. Declare the function in the General Declarations section of your VBA module using this code:
Private Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Then you can use the function as follows:
ShellExecute 0&, “”, “c:\program files\macro scheduler\msched.exe”, “calculator”, “”, 1
This code runs the Calculator sample macro in Macro Scheduler.
To run an application with no command line parameters leave the fourth parameter empty:
ShellExecute 0&, “”, “c:\my macros\mymacro.exe”, “”, “”, 1