Solved

VBA code for a hyperlink to open a file

Posted on 2013-01-31
13
3,881 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 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
 
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 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

772 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