Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

run macro by hyperlink

0

hello  

I  try  opening   specific  page  in  file  pdf     by  hyperlink  in  specific  cell 

should  run  the  macro  and  put  the  page  number  inputbox  then  open  at  this  page   

so  i  appreciate  if  any  body  guide  me 


Sub openPDFPage()
Dim myLink As String
Dim myPage As Long
Dim objIE As New InternetExplorer

myLink = "C:\Users\alhagag\Desktop\files\1.pdf"

myPage = InputBox("Enter the page number")

With objIE
.Navigate myLink & "#page=" & myPage
.Visible = True
End With
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If Target.Parent.Address = "$c$9" Then

Call openPDFPage

End If

End Sub

Answer
Discuss

Answers

0
Selected Answer

Leopard

In the modified file attached, I've used the Adobe Reader to open a file at a given page, rather than IE.

Module1 has this code but you may need to change the executable location and will need to change the file string (which follow the bits in bold):

Sub PageInPDF()

Dim File As String, Reader As String, Pg As String

Reader = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"

    File = "E:\Documents\ATEN cs682 KVM Switch manual_s-2014-10-31.pdf"
    'File = ThisWorkbook.Sheets(1).Range("C9").Value ' use this if cell has file path but NO hyperlink
    Pg = "page=" & InputBox("Enter the page number")

    r = Shell(Reader & " /a " & Chr(34) & Pg & Chr(34) & ", " & Chr(34) & File & Chr(34), 1)

End Sub
Note that you'll need to run this from the Module for now but you could call it from an event macro Private Sub Worksheet_ChangeSelection... say (rather than Worksheet_FollowHyperlink)

The command line syntax (following the Shell command) is demanding so be sure to leave in the Chr(34) references- these add an inverted comma so you get text like "page=3" which is needed.

You'll see above that I have a line commented out (starting 'File = ...) where I had problems opening the hyperlinks from the worksheet- I think this is related to the hyperlink replacing spaces with %20 (like URLs) but my file name included spaces. If you're happy to paste only the file path and full name into the cell as text (e.g. 'E:\Documents\... in my case) but NOT a clickable hyperlink (like cell C5), then that line works and the file opens at the desired page.  

Hope this is some help to you.

Discuss

Discussion

John
thanks  but  it  gives  me  error   file  not found in  this  line 
r = Shell(Reader & " /a " & Chr(34) & Pg & Chr(34) & ", " & Chr(34) & File & Chr(34), 1)

it's  strange   as  in  my  file in C9   with  hyperlink   the  file  opens  normally 
and  if  it is  possible   run  the  macro by  hyperlink  it  will be  great 
leopard (rep: 88) Apr 15, '21 at 5:23 pm
Leopard
That error can happen if the executable (Adobe) Reader file doesn't exist or if the pdf doesn't exist.

Did you try the macro with the pdf path written in the Code and with the TEXT of the path in cell C9? 
John_Ru (rep: 6092) Apr 16, '21 at 1:52 am
John
actually   I did  it    the  same  error  shows   and I  use  copy  and  paste  about  the  file  as  path  to  avoid  the  mistake   when writing and    I  checked the  adobe  reader  it's  really existed 
leopard (rep: 88) Apr 16, '21 at 12:58 pm
Leopard.
I don't know what's happening, sorry. Both techniques work for me. I assume you have the Shell line exactly as in my file (I found that any missing character will produce a problem). 
John_Ru (rep: 6092) Apr 16, '21 at 2:15 pm
John
it  seems  the  problem  from  my  pc   I tested  with  another PC  it  works  but  the  problem  is  why  when  i  press the  hyperlink in  cell 9   it  doesn't  work  the  macro  it  open  the  file  directly
leopard (rep: 88) Apr 17, '21 at 5:06 am
Leopard.

As far as I know, you can NOT prevent Excel from following a hyperlink in a cell (that's why I suggested you put only the TEXT path into the cells instead- which right click/ Remove Hyperlink can do for you).

Even if you add this event macro to your worksheet...
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Call PageInPDF
End Sub
... and click on the cell including a hyperlink, then Excel will first follow the hyperlink (opening at page 1) and after you close that pdf, will open the pdf again but giving you the choice of picking the page to open at (via the macro).

Seems to me you have to chosse between 1) putting TEXT links in cells then opening the pdf at a chosen page (using the macro) OR 2) putting hyperlinks in cells (as normal) and letting Excel open them at page1).

