Linking Excel Range to Pow.Pt. picture

[Kate]

Hi,

I know this is a long shot, but I'm hoping SOMEBODY has
the answer....

I need to create a power point presentation that links to
an excel workbook. The reason I need to link it is
because the numbers will change monthly, and the
presentation is distributed monthly.

So, I need to create a presentation that will:

1) have excel worksheets in it that link to defined ranges
in a workbook.
2) HOPEFULLY have the Pow.Pt. worksheet appear as a bitmap
(if not thats ok)
3) allow the Pow.Pt. SS (or the picture) to link to the SS
so that the image will change every month.

I know it's a long shot, but any help would be GREATLY
appreciated.

Thanks
Kate

 

[Stephen Mayr]

Hi, Kate

I recently did something like this using VBA. You can
update multiple objects using any number of excel files.

Does this sound like something you can use?

 

[Kate]

Absolutely, but I'm not too familiar with the specifics in
the VB code. Do you have a copy of the code to use?

Thank you so much.

 

[Stephen Mayr]

Here you go. You can repeat this code for each slide. This
code updates slide 1. I use the variable cnt to determine
which object it's on so that I know what Excel file to use.

Go over it and if you have specific questions, I'll try to
answer them.

Beginning of code
-----------------------------------------------------------

Public Const Range = "A1:AA15"
Public Const RangeMG = "C1:AA15"
Public Const Range2 = "B1:AA15"
Public Const CurMth = "Jul 2004"
Public Const PreMth = "Jun 2004"


Sub UpdateGraph()
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim rngNewRange As Excel.Range
Dim oGraph As Object
Dim strPath As String
Dim cnt As Integer
'
' Set oPPTApp to PowerPoint by creating a new instance of
PowerPoint.
' If PowerPoint is already open, you would instead use the
GetObject
' method instead.
'
Set oPPTApp = CreateObject("PowerPoint.Application")
'
' Set PowerPoint to be Visible.
'
oPPTApp.Visible = msoTrue
'

'---------------------------------------------------------
' Total Market Slides
'---------------------------------------------------------

'Repeat code starting here to update different slides
' On slide 1 of Presentation1.ppt, loop through each shape.

cnt = 0

With oPPTApp.ActivePresentation.Slides(1)
For Each oPPTShape In .Shapes


' Check to see whether shape is an OLE object.
'
If oPPTShape.Type = msoEmbeddedOLEObject Then

'
' Check to see whether OLE object is a Graph 2000 object.
The ProgID
' is case sensitive.
'
If oPPTShape.OLEFormat.ProgID
= "MSGraph.Chart.8" Then
cnt = cnt + 1

' Set rngNewRange to the collection of cells in the active
Excel
' workbook and active sheet.

If cnt = 1 Then
strPath = "C:\Test\Total\Chart1.xls"
Workbooks.Open FileName:=strPath
ActiveSheet.Range(Range).Select
' Select the range then copy it.
Selection.Copy
End If


If cnt = 2 Then
ActiveWorkbook.Close False
strPath = "C:\Test\Total\Chart2.xls"
Workbooks.Open FileName:=strPath
ActiveSheet.Range(Range).Select
' Select the range then copy it.
Selection.Copy
End If
'
' Set oGraph to the Graph object on the slide.
'
Set oGraph = oPPTShape.OLEFormat.Object

'Graph Titles

If cnt = 1 Then
oGraph.ChartTitle.Text = "Chart 1 Title" &
Chr(13) & "(Switch/Add Combined)" & Chr(13) & "Jan 2003 -
" & CurMth
End If

If cnt = 2 Then
oGraph.ChartTitle.Text = "Chart 2 Title" &
Chr(13) & "Jan 2003 - " & PreMth & " (1 Month Lag to
distinguish)"
End If



'
' Paste the cell range into the upper leftmost cell of the
graph
' datasheet. This position is designated "00" (two zeros).
To designate
' a range to start in the second row, first column, you
would use "01".
' Likewise first row, second column is "A0". This will
also link the
' datasheet to the Excel Workbook cell range. If you do
not want to
' link to the Workbook, just omit the word "True". The
default
' choice for the Paste method is "False".
'
oGraph.Application.DataSheet.Range
("00").Paste True
'update link
oGraph.Application.Update
End If

