Exporting data from Excel to Prism
Although Prism can open Excel files, this is slow. Instead, write a Visual Basic macro to export selected values as a text file. The macro below, run from Excel, exports the upper left corner of worksheet 1 (12 rows, 6 columns) to a comma-separated value (CSV) file, which Prism can import.
Open "C:\data\july97\dr2.csv" For Output As #1
For I = 1 To 12
For J= 1 to 6
Print #1, Worksheets(1).Cells(I,J).Value; ",";
Next
Write #1,
Next
Close #1
Follow this with code to launch Prism, run the Prism script, and perhaps import results from Prism back to Excel.
Creating a PZC (Prism script) file from Excel
While creating a system to run Prism from Excel, you need to edit both the Excel VBA statements and the Prism PZC script. One way to simplify your work is to write Visual Basic code that exports the PZC file from Excel. Then you only have to edit one file. For example, you could use the following VBA code:
Open "C:\Prism4\auto.pzc" For Output As #1
Print #1, “Shortlog”
Print #1, “SetPath C:\gp\prism25\”
Print #1, “Open generate.pzm”
Print #1, “openOutput fit.txt”
Print #1, “ ForEach 500”
Print #1, “ GoTo R 1”
Print #1, “ Regenerate”
Print #1, “ GoTo R 2”
Print #1, “ WCell 5,1”
Print #1, “ Next”
Close #1
Shell ("C:\program files\prism5\prism.exe @C:\prism5\auto.pzc")
The first line starts a new file called auto.pzc and designates it as file number 1. The next 10 lines, export Prism script commands you have written into that file. The next line closes the script file, which is launched by the final line shown above. Further lines would import the results exported by Prism.
Putting all the commands into one file doesn't help conceptually. You still have to distinguish the VBA code within Excel from the script commands of Prism. The advantage is that you can do all the editing within one file, rather than two. The disadvantage is that writing the script is a bit harder, as you have to place each line in quotations and precede it with "Print #1,".
Putting Prism graphs into Excel
In addition to importing the results of Prism analyses, you may want to import a Prism graph into Excel. Exporting the graph is no problem, as a Prism script can export a graph or layout as a WMF picture. The problem is importing it into Excel, as there is no way to write Visual Basic commands to import a picture. You can do it indirectly. Create a picture object manually in Excel (display the control toolbar, click on the image button, then drag to create the object). Then write Basic code to change the contents of that picture object. For example,
Sheet2.DRGraph.Picture = LoadPicture(Path$ + "dr2.wmf")
