Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA code for a hyperlink to open a file

Posted on 2013-01-31
13
Medium Priority
?
4,616 Views
Last Modified: 2013-03-04
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
0
Comment
Question by:alam747
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 38842182
0
 
LVL 21

Expert Comment

by:oleggold
ID: 38842184
0
 
LVL 21

Expert Comment

by:oleggold
ID: 38842186
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38842188
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
0
 
LVL 6

Expert Comment

by:nickinthooz
ID: 38842198
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.
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 38842256
My preferred method is to use the

Application.FollowHyperlink "FileName"

method.  With this method, you don't need to know the source application or where it is located to open the file, it will automatically be opened with the application that is the default for the appropriate file type.

I generally use a message box, display the file name, and use the vbYesNo option, asking if they want to open the file now.  If the response is vbYes, then I use the method described above.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 38842288
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]
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38842340
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
0
 

Author Comment

by:alam747
ID: 38842505
shell "excel.exe" & " " & "C:\test\test.xls",vbnormalfocus

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

Author Comment

by:alam747
ID: 38842524
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
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38843285
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38843627
could it be that your file extension is .XLSX  and not .xls ?
0
 

Author Closing Comment

by:alam747
ID: 38853696
Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question