Linked or Non Linked Automation from PPT to XLS

[lanassist]

Came across a situation yesterday which seemed strange.

25 Excel Statistics Graphs created through VBA Macros in Excel are to be imported into a presentation PPT.

Creating the Graphs OK
Connecting to Excel and importing the Graphs as OLE linked objects all went well.

I then tried importing the Graphs as Static UnLinked OLE objects. Link:=True/False

This works but here for each import there was a warning about the Macros in the the Xl Workbook, whereas while running the Automation through linking there were none.
As stated all was smoothly done.

Does anybody know what can be done about this, apart from making use of signed/trusted VBA Projects ?

Why is there a difference?

PS : Does anybody know how one can UnLink Linked OLE Objects through VBA ?
[It is easy to do it manually in PTT]
Note :
Not using Access to Menubar Control IDs etc.
And Not removing the object and importing it again.
Thanks

 

[firefytr]

For your first question, without setting the security to low, a digitally signed project is the only way to go. As for sometimes there not being warnings via automation sometimes depends on how the procedure is used, but for the most part, opening a workbook via VBA will not give you a macro warning.

As far as unlinking OLE objects, I'm assuming this is powerpoint. Have you tried recording a macro for the action and expanding on it?

-----------
Regards,
Zack Barresse

 

[lanassist]

Hello,

> ... depends on how the procedure is used, ...

?? It is strange that this is not documented in the help.
You know of no way to circumvent this using a more sophisicated method ?
What is the reason for this difference ?
I would have thought that OLE Linking is more demanding and a OLE unlinked object.

>Have you tried recording a macro for the action and expanding on it?

Of course.
Records absolutely nothing which is why I asked

 

[firefytr]

PPT: I'm not too proficent with the PPT Object Model, I do Excel. If you could upload a sample file to the net and paste a link it would help.

As for the security message, that is just how it is. Will MSFT ever change it to a more *logical* way? I doubt it.

-----------
Regards,
Zack Barresse

 

[lanassist]

Hi Zack,
Please excuse the delay and thanks for the proposal. Was away today and only just returned
You should find that this runs without a hitch on W2K Office 2K configs.
In this example it presumes an empty presentation with an empty first slide Title format.
I have not as yet tried it on a 2003 setup but I expect there will be a few differences. Eg: Probably will receive the alert from Excel even using the link method. If you have a chance to test it let me know.
As mentioned before, using the linked method I obtain no warning methods and can import all of the statistical graphs from Excel on the fly.
In fact with a couple of user functions one can set up the Title and Sub Title with the data from the Excel Charts.
Best wishes

__________________________________________________________________
'Defs etc …
'NB : LinkVal as Boolean
'Use Dim and New rather than the older Test Get Create Object method
Set xlApp = New Excel.Application
' Open the Excel workbook
Set xlWrkBook = xlApp.Workbooks.Open(pth)
'loop through Charts only
For Each cht In xlWrkBook.Charts
'do something then
cht.Select
'make PPT Chart
With ActiveWindow
.Selection.SlideRange.Shapes.AddOLEObject(Left:=120#, Top:=110#, Width:=480#, Height:=320#, _
FileName:=pth, Link:=CInt(LinkVal)).Select
' DoEvents
With .Selection.ShapeRange
.Left = 120#
.Top = 120.5
.Width = 480#
.Height = 298.875
.ZOrder msoSendToBack
End With

'unselect and add new slide
.Selection.Unselect
i = i + 1
'BreakPoint
If i > xlWrkBook.Charts.Count Then Exit For
.View.GotoSlide Index:=ActivePresentation.Slides.Add(Index:=i, Layout:=ppLayoutTitle).SlideIndex
End With
Next
' Close the open workbook without saving changes
xlWrkBook.Close (False)
xlApp.Quit
Set xlWrkBook = Nothing
Set xlApp = Nothing