Solved: How To Run A ShortCut Of A File From A VBA Code?

[Erdin? E. Ka]

Hi everone,

How To Run A ShortCut Of A File From A VBA Code?

I tried Shell and ShellExecute but i couldn't solve it.

Directory: C:\
ShortCut File Name = FileTheNameOfTheFile.Pdf

Thanks a lot.

[Ken Puls]

Hi there,

I've found that sometimes I need to run this twice to make it happen. I'm not sure why that is, though. It almost seems like it ignores the first time on occasion, but not always.

[vba]
Private Declare Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

Sub OpenFile()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Use ShellExecute API to launch any file in its
' host application

Dim strFilePath As String

'Set file path
strFilePath = "C:\My Documents\test.pdf"

'Shell out start to owning application
ShellExecute 0, "Open", strFilePath, "", "", 0
End Sub[/vba]

[Erdin? E. Ka]

Hi Ken. Thanks a lot. Actually i was tried the same code before i send my fisrt message. But it doesn't work. I am running the code a lot of times but doing nothing.

This is not an original file. The ShortCut of o an original file.

Is this an important thing for the codes?

[mdmackillop]

Based on some code I got from Ivan
[vba]
Sub RunFile()
Dim MyFile As String, Cmd As String
MyFile = "C:\My Documents\test.pdf"
Cmd = "RunDLL32.EXE shell32.dll,ShellExec_RunDLL "
Shell (Cmd & MyFile)
End Sub

[/vba]

[mdmackillop]

Also seems to work with shortcuts
[vba]
Sub RunFile()
Dim MyFile As String, Cmd As String
MyFile = "C:\AAA\Schedule.doc.lnk"
Cmd = "RunDLL32.EXE shell32.dll,ShellExec_RunDLL "
Shell (Cmd & MyFile)
End Sub

[/vba]

[Erdin? E. Ka]

Also seems to work with shortcuts
[vba]
Sub RunFile()
Dim MyFile As String, Cmd As String
MyFile = "C:\AAA\Schedule.doc.lnk"
Cmd = "RunDLL32.EXE shell32.dll,ShellExec_RunDLL "
Shell (Cmd & MyFile)
End Sub

[/vba]

Hi mdmackillop,

Thanks a lot. This is perfect.

[malik641]

When using excel I find it easiest to open files/folders/websites (even a new e-mail message with outlook) using the FollowHyperlink method of the workbook object:
[VBA]
Sub OpenUsingFollowHyperlinkMethod()
ActiveWorkbook.FollowHyperlink Address:="C:\TestFolder\Text.txt.lnk", NewWindow:=True
End Sub[/VBA]
Thanks Malcolm for the ".lnk" piece of the filename

[Ken Puls]

When using excel I find it easiest to open files/folders/websites (even a new e-mail message with outlook) using the FollowHyperlink method of the workbook object:
[vba]
Sub OpenUsingFollowHyperlinkMethod()
ActiveWorkbook.FollowHyperlink Address:="C:\TestFolder\Text.txt.lnk", NewWindow:=True
End Sub[/vba] Thanks Malcolm for the ".lnk" piece of the filename

This has been known to throw security warnings on some people's machines though. Very irritating.

[malik641]

This has been known to throw security warnings on some people's machines though. Very irritating.

I've seen some...but for the most part (for me) it doesn't happen...is there a way to bypass that?

Thanks for the heads-up Ken

[Ken Puls]

I've seen some...but for the most part (for me) it doesn't happen...is there a way to bypass that?

To be honest, I'm not sure. I had a user complain about it, but I've never encountered it on my PC. I could never figure out what was different about them, not that I invested much time on it. I've seen it said that there is no way to avoid it, but never investigated that further.

I just started working with other methods to avoid the issue all together.

[malik641]

Hmm....So should I forget about my KB entry?

[vba]Sub OpenFileOrFolderOrWebsite()
'Shows how to open files and / or folders and / or websites / or
'create emails using the FollowHyperlink method
Dim strXLSFile As String, strPDFFile As String, strFolder As String, strWebsite As String
Dim strEmail As String, strSubject As String, strEmailHyperlink As String
strFolder = "C:\Test Files\"
strXLSFile = strFolder & "Test1.xls"
strPDFFile = strFolder & "Test.pdf"
strWebsite = "http://www.vbaexpress.com/"
strEmail = "mailto:someone@email.com"
strSubject = "?subject=Test"
strEmailHyperlink = strEmail & strSubject
'**************FEEL FREE TO COMMENT ANY OF THESE TO TEST JUST ONE ITEM*********
'Open Folder
ActiveWorkbook.FollowHyperlink Address:=strFolder, NewWindow:=True
'Open excel workbook
ActiveWorkbook.FollowHyperlink Address:=strXLSFile, NewWindow:=True
'Open PDF file
ActiveWorkbook.FollowHyperlink Address:=strPDFFile, NewWindow:=True
'Open VBAX
ActiveWorkbook.FollowHyperlink Address:=strWebsite, NewWindow:=True
'Create New Email
ActiveWorkbook.FollowHyperlink Address:=strEmailHyperlink, NewWindow:=True
'************************************************************************** ****
End Sub
[/vba]
That's too bad too....this seemed like such an easy method to do all those things above.

...maybe it only happens with one type of hyperlink method (like opening a file...whereas opening a webpage won't show a warning).

[Ken Puls]

No, Joseph, I wouldn't can the entry. I'd just test it completely and maybe put a note in about potential security warnings. I just tried your code, and it doesn't trigger anything for me.

It's a valid alternate method, no question.