Using Macros to Place SigmaPlot Charts Within Microsoft®Applications

 

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\SPW13

but you can easily find this path by using the Search or Find feature in your Start button and searching for “Spw.exe”

This macro:

1. imports data from an Excel well plate file

2. creates a graph using the X, Y Replicate row replicate format

3. 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.

1. Select Macros, Record New Macro from the Toolbox ribbon main menu

2. Select the Main Button, New, Notebook, OK

3. Select Import, Import File, from the Worksheet ribbon and locate the “well plate data.xls” file that you placed in your SigmaPlot directory.

4. Click on this file to select it and click “Open”.

5. Click Import again to import the entire Excel worksheet

6. 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).

7. 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.

8. Select Row Means for the Symbol Value and leave Standard Deviation as the Upper and Lower error bar calculation.

9. Click Next. Make sure X,Y Replicate is selected and click Next again.

10. Click Finish.

11. Click the Stop Recording button on the small macro recording dialog and enter the macro name “Graph from Excel File”. Click OK.

12. 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.

If you run the Graph to PowerPoint macro in the Graph to PowerPoint.jnb notebook you need to change the path from:

Path = “C:\Program Files\SigmaPlot\SPW13\well plate data.xls”

to the path where you placed the well plate data.xls file.

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.

If you run the Graph to Word macro in the Graph to PowerPoint.jnb notebook you need to change the path from:

Path = “C:\Program Files\SigmaPlot\SPW13\well plate data.xls”

to the path where you placed the well plate data.xls file.

Try SigmaPlot FREE for 30 Days!