Web Development

Ask something
Discussion Locked

Excel/PowerPoint Linked Tables & Charts

0 helpful
9 replies

by suzannerd

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm trying to write some code that will update tables and charts from Excel linked into PowerPoint when the Excel file is moved. I've used sample code from the MS Knowledge Base that works correctly with the tables (just cells of data). However, it is not working with the charts. It truncates the chart data to just using the entire file name, and then shows the entire first worksheet on the slide. Here is the code:

------------------------------------------------
Sub ChangeExcelSource()

Dim i As Integer, k As Integer, linkpos As Integer
Dim FileNameAndPath As String, linkname As String

FileNameAndPath = "c:\temp\myfile.xls"

'loop through slides
For i = 1 To ActivePresentation.Slides.Count With ActivePresentation.Slides(i)
For k = 1 To .Shapes.Count
With .Shapes(k)

' If the shape's type is an OLE object then...
If .Type = msoLinkedOLEObject Then With .LinkFormat

' Find where in the source path string the character "!" occurs
linkpos = InStr(1, .SourceFullName, "!", vbTextCompare)

' Assign linkname to worksheet reference
linkname = Right(.SourceFullName, Len(.SourceFullName) - linkpos)
.SourceFullName = FileNameAndPath & "!" & linkname
.AutoUpdate = ppUpdateOptionAutomatic
End With
End If
End With
Next k
End With
Next i

ActivePresentation.UpdateLinks
End Sub
------------------------------------------------

Can anyone help???
Thanks!

9 replies

by plumley9

Not the answer you are looking for but a work around that we use. First, charts in Excel are NOT direct objects but embedded objects. This means you are double embedded by the time you get to Power Point. We generate the charts as external objects using the external 'Chart' tool in Microsoft Office. They are updated as the underlying worksheet is changed. OLE in Power Point links to first level objects vary well - direct to the worksheet, direct to a chart. Second level linking - to the chart IN a worksheet - is touchy and I have not had much luck when tried off the local system.
I will check back to see if the better code warriors can help.

by suzannerd

Now that you say this, it makes some sense. However, you can update the charts manually one by one from PowerPoint. Sounds like that's an upgrade to the language that MS needs to make for the next version! Our work around was that the folks that will be downloading the file don't need to update the information from the Excel file anyway, but it's good to know for future reference. Thanks for your help!

by jpratt

First, never trust Microsoft sample code. Its notoriously awful. Second, you need to step through the code to make sure the .SourceFullName data is what you expect. You don't have any error condition checking, like making sure there actually is an "!" character in the source. Message boxes notifying the user of any unexpected condition is a minimum, with a write-out to a text log file the best solution. Either way, the user can provide you with troubleshooting info. Third, VBA help says .AutoUpdate is read-only, at least in Excel. Even if it is truly writable at run-time, it should be set before the .SourceFullName is modified. There is also an .Update method of the .LinkFormat object which you might try calling after the .SourceFullName is modified. Finally, an INI file with a key for your original file path and a key for the user's file path, combined with a Replace method call if the .SourceFullName is not the desired path, will ease the burden of variable file locations.
Hope this helps,

John C. Pratt
Senior Software Developer

by suzannerd

Thanks for your inputs. I had written basically the same code before finding Microsoft's example, except I had a more complicated way of parsing the file name, so I used their more simple example. I had stepped through the code. It gets the correct path, and works correctly for updating a set of rows and columns. It is the charts that do not work correctly. It truncates the path and loses the chart information. I'm thinking Answer Number 1 makes sense if the Excel charts are actually embedding from another program.

by deetee2000

Why not simply paste a link from the Excel sheet to the presentation, then whenever the sheet is saved the data is automatically updated in the presentation? Means you could work with excel without worrying about powerpoint.

D.

by suzannerd

That's exactly what I've done. However, if you move either of the files to a different path, it loses the links. Since our customers will be downloading the files, I don't have control of where they will put them on their machines.

by don christner

I know this isn't what you are asking for but, couldn't the client convert the PowerPoint pres. into a PDF file? This would remove the problem of where files are.

Don

by suzannerd

That would work if they didn't need to make additions to the presentation after they download it. Thanks for your suggestion.

This discussion is closed to new replies.