Open embedded PDF and bring that file to front

[bisel]

Greetings all,

I have an Excel workbook with an embedded PDF.

I have created a macro that will open that PDF (see below).

Sub getstarted()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheet5.OLEObjects("getting_started").Verb xlVerbOpen ' Open the PDF file embedded in Sheet5
    Application.DisplayAlerts = True
    Sheet16.Activate ' Force return to Sheet16
  
End Sub

The problem I am having is that the macro is initiated from a single worksheet in the Excel workbook, but the embedded file is on a different worksheet which is normally hidden. Sometimes the PDF opens and the file is brought to the foreground as I would like. Sometimes, the macro opens the PDF but the file is the background.

I would like to force it to the foreground if I can.

Appreciate any help.

Regards,

Steve

 

[Domenic]

I believe that this is what's happening.

While your embedded PDF file is opened, you're actually getting the following error...

Run-time error '1004': Cannot start the source application for this object.

However, since you have On Error Resume Next, it's hiding it. And so sometimes your PDF file gets focus and other times not. Try replacing the Verb argument xlVerbOpen with xlVerbPrimary instead...

Sheet5.OLEObjects("getting_started").Verb xlVerbPrimary

That should avoid the error in the first place. Then you can handle errors this way . . .

On Error Resume Next
Sheet5.OLEObjects("getting_started").Verb xlVerbPrimary
If Err <> 0 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
    Exit Sub
End If
On Error GoTo 0

Does this help?

 

[bisel]

I believe that this is what's happening.

While your embedded PDF file is opened, you're actually getting the following error...

Run-time error '1004': Cannot start the source application for this object.

However, since you have On Error Resume Next, it's hiding it. And so sometimes your PDF file gets focus and other times not. Try replacing the Verb argument xlVerbOpen with xlVerbPrimary instead...

Sheet5.OLEObjects("getting_started").Verb xlVerbPrimary

That should avoid the error in the first place. Then you can handle errors this way . . .

On Error Resume Next
Sheet5.OLEObjects("getting_started").Verb xlVerbPrimary
If Err <> 0 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
    Exit Sub
End If
On Error GoTo 0

Does this help?

Hi,

When I changed from ...
Sheet5.OLEObjects("getting_started").Verb xlVerbOpen
to ...
Sheet5.OLEObjects("getting_started").Verb xlVerbPrimary

That did not make any difference. I had as my last line ... to activate sheet16. I took that out and still no difference.

Regards,

Steve

 

[Domenic]

In that case, try using the AppActivate statement. You'll need to specify the title in the title bar of the application you want to activate. So, for example, this is the title bar of my application...

Accordingly, the AppActivate statement would be as follows . . .

AppActivate "Adobe Acrobat Reader DC (64-bit)"

Hope this helps!

 

[bisel]

In that case, try using the AppActivate statement. You'll need to specify the title in the title bar of the application you want to activate. So, for example, this is the title bar of my application...

View attachment 66485

Accordingly, the AppActivate statement would be as follows . . .

AppActivate "Adobe Acrobat Reader DC (64-bit)"

Hope this helps!

I thought AppActivate activates an application. But I cannot determine from your suggestion how to open a specific embedded file.

Regards,

Steve

 

[Domenic]

Double-click the embedded file so that it opens up. Then, look at the title bar to determine the title to use.

In my previous post, you're seeing the window that appeared after double-clicking the embedded file. And, as you can see, I used the "Adobe Acrobat Reader DC (64-bit)" part of the title, which is the name of the application. You don't need to use the full title, which includes the document name, unless you have multiple documents opened in separate windows and you want to activate a specific one.

Does this help?

 

[bisel]

I have tried suggestions, but to no avail. Sometimes it brings Acrobat to the front, but generally it does not.

Steve

 

[Dan_W]

I wonder if perhaps it is worth making VBA pause for a second or two before executing the AppActivate command. The the code at the bottom of this reply is one way of getting VBA to pause, and you can call it with:
PAUSE 2
to make it pause for approx 2 seconds, for example. But that said, the approach suggested by @Domenic is working fine for me - could you perhaps post your updated code please?

Sub PAUSE(ByVal Period As Single)
    
    Dim CurrentTime As Single
    CurrentTime = Timer
    
    Do
        DoEvents
    Loop While CurrentTime + Period > Timer

End Sub

 

[bisel]

I wonder if perhaps it is worth making VBA pause for a second or two before executing the AppActivate command. The the code at the bottom of this reply is one way of getting VBA to pause, and you can call it with:
PAUSE 2
to make it pause for approx 2 seconds, for example. But that said, the approach suggested by @Domenic is working fine for me - could you perhaps post your updated code please?

Sub PAUSE(ByVal Period As Single)
   
    Dim CurrentTime As Single
    CurrentTime = Timer
   
    Do
        DoEvents
    Loop While CurrentTime + Period > Timer

End Sub

Gave that a try. And same as before. Sometimes Acrobat opens and comes to the front and sometimes it does not.

It is not that big of a deal, so decided not to worry too much about it.

Steve

 

[Dan_W]

Is it possible to post the revised code that you're currently using?