Moving data from SPSS to live MS Powerpoint Charts
Jim Santa Barbara
I am fairly new to SPSS at a new job doing linear regression modeling.
My question is: is there a way to move data from SPSS to "live"
Microsoft Powerpoint charts? By "live", I mean charts with data
behind them, not just pictures of SPSS graphs.
Currently, I spend way too much time moving data from SPSS datasets to
Excel, and then from Excel to Powerpoint graphs, and if this could be
automated it would really improve my productivity. Any help would be
greatly, greatly appreciated. Thanks for the help!!
Tom
"Jim Santa Barbara" wrote in message
news:7375cd4e.04020...@posting.google.com...
Hi Jim,
Congratulations on your new job. The problem you face is that SPSS sav files are
not directly readable in MS Applications. You need to pass the data through a
provider like ODBC or a compatible intermediary data file (CSV, Excel). Then you
can set this as the data source for the chart in your slide.
You could also put the SPSS chart directly into your slide, with SPSS running
the data in the background, but this would be quite an effort (and quite ugly).
Depending on what you are doing, I would probably suggest you create blank
graphs in PP and use VBA to open the ODBC or CSV file and set it as the data
source everytime you open the slideshow or run a macro. However, because the raw
data usually needs transforming into percentages or counts for use in the graph,
you should probably work it through some SPSS Aggregates or Excel
transformations first. Much as you do now I suppose, only automated.
BTW what do you do to your data in Excel ?
Let us know if I'm on the the right track and which bit you want help with...
Jim Santa Barbara
Thanks so much for the quick reply. I think you are right on with
your solution, I will need to dump the data into Excel and then use
some VBA to move the data into Powerpoint (well, it doesn't SOUND too
hard, except I don't know how to do any of that....yet... but I will
LOL).
I did a little searching and found at least one website that seems to
provide some information on how to do this (see link).
Someone else also recommended I check out:
http://www.take-off.as/datapoint/
which also looks interesting but will take some time to digest.
As far as what I do to the data in Excel.... it varies. I work with a
dataset with about 15,000 obs, and then this dataset is aggregated to
a more manageable dataset of weekly data divided into 6 subsets. I
sometimes simply need to graph my causal data from this aggregated
dataset, but for maybe 200 variables (at most 5-6 go in each chart).
So it just means a LOT of copying and pasting.
At other times, we use Excel to do some simple calculations and then
chart the data we've calculated. But I thought if I could at least
master the first step of automating the graphing of the raw causal
data, that would be a start, and other automation could wait.
Thanks again for the quick reply. Hopefully, I can get working on
this project in the next few weeks, because I think it'll really make
me (and my coworkers) more efficient.
Jim Santa Barbara
Tom
"Jim Santa Barbara" <jsant...@yahoo.com> wrote in message
news:7375cd4e.04020...@posting.google.com...
You are going to save yourself a lot of time. Below is a sample VBScript which
should be useful for the PowerPoint bit. You already know how to export a subset
from SPSS, right? It's one line of syntax or about three lines of VB code. You
could add that to this script. I'm not clear how you transform your data for
graphing, but you could probably include that as well. You could automate Excel
and PowerPoint from SPSS, or automate PowerPoint and SPSS from Excel, or indeed
automate Excel and SPSS from PowerPoint.
Just copy this into a text file with a .VBS extension and double-click it to run
it (from explorer). You could easily change it to create a PP-chart from the
Excel sheet you have open, or a named range, or you could just use the name of
the worksheet in the script.
Any use?
' -- Script to graph a CSV file in PowerPoint --
Option Explicit
Dim oPPApp, oPPT, oPPSlide, oGraph
Const ppLayoutBlank = 12
Set oPPApp = CreateObject("PowerPoint.Application")
Set oPPT = oPPApp.Presentations.Add
Set oPPSlide = oPPT.Slides.Add(1, ppLayoutBlank)
oPPApp.Visible = True
Set oGraph = oPPSlide.Shapes.AddOLEObject( _
120, 110, 480, 320, _
"MSGraph.Chart.8" _
).OLEFormat.Object.Application
' Uncomment the next line to open the graph data file (csv, xls, etc)
'oGraph.FileImport "C:\data\graphdata.xls", , , "Sheet7"
' Sample data
Dim i
For i = 2 To 10
oGraph.DataSheet.Cells(1, i).Value = Chr(95 + i)
oGraph.DataSheet.Cells(2, i).Value = 11 - i
oGraph.DataSheet.Cells(3, i).Value = 11 + i
oGraph.DataSheet.Cells(4, i).Value = 11 \ i
Next