Solved

VBA code for a hyperlink to open a file

Posted on 2013-01-31
13
3,816 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
 
LVL 119

Expert Comment

by:Rey Obrero
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 119

Expert Comment

by:Rey Obrero
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access query with left expression 9 31
Open CSV, modify and save as xls from Access 12 19
Sub Reports 8 23
Access Excel export not behaving 2 26
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now