?
Solved

VBA code for a hyperlink to open a file

Posted on 2013-01-31
13
Medium Priority
?
4,341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 48

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 48

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 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