Consulting

Results 1 to 11 of 11

Thread: Open hyperlink from VBA code

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Question Open hyperlink from VBA code

    Hi,
    Using the following code, I can assign a hyperlink to a cell, and then use the "follow" method to open that file.

    MyRange = Target.Address
        Worksheets(1).Hyperlinks.Add Range(MyRange), MyPth 'Full path to file
        On Error Resume Next
        Target.Hyperlinks(1).Follow
    I want to accomplish this without using a spreadsheet object. ie, use an object within the code itself eg

    Dim MyObj as Hyperlink
    Set MyObj = MyPth 'Full path to file
    MyObj.follow
    Any suggestions?
    MD

  2. #2
    VBAX Regular
    Joined
    Jul 2004
    Posts
    15
    Location
    Hmm, looks like a hyperlink always needs a parent, be it a range, shape, chart or worksheet. Doesn't look like you can instantiate a New Hyperlink out of thin air (unless maybe using a class module?). So I'd guess you'd have to create one using the .Add method, which implies a parent. But I would think that you could delete it after you'd added it and followed it. [I'll be pleased as punch if someone proves me wrong and shows us how to do it with no parent needed.]
    Greg

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Greg,
    Its the fact that you can "Dim MyObj as Hyperlink(s)" that makes me think there must be some way, or how else is this object used?
    MD

  4. #4
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Try this


    Dim RetCode as Integer
    RetCode = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE _
    http://www.vbaexpress.com", vbNormalFocus)
    If RetCode = 0 Then
       MsgBox "Could not run VBAX"
    End if
    "All that's necessary for evil to triumph is for good men to do nothing."

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Posts
    15
    Location
    Looks like James has an idea that might solve your problem.

    As for being able to Dim something as whatever; think of Range objects. I can Dim rngOne as Range, but when it comes time to Set rngOne = Range(xxxx), it still needs a parent. I can't just create a new range object with no parent.
    Greg

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi James,
    I'm really looking for it to open files on my own PC, for the project listed here.
    http://www.vbaexpress.com/forum/show...=newpost&t=474
    MD

  7. #7
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    MD,
    You can use the same code, just modify the arguement. If your file extension is registered with the OS to an application, use:


    Dim RetCode As Integer 
    RetCode = Shell("C:\MyFiles\MyXLFile.XLS", vbNormalFocus) 
    If RetCode = 0 Then 
        MsgBox "Could not run XL" 
    End If

    If the file is not associated with an application, you will need to supply the path to the app's exe and the file name (if your app recognizes the filename as a valid arguement to open the file, of course):


    Dim RetCode As Integer 
    RetCode = Shell("C:\Program Files\MyApplication\MyApp.EXE _ 
    MyAppFileName.XYZ", vbNormalFocus) 
    If RetCode = 0 Then 
        MsgBox "Could not run MyApplication" 
    End If

    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi James,

    The Shell command was the first method I tried. Your first code gives me a Runtime Error 5, The second needs the programme path, which I was trying to avoid. I have a working hyperlink solution, it's just doesn't appear very "elegant", and I was hoping to improve it.
    MD

  9. #9
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    MD,
    A little more research yielded this code sample. ShellExececute will allow you to use only a filename. I wasn't aware that the regular Shell command did not support file names by themselves.

    You can find the details for this WIndows API in http://support.microsoft.com/?id=238245



    'Place these function declarations in the General Declaration sections of your module or userform

    Declare Function ShellExecute Lib "shell32.dll" Alias _
       "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _
       As String, ByVal lpFile As String, ByVal lpParameters _
       As String, ByVal lpDirectory As String, ByVal nShowCmd _
       As Long) As Long
    
    Declare Function apiFindWindow Lib "User32" Alias "FindWindowA" _
       (ByVal lpclassname As Any, ByVal lpCaption As Any) As Long
    
    Global Const SW_SHOWNORMAL = 1

    Here is the procedure to use:

    Dim hwnd
    Dim StartDoc
    hwnd = apiFindWindow("OPUSAPP", "0")
    StartDoc = ShellExecute(hwnd, "open", "C:\Myfile\test.xls", "", _
                    "C:\", SW_SHOWNORMAL)

    Let us know how this works for you.

    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  10. #10
    VBAX Regular
    Joined
    May 2004
    Location
    London
    Posts
    8
    Location
    ActiveWorkbook.FollowHyperlink "http://www.google.com"

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    File List

    Herilane,
    That's exactly what I was after!

    James,
    Thanks for your efforts.
    Slightly mixed results.
    1. The associated programmes opened, but focus returned to Excel.
    2. Another Excel workbook is not opened by the command, and the programme tends to hang.
    I've posted this at http://f2.pg.briefcase.yahoo.com/mdmackillop FileList2.xls if you're interested. The working version will be put up for the KB shortly.

    Many thanks
    MD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •