I'm wondering if anyone has encountered the following problem. In my script, I'm opening an Excel file, reading data from the file and writing PASS/FAIL results back to it. Works great.
The only problem is that the file has a macro which paints the designated PASS/FAIL column Green if a cell has "PASS" in it and "RED" if it has "FAIL" in it.
On "ddt_save"(which I believe actually completes the Write to the cell), the macro is removed from the file.(Going to Tools->Macro->Macros...) Shows nothing. The macro no longer exists.
Is there a way to Write to a File with an embedded macro and not lose the macro?
Kudos to whoever can figure this out(!)
WR data tables are subsets of Excel spreadsheets. While a spreadsheet is readable by WR, when it comes to writing to a cell, I'd bet that it happens at the lowest level - just slamming the data in there, including overwriting macros.
Nope - that is just the way it works, unfortunately. If you write data back to the file, it is going to wipe out any macros that are entered into the file.
It is due (I think) to WinRunner using a stripped down, modified version of an older revision of Excel type functionality to interface with the files. You are pretty much at a whim to what they do with the code.
On option for you might be to create some type of file to write your results out to from the script, and then modify the macro to read in this file and have it use the macro without wiping it out.
Insanity: doing the same thing over and over again and expecting different results
I suspect you are referring to conditional formatting or functions rather than XL macros.
My limited experience would indicate that the macros reside in the module object rather than any cell in a sheet.
I do not know if WR kills off this object but I would hope not. (Unable to ratify as I do not have WR at my current site)
If the macro survives WR then you could write a macro to apply the conditional formatting perhaps as an autostart.
You are right. Having re read the original post it would appear that using WR to save to XL removes the module.
So what you need is for your WR script to copy the XL before it starts and use this copy for its activity.
Once the job is done open the original XL and have an additional macro to repopulate the appropriate sheet from the WR saved copy. Then run your format macro.
When you save an excel file.. you have an option of saving the file as "Microsoft Excel Add-in" or ".xla" file.
To load this .xla file you will have to select it in Tools>>Add-ins menu option. Once you do this this file will be loaded as soon as excel is started.
All macros that you require for formating, generating data etc could be saved in such a file.
So your Data file does not have macros at all. This becomes a global macro addin file...(also makes maintainance easy as you dont have multiple copies of same code in each data file).
Question is what all can be done through Add-ins...
The default addins Analysis Tool Pak, Conditional Tool Pack etc are released by Microsoft in xla formats... i.e. there is no limit to what you can do.
Once again Read Help on excel addins press "F1" [img]images/icons/smile.gif[/img]