End If


'
' Select the next shape on the slide.
'
Next oPPTShape
End With

ActiveWorkbook.Close False

'Paste copied code here to update different slides

End Sub

 

[Kate]

thanks so much!

-----Original Message-----
Here you go. You can repeat this code for each slide. This
code updates slide 1. I use the variable cnt to determine
which object it's on so that I know what Excel file to use.

Go over it and if you have specific questions, I'll try to
answer them.

Beginning of code
---------------------------------------------------------- -

Public Const Range = "A1:AA15"
Public Const RangeMG = "C1:AA15"
Public Const Range2 = "B1:AA15"
Public Const CurMth = "Jul 2004"
Public Const PreMth = "Jun 2004"


Sub UpdateGraph()
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim rngNewRange As Excel.Range
Dim oGraph As Object
Dim strPath As String
Dim cnt As Integer
'
' Set oPPTApp to PowerPoint by creating a new instance of
PowerPoint.
' If PowerPoint is already open, you would instead use the
GetObject
' method instead.
'
Set oPPTApp = CreateObject("PowerPoint.Application")
'
' Set PowerPoint to be Visible.
'
oPPTApp.Visible = msoTrue
'

'---------------------------------------------------------
' Total Market Slides
'---------------------------------------------------------

'Repeat code starting here to update different slides
' On slide 1 of Presentation1.ppt, loop through each shape.

cnt = 0

With oPPTApp.ActivePresentation.Slides(1)
For Each oPPTShape In .Shapes


' Check to see whether shape is an OLE object.
'
If oPPTShape.Type = msoEmbeddedOLEObject Then

'
' Check to see whether OLE object is a Graph 2000 object.
The ProgID
' is case sensitive.
'
If oPPTShape.OLEFormat.ProgID
= "MSGraph.Chart.8" Then
cnt = cnt + 1

' Set rngNewRange to the collection of cells in the active
Excel
' workbook and active sheet.

If cnt = 1 Then
strPath = "C:\Test\Total\Chart1.xls"
Workbooks.Open FileName:=strPath
ActiveSheet.Range(Range).Select
' Select the range then copy it.
Selection.Copy
End If


If cnt = 2 Then
ActiveWorkbook.Close False
strPath = "C:\Test\Total\Chart2.xls"
Workbooks.Open FileName:=strPath
ActiveSheet.Range(Range).Select
' Select the range then copy it.
Selection.Copy
End If
'
' Set oGraph to the Graph object on the slide.
'
Set oGraph = oPPTShape.OLEFormat.Object

'Graph Titles

If cnt = 1 Then
oGraph.ChartTitle.Text = "Chart 1 Title" &
Chr(13) & "(Switch/Add Combined)" & Chr(13) & "Jan 2003 -
" & CurMth
End If

If cnt = 2 Then
oGraph.ChartTitle.Text = "Chart 2 Title" &
Chr(13) & "Jan 2003 - " & PreMth & " (1 Month Lag to
distinguish)"
End If



'
' Paste the cell range into the upper leftmost cell of the
graph
' datasheet. This position is designated "00" (two zeros).
To designate
' a range to start in the second row, first column, you
would use "01".
' Likewise first row, second column is "A0". This will
also link the
' datasheet to the Excel Workbook cell range. If you do
not want to
' link to the Workbook, just omit the word "True". The
default
' choice for the Paste method is "False".
'
oGraph.Application.DataSheet.Range
("00").Paste True
'update link
oGraph.Application.Update
End If

End If


'
' Select the next shape on the slide.
'
Next oPPTShape
End With

ActiveWorkbook.Close False

'Paste copied code here to update different slides

End Sub
---------------------------------------------------------

End of code


.

 

[Kurt]

Kate,
Also have a look at www.take-off.as/datapoint if you want to have a ready
made solution or addon.
Kurt