• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4818
  • Last Modified:

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
0
alam747
Asked:
alam747
  • 3
  • 3
  • 3
  • +3
1 Solution
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
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
 
nickinthoozCommented:
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
 
Dale FyeCommented:
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
 
GrahamMandenoCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
alam747Author Commented:
shell "excel.exe" & " " & "C:\test\test.xls",vbnormalfocus

I did as above ... getting error file could not find
0
 
alam747Author Commented:
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
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
could it be that your file extension is .XLSX  and not .xls ?
0
 
alam747Author Commented:
Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now