I can't help you further, sorry.

John
John_Ru (rep: 6092) Apr 17, '21 at 10:19 am
Also, did you try the solution from Variatus on another PC? If I make the change he suggested, the macro works but opens on page 1 for me.
John_Ru (rep: 6092) Apr 17, '21 at 10:33 am
John
thanks   I will satisfy  your  answering  it doesn't  seem  do  that  directly  by hyperlink 
leopard (rep: 88) Apr 18, '21 at 7:15 am
Thanks Leopard. This was a tricky challenge! 
John_Ru (rep: 6092) Apr 18, '21 at 9:44 am
Add to Discussion
0

Excel executes the hyperlink before the FollowHyperlink event occurs. Therefore that event isn't suitable for your purpose. In the attached workbook I made a simple change. In cell C9 I removed the hyperlink and kept the address. I coloured the text red but you can give it any colour you want, including blue so that the user will think he cllicks a hyperlink.

Then I attached a SelectionChange event to the cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '223

    Const myLink    As String = "C:\Users\alhagag\Desktop\files\1.pdf"
    Dim objIE       As Object
    Dim myPage      As Variant

    If Target.Address(0, 0) = "C9" Then
        Set objIE = CreateObject("InternetExplorer.Application")
        myPage = InputBox("Enter the page number")
        If Val(myPage) < 1 Then Exit Sub    ' user pressed Cancel

        With objIE
            .Navigate myLink & "#page=" & myPage
            .Visible = True
        End With
    End If
End Sub

As you will see, I combined your original procedure into the event procedure and I changed the IE object to late binding (meaning you don't need to load the DLL for it beforehand). This part of the code isn't tested but I did test it the other day. Therefore I think it will work for you once the file in MyLink exists.

The difference you need to bear in mind is that you can't click and hold C9 to gain access to it as you would do with a hyperlink. The SelectionChange event will occur as soon as you click. To edit the cell you need to temporarily disable application events. You can do that by typing in the Immediate pane: Application.EnableEvents = False. Turn them back on in the same way.

Discuss

Discussion

@variatus
thanks  for  your  interesting    about  your suggestion "specified the Internet Explorer."  I did  it and  call it from an event macro Private Sub Worksheet_ChangeSelection  but  nothing  happens  and  no  error ,about  you  ask  me   I don't state what you found wrong with my code actually   I  mentioned about  the  error  and  I  said  " file not found"   see  discuss with  Mr.John  and  my  original  code   there  is  no  error ,and  when   I  put  my  orginal  code  in  Worksheet_ChangeSelection then  the  error show in this  line
If Target.Parent.Address = "$c$9" Then
object  doesn't  support  this  property  or  method
leopard (rep: 88) Apr 16, '21 at 8:03 am
Hi Leopard, I don't normally read the Discussions. Try to make sure that all of your question is in the Question. It can be amended if you forgot something, as you know. The error message is correct. Target.Parent is the workbook in which Target is located. The workbook doesn't have an Address. In the above context, what you mean is Target.Address.
Variatus (rep: 4889) Apr 16, '21 at 8:02 pm
variatus 
you're  right   about  my  explenation 
the  question  is   , is  it  possible run  the  macro by  press hyperlink  or  not ?
because   now  after  tested with  another PC   it  works  but  open  the  file  directly  without run  the  macro , it  should  show  inputbox  to write  the  page number . 
leopard (rep: 88) Apr 17, '21 at 5:10 am
variatus 
this  the  same  what  John suggests for  me    
concolusion :  it  doesn't  seems  to  be  possible  to  run  the  macro  directly  by  hyperlinkfollow  event   the  code  gives  me  message   " waiting to  complete  procedure  internet explorer OLE   and   gives me three  choices  ok,exchange to ,cancel
  
leopard (rep: 88) Apr 18, '21 at 6:52 am
No. It's not possible to use the FollowHyperlink event in this context. And no, my suggestion isn't the same as John's. Not clear how you get the message "Waiting to complete Internet Explorer OLE". It's not from the code but from Internet Explorer. I didn't get such a message when I tested the code a few days ago with a file of my own. Try another PDF file. Since you only quote half the message I can't form an opinion about it.
Variatus (rep: 4889) Apr 18, '21 at 7:26 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login