I work on SAS 9.1 and have a task to create a Excel spreadsheet with a pie chart on it.
I am able to create the spreadsheet(numbers and data) using ExcelXP, tagset and proc template, but don't know how to create a Pie Chart from the data.
Googling led to DDE technique and the X command, but I dont know how to use them from mainframe.
Could you please let me know if it is possible to create a XML file with embedded VB macro code, so that the VB macro creates the Pie Chart?
1. ExcelXP tagset creates XML into a output file.(ok here)
2. Edit the output XML file in the next step and insert some statements, so that when XML is opened it executes a VB macro to create a pie chart. (No idea on this part)
Please feel free to correct me or suggest your ideas about how to go about this requirement.
Why not just have the macro in the spreadsheet? On "open" of the particular sheet, macro kicks-in and Pie appears. All you do is provide the data.
Process, of course, includes all the good things like checking the header (logical file name, business/data date, run-date and time) and trailer (count of records, hash-totals of value fields, etc).
There's lots of stuff you can do to make it "professional" and easy to use. Use the same rigour on the PC as on the mainframe. You might get "territory conflicts" but if well-designed and specified, there can't be much genuine complaint.
The spreadsheet is generated in the form of XML from mainframe, Still have not figured out how to embed macro/VB in XML data file.
Are you suggesting to download the XML from mainframe and then manually run a macro on it? I could do this but it involves some amount of manual intervention and manual work. So looking to automate the whole thing.