insert linked chart from excel to powerpoint
 

Hi,

does anyone know the code to insert a large number of linked chart
from excel into powerpoint?

I have worked out how to export unlinked pictures from excel and a
almost there in PP by copy/pastespecial linking a single linked chart
copying it to a new slide, and overwriting the source, but I'd like t
automate the whole process if possible.

My problem is that in PP there seems to be neither a pastespecia
command nor any means of indicating which chart from a workbook t
display through the insert object method.

Am I misisng something obvious here

--
Message posted from http://www.ExcelForum.com

insert linked chart from excel to powerpoint
 

Hi Nicky,

Does this help?

The macro assumes that the Excel workbook has the same name as the P
file, except for the file extension.

The "Chart_Array" lists the names of the Excel worksheets, each o
which contains a chart. Each charts is then copied as a 'picture'.

Sub Import_Excel_Chart()

Dim Path_Name As String
Dim Last_Slide As Integer
Dim Slide_Index As Integer
Dim Chart_Array As Variant
Dim Chart_Value As Variant
Dim PP_App As PowerPoint.Application
Dim PP_Pres As PowerPoint.Presentation
Dim PP_Pres_Name As String
Dim PP_Slide As PowerPoint.Slide
Dim Excel_App As Excel.Application
Dim Excel_Book As Excel.Workbook
Dim Excel_Chart As Excel.Chart

On Error GoTo ERROR_HANDLER

Path_Name = CurDir & "\"
PP_Pres_Name = Left(ActivePresentation.Name
Len(ActivePresentation.Name) - 4)

Set PP_App = PowerPoint.Application
Set PP_Pres = PP_App.ActivePresentation

Last_Slide = PP_Pres.Slides.Count

If Last_Slide 0 Then
For Slide_Index = 1 To Last_Slide
ActiveWindow.Selection.SlideRange.Delete
Next Slide_Index
End If

Set Excel_App = Nothing
Set Excel_App = New Excel.Application

Excel_App.Application.Visible = False
Excel_App.Application.DisplayAlerts = False

Set Excel_Book = Excel_App.Workbooks.Open _
(Path_Name & PP_Pres_Name & ".xls")

Chart_Array = Array("OUTSTANDING_TRANSACTIONS", _
"OUTSTANDING_AMOUNTS", _
"AGED_RECORDS", _
"AGED_RECORDS_(with Total)", _
"AGED_RECORDS_(stacked)", _
"AGED_RECORDS_(stacked_100)", _
"AGED_RECORDS_(by Date)", _
"AGED_RECORDS_(by Date)_3D")

Slide_Index = 0

For Each Chart_Value In Chart_Array

Slide_Index = Slide_Index + 1

ActiveWindow.View.GotoSlid
Index:=ActivePresentation.Slides.Add _
(Index:=Slide_Index, Layout:=ppLayoutBlank).SlideIndex

Set PP_Slide = PP_Pres.Slides _
(PP_App.ActiveWindow.Selection.SlideRange.SlideInd ex)

COPY_CHART:
Excel_App.Charts(Chart_Value).CopyPicture Appearance:=xlScreen
Format:=xlPicture

PP_Slide.Shapes.Paste.Select

PP_App.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters
True
PP_App.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles
True

With ActiveWindow.Selection.ShapeRange
.Height = 414.88
.Width = 718.62
.Left = 0.75
.Top = 35
End With

ActiveWindow.Selection.Unselect

Next

ActiveWindow.View.GotoSlide (1)

GoTo ENDSUB

ERROR_HANDLER:
Msg = "Error occurred during copy process." & Chr(10)
Msg = Msg + " " & Chr(10)
Msg = Msg + "Re-run macro." & Chr(10)
Msg = Msg + " " & Chr(10)
Msg = Msg + "Process terminated." & Chr(10)
Msg = Msg + " "
Style = vbOKOnly
Response = MsgBox(Msg, Style, Title)

ENDSUB:
Excel_App.Application.DisplayAlerts = False
Set Excel_Book = Nothing
Set Excel_Chart = Nothing
Excel_App.Quit
Set Excel_App = Nothing

Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

Regards,
Mark_

--
Message posted from http://www.ExcelForum.com

insert linked chart from excel to powerpoint
 

Hi Mark,

thanks for that quick response - it works well with a little tweaking.

However, what I really wanted was to insert charts that are linked to
the excel workbook, for a couple of reasons - it saves overwriting the
whole presentation when I only want to update a few charts, and it
looks a little better than the copypicture method, which tends to make
text and lines a little thicker and more blurred than copy pastespecial
links, for some reason.

However, if that's not possible, yours will be the next best thing.


---
Message posted from http://www.ExcelForum.com/

insert linked chart from excel to powerpoint
 

Hi Nicky,

I prefer the above method because I wasn't happy with the formatting i
PP.

I'm not quite clear on what you've already attempted, but did you tr
creating a blank slide, then clicking 'Insert', 'Chart...'? Left-clic
somewhere on the slide to close the 'Datasheet' window and make th
border around the chart disappear. Then right-click on the displaye
chart, point to 'Chart Object' and click 'Open'. On the Microsof
Graph menu bar, click 'Edit', 'Import File' and then select the Exce
file containing your charts. A window will appear allowing you t
select a specific worksheet. Each slide in PP can be linked to
different worksheet and will be automatically updated whenever th
Excel worksheet changes.

