VBA script and data links

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have a script which updates all the links in a presentation. What I
want to know is, is there a way to break the link from within the
macro, prior to saving with a different name, so that the saved
presentation will not automaically update the link if clicked on.
Failing that (or actually preferable to that) is there a way in VBA to
turn on and off the link update? (i.e.: from automatic to manual).

TIA
David
 
Shyam,

As always you have an excellent example of what I am attempting to
accomplish. My problem is that the debugger shows that the "If
sh.Type = msoLinkedOLEObject Then" is never satisfied. Is there
anyway to identify the sh.Type of the object? When I put a watch on
it the value only shows up as a numeric (7, 14, etc.) Is there
anyplace I can find a table that tells me what the numeric is
equivilant to?

I feel I'm getting closer to what I need ... just can't get the gold
ring.

David
 
David,
7 = EmbeddedOleObject
14 = Placeholder

To get names associated with values or vice versa you need to use the object
browser.

Switch to VBE
Press F2 to bring up the object browser
In the find field type msoShapeType and press <Enter>
In the results window click on the 1st entry.
Under 'Members of msoShapeType' category you will find the enumerations
listed.
Select any enumeration and the value will appear below. Something along the
lines of
Const msoPlaceholder = 14
Member of Office.MsoShapeType
 
Shayam,

An update to my previous query.

Although the datasheet is linked to an EXCEL spreadsheet, the graph
object is considered an msoEmbeddedObject to which
sh.LinkFormat.AutoUpdate does not apply.

Is there any way to identify the datasheet and it's associated link?
Do you know of any documentation sites I can possibly get more info on
this? (aside from yours, of course)
 
If it is showing up as an msoEmbeddedObject shape then it is not a linked
object.
Are you sure that the shape is linked and not embedded?
 
Actually I'm confused. I was hoping you could shed some light. The
object is an MSGraph whose datashet is linked to an Excel spreadsheet.
How do I identify the datasheet, which is the linked object, with
sh.type? Is the datasheet of an MSGraph considered a separate object?

Again, I'm missing something, just don't know what.

Thanks again for the help. I'll probably kick myself when I get the
solution.

David
 
Just a follow up to my last note. Just ran it through the debugger
again and all I found were types 7,14,17. Does this mean I cannot
identify the datasheetuniquely?

Do I need to create the chart in a different manner?

David
 
Shyam,

I think therein lies my confusion. Yes, I can state that the Graph in
the presentation shows up in the debugger as an embedded object
(type=7). However, I can also state that the datasheet that
represents the data in the graph is linked to an Excel spreadsheet.

Is it possible that the datasheet is not recognised as a separate
object? I'm clutching at straws here. Is there anyway within the
presentation, not the debugger, to identify the type of an object that
has focus. I was using PPTools to identify the objects, but when I
bring up the datasheet the PPTools is removed from the tool bar.

Anyway, thanks for the effort. This one is beyond me.

David
 
I think therein lies my confusion. Yes, I can state that the Graph in
the presentation shows up in the debugger as an embedded object
(type=7). However, I can also state that the datasheet that
represents the data in the graph is linked to an Excel spreadsheet.
Click to expand...

Ah, now I see the confusion.

The MSGraph chart will always be an embedded object, never linked.
It might be possible, through the Graph object model, to get at the link source
of the data in the datasheet; I don't know the answer to that offhand.

You asked if there's a better way of creating the charts instead; I wouldn't
link the data from excel to graph if possible. If you need linked data to
update the charts, do it all in Excel, charts and all.


Is it possible that the datasheet is not recognised as a separate
object? I'm clutching at straws here. Is there anyway within the
presentation, not the debugger, to identify the type of an object that
has focus. I was using PPTools to identify the objects, but when I
bring up the datasheet the PPTools is removed from the tool bar.
Click to expand...

When you activate the chart, you're no longer really working in PowerPoint;
instead you're working in MSGraph (an instance of it hosted within PowerPoint).
The menus change to reflect that, and since PPTools work within PowerPoint,
not MSGraph, it makes sense for them not to be active.
 
David,
I'm jumping in here after much better advice from Shyam and Steve, but
let me amplify Steve's last comment, hopefully graciously (doubt that
sometimes)
The absolutely wrongest way to do things (g) is to link from an Excel
worksheet to a MSGrump datasheet and make the chart in MSGrump. At
least that's my subtle suggestion. (g)

As Ol'(e) Stevie puts it, if the data starts in an Excel worksheet,
make the charts in Excel and paste link those to PPT.

FYI, you can do that this way all the way back to PPT 97, which didn't
have the PasteSpecial + Link method exposed:
1. Create the chart in XL on a chartsheet and copy that sheet to a new
workbook (Edit + Copy Sheet) which would be a single sheet workbook.
Copy and paste that puppy into PPT. The from the PPT menu, anytime,
Edit + Links Update would see that silly chart and update itself back
to the original XL file that it was sourced in and update. (Thanks to
Rob Bovey, Excel MVP for that piece of wizardry).

Brian Reilly, PowerPoint MVP
 
David,
You can use this function to ascertain if an embedded OLE shape is a graph
with links.
' -----------------------------------------------------
Function DoesGraphHaveLink(oShp As Shape) As Boolean
On Error GoTo ErrClearOut
If oShp.Type = msoEmbeddedOLEObject Then
If oShp.OLEFormat.ProgID = "MSGraph.Chart.8" Then
If oShp.OLEFormat.Object.Application.Haslinks Then
DoesGraphHaveLink = True
Exit Function
End If
End If
End If
ErrClearOut:
DoesGraphHaveLink = False
End Function

Sub Test()
Debug.Print DoesGraphHaveLink(ActiveWindow.Selection.ShapeRange(1))
End Sub
' -----------------------------------------------------
 
Nice!

On the theory that the only constant is change, I use something like:

If Instr(oShp.OLEFormat.ProgID,"MSGraph.Chart") > 0 Then
' Whatever

Then there's no worry about what version of MSGraph comes floating by.
Can you see any drawbacks?
 
Steve/Shyam,

Okay, I understand what is happening (basically what I thought all
along). Is this something I should possibly address to Microsoft Tech
Support?

I want to thank you again for the time spent answering this (or at
least trying to). I said that I thought it might be a question
woithout an answer.

David
 
Shyam,

Thanks, now what. I still have to update the MSGraph property. Is
there an MSGraph property to change the link similar to the Powerpoint
one?

David
 
David Kellerman said:
Steve/Shyam,

Okay, I understand what is happening (basically what I thought all
along). Is this something I should possibly address to Microsoft Tech
Support?
Click to expand...

If you have a contract with them, it can't hurt. If you have to pay to play,
then I don't think so. It's all working as designed, really; it's just not
designed to meet your needs. There's not much TS can do about that, I
wouldn't think.
 
Steve,

Thanks for the tip, it works as far as it goes. However I know that
the graph objects has links. What I need to know is if there is
anyway to access that linked property from a VBA executing in
Powerpoint. I have been unable to find it.

I can affect the graph object, but not the underlying datasheet.

David
 
David Kellerman said:
Steve,

Thanks for the tip, it works as far as it goes. However I know that
the graph objects has links. What I need to know is if there is
anyway to access that linked property from a VBA executing in
Powerpoint. I have been unable to find it.

I can affect the graph object, but not the underlying datasheet.
Click to expand...

I've had a look just now and haven't had much luck finding a way to reference
the link source either. Do you need the source or do you need to alter the
numbers in the datasheet, though?
 
Back
Top