Replies

Hi Hans,

Two questions spring to mind:

1. The online help that I am used to does not have an answer wizard. Can you point me to the one that you are looking at?
2. What screenshot?

Hi Hans,

Two questions spring to mind:

1. The online help that I am used to does not have an answer wizard. Can you point me to the one that you are looking at?
2. What screenshot?

That one is really neat.

The help suggests that array constants can only be used in array formulas when it says:

“In an ordinary formula, you can enter a reference to a cell containing a value, or the value itself, also called a constant. Similarly, in an array formula you can enter a reference to an array, or enter the array of values contained within the cells, also called an array constant. Array formulas accept constants in the same way that nonarray formulas do, but you must enter the array constants in a certain format.”

So I’m a bit surprised to find it working in a non-array formula???

That one is really neat.

The help suggests that array constants can only be used in array formulas when it says:

“In an ordinary formula, you can enter a reference to a cell containing a value, or the value itself, also called a constant. Similarly, in an array formula you can enter a reference to an array, or enter the array of values contained within the cells, also called an array constant. Array formulas accept constants in the same way that nonarray formulas do, but you must enter the array constants in a certain format.”

So I’m a bit surprised to find it working in a non-array formula???

Oh don’t say that . It was a work of art.

I was just trying to put the icing on the cake!

The code that you posted does the job excellently.

Oh don’t say that . It was a work of art.

I was just trying to put the icing on the cake!

The code that you posted does the job excellently.

Application.ScreenUpdating doesn’t seem to work.

As I say, normally with OLE, one sets the target application’s Visible property to false.

Application.ScreenUpdating doesn’t seem to work.

As I say, normally with OLE, one sets the target application’s Visible property to false.

Well after posting my question about the variable i I realised that you used it for obtaining unique names! Just me being slow.

Anyway, since each of the presentations already has a name, I altered your code slightly to use the existing name. I also removed the selection of A1. Was there a special reason for this?

Now, unlike you and Andrew, when I run your code, I don’t appear to have an instance of PowerPoint still running (at least I cannot see one in the Task Manager).

One more question springs to mind. Is there anyway to have this running invisibly? Normally when one uses OLE, one can set the visible property of the application to False. But I can’t seem to get this to work anywhere in your snippet???

Here is what I have ended up with:

Sub ExportPPT()
    Const strFile = "C:Test"

    Dim wsh As Worksheet
    Dim obj As OLEObject
    Dim ppt As PowerPoint.Application

    Set wsh = ActiveSheet
    For Each obj In wsh.OLEObjects
      If Left(obj.progID, 10) = "PowerPoint" And obj.OLEType = xlOLEEmbed Then
        obj.Activate
        Set ppt = obj.Object.Application
        ppt.ActivePresentation.SaveAs strFile & ppt.ActivePresentation.Name & ".ppt"
        ppt.Quit
        Set ppt = Nothing
      End If
    Next


    Set obj = Nothing
    Set wsh = Nothing
End Sub

Well after posting my question about the variable i I realised that you used it for obtaining unique names! Just me being slow.

Anyway, since each of the presentations already has a name, I altered your code slightly to use the existing name. I also removed the selection of A1. Was there a special reason for this?

Now, unlike you and Andrew, when I run your code, I don’t appear to have an instance of PowerPoint still running (at least I cannot see one in the Task Manager).

One more question springs to mind. Is there anyway to have this running invisibly? Normally when one uses OLE, one can set the visible property of the application to False. But I can’t seem to get this to work anywhere in your snippet???

Here is what I have ended up with:

Sub ExportPPT()
    Const strFile = "C:Test"

    Dim wsh As Worksheet
    Dim obj As OLEObject
    Dim ppt As PowerPoint.Application

    Set wsh = ActiveSheet
    For Each obj In wsh.OLEObjects
      If Left(obj.progID, 10) = "PowerPoint" And obj.OLEType = xlOLEEmbed Then
        obj.Activate
        Set ppt = obj.Object.Application
        ppt.ActivePresentation.SaveAs strFile & ppt.ActivePresentation.Name & ".ppt"
        ppt.Quit
        Set ppt = Nothing
      End If
    Next


    Set obj = Nothing
    Set wsh = Nothing
End Sub

Thanks Hans, this looks good. I must have been almost there but I missed the ActivePresentation.

As a matter of interest, is there a good reason for using:

For i = 1 To wsh.OLEObjects.Count
Set obj = wsh.OLEObjects(i)

As opposed to

For each obj in wsh.OLEObjects

Thanks Hans, this looks good. I must have been almost there but I missed the ActivePresentation.

As a matter of interest, is there a good reason for using:

For i = 1 To wsh.OLEObjects.Count
Set obj = wsh.OLEObjects(i)

As opposed to

For each obj in wsh.OLEObjects

Well,
thought I ought to update y’all on what I found.

I did an IPCONFIG /ALL and discovered that the offending network adapter was the virtual network adapter set up by the VPN client that I use (AT&T Network Client).
Regarding the timing question, it would appear that the time logged in the even viewer is the time that the event kicks off. but it is not written until the event has completed.

Anyway, as a test, I disabled the particular virtual network adapter in Network Connections before rebooting and then the only thing that held up my reboot was NAV sittting waiting with an exclamation mark on its icon. Wish I knew what caused that!

Thanks for all your help. John Gray’s pointer to the Event Viewer was just what I needed.

Well,
thought I ought to update y’all on what I found.

I did an IPCONFIG /ALL and discovered that the offending network adapter was the virtual network adapter set up by the VPN client that I use (AT&T Network Client).
Regarding the timing question, it would appear that the time logged in the even viewer is the time that the event kicks off. but it is not written until the event has completed.

Anyway, as a test, I disabled the particular virtual network adapter in Network Connections before rebooting and then the only thing that held up my reboot was NAV sittting waiting with an exclamation mark on its icon. Wish I knew what caused that!

Thanks for all your help. John Gray’s pointer to the Event Viewer was just what I needed.

I’m on a home wireless network. I don’t think the company’s network guys will be terribly interested.

Wierd thing is that the 2 minutes between events seems to occur AFTER the autoconfig has happened.