|
Keywords: SigmaPlot Download, sigmaplot, download, SigmaPlot, Download
Using Macros to Place SigmaPlot Charts in Microsoft Products
This macro requires that you install the "well plate data.xls" file in your SigmaPlot directory. The path to this directory will be similar to
C:\Program Files\SigmaPlot\SPW7
but you can easily find this path by using the Search or Find feature in your Start menu and searching for "Spw.exe"
This macro
- imports data from an Excel well plate file
- creates a graph using the X, Y Replicate row replicate format
- copies the graph in SigmaPlot and pastes it to a PowerPoint slide.
The macro statements for importing data and creating the graph were created with the macro recorder. One statement was then modified to allow the Excel graph to be found on all computer
installations. The macro statements were then added which open PowerPoint (if necessary), add a slide and paste the graph.
SigmaPlot includes a macro Paste to PowerPoint Slide. This is much more sophisticated than what is presented here. This presentation is tutorial in nature and shows the smallest amount
of macro code required to paste a graph into PowerPoint.
Recording File Import and Graph Creation
The steps used to record the well plate file import and graph creation are outlined below. It is useful to see how easy it is to record the file import and select a region of the
well plate worksheet to be graphed. If you want you can view the graphics below which show the important steps in the recording process and skip to the section on Adding PowerPoint Automation Statements. Working
versions of all macros discussed here are contained in the Graph to PowerPoint Macro SigmaPlot notebook for you to use.
 |
- Select Tools, Macro, Record New Macro from the main menu
- Select File, New, Notebook, OK
- Select File, Import... and locate the "well plate data.xls" file that you placed in your SigmaPlot directory.
- Click on this file to select it and click Import.
- Click Import again to import the entire Excel worksheet
- Highlight the well plate data showing replicates across rows for different inhibitor concentrations down columns (the log concentration data in
column 2 has been artificially added to this worksheet for presentation simplicity).
- Select the Line/Scatter Plot icon and then click the Simple Line & Scatter - Error Bars sub icon. The first dialog of the Graph Wizard appears.
- Select Row Means for the Symbol Value and leave Standard Deviation as the Upper and Lower error bar calculation.
- Click Next. Make sure X,Y Replicate is selected and click Next again.
- Click Finish.
- Click the Stop Recording button on the small macro recording dialog and enter the macro name "Graph from Excel File." Click OK.
- This macro will be saved in the SigmaPlot Macro Library.jnb notebook when you exit SigmaPlot and respond Yes to the prompt.
The macro "Graph from Excel File" has been created. It will import data from an Excel file, select the data region of the worksheet and create the following graph.
A copy of this recorded macro is included in the SigmaPlot notebook Graph to PowerPoint.jnb. You may run it by double clicking on the Graph from Excel
File macro icon in this notebook and selecting Run from the Macros dialog.
Adding PowerPoint Automation Statements
To paste the graph to a PowerPoint slide, PowerPoint automation statements must be added to the macro. Double click the macro Graph to PowerPoint in
the SigmaPlot notebook Graph to PowerPoint.jnb and select Edit. The macro with the additional PowerPoint statements is displayed.
We use some of the PowerPoint constants (e.g., ppLayoutBlank to create a blank slide) and, in order to do this, we need to select the PowerPoint Macro
Reference. Click the Macro References button In the SigmaPlot macro editor.
Find and select the Microsoft PowerPoint Object Library and click OK.
Save the SigmaPlot macro to save this reference for this macro.
The first two statements are SigmaPlot automation statements that select the graph and copy it to the clipboard.
'copy SigmaPlot graph
ActiveDocument.CurrentPageItem.SelectAll 'select everything on
pageActiveDocument.CurrentPageItem.Copy 'copy selection to clipboard
The next group of statements defines PPApp as a PowerPoint object in SigmaPlot and then starts PowerPoint if it is not already open.
'start PowerPoint
Dim PPApp As Object
Set PPApp=CreateObject("PowerPoint.Application") 'create PowerPoint application object
PPApp.Visible=True 'start PowerPoint (if its not already
running)
The Add method is first used to create a new PowerPoint presentation and then to add a blank slide to this presentation. The ppViewSlide and ppWindowMaximized constants are used to change to the Slide View and
maximize it.
'set up PowerPoint window and slide layout
PPApp.Presentations.Add 'create a new presentation
PPApp.ActivePresentation.Slides.Add PPApp.ActivePresentation.Slides.Count+1, ppLayoutBlank
PPApp.ActiveWindow.ViewType = ppViewSlide 'change from Normal
View to Slide View
PPApp.ActiveWindow.WindowState = ppWindowMaximized 'maximize Slide View window
The contents of the clipboard (a SigmaPlot graph) are then pasted into the slide view. System resources are then released by terminating the PowerPoint application.
'paste SigmaPlot graph to PowerPoint slide
PPApp.ActiveWindow.View.Paste 'paste the SigmaPlot graph into the slide view
Set PPApp = Nothing 'release system resources
Running this macro will paste the graph imported from an Excel worksheet into a PowerPoint slide.
An obvious question is, "how easy is it to modify this macro to work with Microsoft Word"?
Pasting a Graph into Word
This is a simple modification of the PowerPoint macro. We'll keep the startup and shutdown portions of the PowerPoint code (modifying it to refer to Word)
and use the Word macro recorder to get an idea about what Word automation code to use.
With slight modifications the initial macro code is identical to that in PowerPoint.
'start Word
Dim WApp As Object
Set WApp=CreateObject("Word.Application") 'create Word application object in SigmaPlot
WApp.Visible=True 'start Word (if its not already
running)
Recording the opening of a new blank Word document and pasting of a SigmaPlot graph gives
Documents.Add DocumentType:=wdNewBlankDocument
Selection.Paste
This was recorded in Word. Since we are creating a macro to run from SigmaPlot we need to specify the application and window so these statements become
'set up blank Word document
WApp.Documents.Add DocumentType:=wdNewBlankDocument
'paste SigmaPlot graph to Word document
WApp.ActiveWindow.Selection.Paste
With slight modification, the statement releasing system resources remains the same.
We need to reference the Microsoft Word Object Library. Click the Macro References icon in the SigmaPlot macro edit window and select this library. Save the macro to save this reference.
And that's it. You can run a copy of this macro by double clicking on the Graph to Word macro in the Graph to PowerPoint Macro notebook.
Back to SigmaPlot Product Uses Page.
|
