asked on

VBA code for a hyperlink to open a file

Hi Exparts,

I have a button while click export a query report to an excel file.
After file has been exported I just want to want to display a message using msgbox
like "Report file location is C:\test\test.xls"
and it will be a hyperlink so the file can open while click on the hyperlink.

Please advise me vba code for that purpose.

Thanks

oleggold

try this:
http://www.vbaexpress.com/forum/archive/index.php/t-536.html

oleggold

or this:
http://www.ehow.com/how_8481140_create-hyperlinks-excel-vba.html

oleggold

also:
http://www.access-programmers.co.uk/forums/showthread.php?t=216986

Rey Obrero (Capricorn1)

better do it this way

If (msgbox("Report file location is C:\test\test.xls" & vbcrlf & "Do you want to open it?",vbyesNo))=vbyes then

shell "excel.exe" & " " & "C:\test\test.xls",vbnormalfocus

end if

nickinthooz

Capricorns code will work, you can also do something like this...

Set objShell = CreateObject("Wscript.Shell")

intMessage = Msgbox("Would you like to apply for access to this resource?", _
    vbYesNo, "Access Denied")

If intMessage = vbYes Then
    objShell.Run("http://www.microsoft.com")
Else
    Wscript.Quit
End If

Open in new window

doesn't show the link but will launch it automatically when you click yes.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Graham Mandeno

Another option is to use FollowHyperlink:

' assuming you have a variable strFilename containing the file 
' into which you have just exported the data:

If MsgBox( "Report has been exported to " & strFilename & vbCrLf _
           &  "Do you want to open it?", vbYesNo, "Export Complete" ) = vbYes Then
    Application.FollowHyperlink strFilename
End If

Open in new window

FollowHyperlink will launch the application associated with the given URL or file - in this case MS Excel.

-- Graham Mandeno [Access MVP 1996-2013]

Rey Obrero (Capricorn1)

i also use the "FollowHyperlink" method, but in most cases you will encounter the problem of getting

a message popup indicating that a potential securoty concern has been identified. Hyperlinks can be harmful....etc.

which you you will not get with the Shell command

alam747

ASKER

shell "excel.exe" & " " & "C:\test\test.xls",vbnormalfocus

I did as above ... getting error file could not find

alam747

ASKER

message are 'c:\test.xls' cannot be found. Check your spelling, or try a different path.

Though I wrote as bellow:
shell "excel.exe" & " " & "C:\test\test.xls",vbnormalfocus

though the file exist in C:\test\test.xls, its looking for the file in C:\

Please advise..

Thanks

Dale Fye

I thought that with Shell, you had to define the entire path for the executable file?

shell "C:\Program Files\Microsoft Office\Office12excel.exe", strFileName

Rey Obrero (Capricorn1)

could it be that your file extension is .XLSX and not .xls ?

alam747

ASKER

Thanks