Hope this makes sense. Let me know if you have any questions.

Regards,
Mar

--
Message posted from http://www.ExcelForum.com

insert linked chart from excel to powerpoint
 

Hi Nicky,

I prefer the above method because I wasn't happy with the formatting i
PP.

I'm not quite clear on what you've already attempted, but did you tr
creating a blank slide, then clicking 'Insert', 'Chart...'? Left-clic
somewhere on the slide to close the 'Datasheet' window and make th
border around the chart disappear. Then right-click on the displaye
chart, point to 'Chart Object' and click 'Open'. On the Microsof
Graph menu bar, click 'Edit', 'Import File' and then select the Exce
file containing your charts. A window will appear allowing you t
select a specific worksheet. Each slide in PP can be linked to
different worksheet and will be automatically updated whenever th
Excel worksheet changes.

Hope this makes sense. Let me know if you have any questions.

Regards,
Mar

--
Message posted from http://www.ExcelForum.com

insert linked chart from excel to powerpoint
 

Hi Mark, thanks for that - ther appearance is clearer, though th
formatting needs changing. Still can't either link to the excel sourc
file or record/write the code to replicate it though.

The nearest I've come is to copy a chart from excel, pastespecia
pastelink onto the first slide in powerpoint, then run this code t
first copy and then overwrite the linked source for each chart:

Sub overwrite_linksources()
'pastelink a chart from the source file onto the first slide

'chart names:
charts_array = Array("chart1", "chart2", "chart3", "chart4", "chart5"
"chart6")
'source file:
file_fullname = "c:\temp\chart source.xls"

ActiveWindow.View.GotoSlide Index:=1
ActiveWindow.Selection.SlideRange.Shapes(1).Copy

For n = LBound(charts_array) To UBound(charts_array)
ActiveWindow.View.GotoSlid
Index:=ActivePresentation.Slides.Add(Index:=n + 1
Layout:=ppLayoutBlank).SlideIndex
ActiveWindow.View.Paste
ActiveWindow.Selection.SlideRange.Shapes(1).LinkFo rmat.SourceFullName
file_fullname & "!" & charts_array(n)

Next


End Su

--
Message posted from http://www.ExcelForum.com

insert linked chart from excel to powerpoint
 

hi Mark
I finally found a sort of solution, though if you or anyone out ther
has a simpler one, I'd love to hear. I've picked up on your ideas o
opening excel through pp and also inserting an excel chart.

The code below places linked copies of all the charts in a worksheet o
successive slides in a pp presentation by going through thes
processes:

* opens excel
* deletes all slides
* places an unspecified linked chart from the target excel file file o
slide 1
* for each chart in excel, copies the linked chart onto a new slide
* overwrites the link source for each new chart with a link to th
named chart sheet in excel
* updates the links

clunky, but it does the job

Sub overwrite_link_sources()
Dim Excel_App As Excel.Application
Dim Excel_Book As Excel.Workbook
'source file:
file_fullname = "c:\temp\chart source.xls"

Set Excel_App = Nothing
Set Excel_App = New Excel.Application
Set Excel_Book = Excel_App.Workbooks.Open(file_fullname
UpdateLinks:=False)

'delete all slides, then insert linked chart on new slide 1
ActivePresentation.Slides.Range.Delete
ActivePresentation.Slides.Add(Index:=1, Layout:=ppLayoutBlank).Select
ActiveWindow.Selection.SlideRange.Shapes.AddOLEObj ect(Left:=120#
Top:=110#, Width:=480#, Height:=320#, FileName:=file_fullname
Link:=msoTrue).Select
With ActiveWindow.Selection.ShapeRange
.Left = 8
.Top = 12
.LockAspectRatio = msoFalse
.Height = 520
.Width = 700
.LinkFormat.AutoUpdate = ppUpdateOptionManual
End With

'copy linked chart on slide 1
ActiveWindow.View.GotoSlide Index:=1
ActiveWindow.Selection.SlideRange.Shapes(1).Copy

'add new slides, copy the linked chart from slide 1, then overwrite th
link
For n = 1 To Excel_Book.Charts.Count
ActiveWindow.View.GotoSlid
Index:=ActivePresentation.Slides.Add(Index:=n + 1
Layout:=ppLayoutBlank).SlideIndex
ActiveWindow.View.Paste
fncn = file_fullname & "!" & Excel_Book.Charts(n).Name
ActiveWindow.Selection.SlideRange.Shapes(1).LinkFo rmat.SourceFullName
fncn
'ActiveWindow.Selection.ShapeRange.LinkFormat.Upda te
Next

ActivePresentation.Slides(1).Delete ' delete dummy slide

'update links
For Each sld In ActivePresentation.Slides
For Each sh In sld.Shapes
If sh.Type = msoLinkedOLEObject Then
sh.LinkFormat.Update
End If
Next
Next


Set Excel_Book = Nothing
Set Excel_App = Nothing

End Su

--
Message posted from http://www.ExcelForum.com