PDA

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



Erdin? E. Ka
11-01-2006, 01:32 PM
Hi everone,:hi:

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
11-01-2006, 02:15 PM
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.


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 (http://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

Erdin? E. Ka
11-01-2006, 02:53 PM
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
11-01-2006, 02:53 PM
Based on some code I got from Ivan

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

mdmackillop
11-01-2006, 03:23 PM
Also seems to work with shortcuts

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

Erdin? E. Ka
11-01-2006, 04:36 PM
Also seems to work with shortcuts

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



Hi mdmackillop, :hi:

Thanks a lot. This is perfect. :yes

malik641
11-01-2006, 04:48 PM
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:

Sub OpenUsingFollowHyperlinkMethod()
ActiveWorkbook.FollowHyperlink Address:="C:\TestFolder\Text.txt.lnk", NewWindow:=True
End Sub
Thanks Malcolm for the ".lnk" piece of the filename :)

Ken Puls
11-01-2006, 11:52 PM
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:

Sub OpenUsingFollowHyperlinkMethod()
ActiveWorkbook.FollowHyperlink Address:="C:\TestFolder\Text.txt.lnk", NewWindow:=True
End Sub 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
11-02-2006, 05:40 AM
This has been known to throw security warnings on some people's machines though. Very irritating. ;)
:think: 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 :thumb

Ken Puls
11-02-2006, 09:33 AM
:think: 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
11-02-2006, 09:42 AM
Hmm....So should I forget about my KB entry?

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

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
11-02-2006, 10:54 AM
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.