Linking Excel Range to Pow.Pt. picture

  • Thread starter Thread starter Kate
  • Start date Start date
K

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
 
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?
 
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.
 
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
 
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.
Click to expand...

If all the long shots were this short, it'd be an easy life indeed.

To link content from Excel to PPT:
Open your PPT file
Open your Excel file. It must first have been saved at least once for this to
work.
Select the content in Excel and choose Edit, Copy from the main menu bar (or
press Ctrl+C)
Switch to PPT, go to the slide where you want the content to appear.
Choose Edit, Paste Special and click Link.

Done. Size to taste. Rightclick, choose Format and on the color tab choose
Recolor to remap Excel colors to PPT colors if you need to.

It won't be a bitmap -- actually it'll all be vector graphics and text, which
gives you better quality as a rule.

When you update the spreadsheet, PowerPoint will update the data in your PPT
file the the next time you open the PPT.

--
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Featured Presenter, PowerPoint Live 2004
October 10-13, San Diego, CA www.PowerPointLive.com
================================================
 
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


.
Click to expand...
 
Back
Top