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.

Code:

---

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

Code:

---

Dim MyObj as Hyperlink
Set MyObj = MyPth 'Full path to file
MyObj.follow

---

Any suggestions?
MD

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.]

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

Try this

Code:

---

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

---

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.

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

MD,
You can use the same code, just modify the arguement. If your file extension is registered with the OS to an application, use:

Code:

---

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):

Code:

---

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

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

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

Code:

---

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:

Code:

---

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

Code:

---

ActiveWorkbook.FollowHyperlink "http://www.google.com"

---

Herilane,
That's exactly what I was after!:dance:

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