PDA

View Full Version : Solved: Open hyperlink from VBA code



mdmackillop
07-21-2004, 12:09 PM
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

Greg T
07-21-2004, 12:25 PM
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.]

mdmackillop
07-21-2004, 12:31 PM
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

jamescol
07-21-2004, 12:31 PM
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

Greg T
07-21-2004, 12:38 PM
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.

mdmackillop
07-21-2004, 12:43 PM
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/showthread.php?goto=newpost&t=474
MD

jamescol
07-21-2004, 12:50 PM
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

mdmackillop
07-21-2004, 01:08 PM
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

jamescol
07-21-2004, 02:57 PM
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

herilane
07-21-2004, 03:01 PM
ActiveWorkbook.FollowHyperlink "http://www.google.com"

mdmackillop
07-21-2004, 03:35 PM
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