25

I want an Excel spreadsheet that has a file path and name in column A. When a macro is run, let's say the file specified in A1 should be opened on the user's machine. The file could be .doc, .xls, .txt, etc.... rather than my vba needing to know the full path the to application, how could I have the vba tell the machine "please open this file and use your application associated with the extension" ?

I have already found this to work with the full path:

dblShellReturned = Shell("C:\Windows\System32\notepad.exe myfile.txt, vbNormalFocus)

how could I get it to work with something like:

dblShellReturned = Shell("myfile.txt", vbNormalFocus) ' how do I get this to work

Thank you in advance!

Community
  • 1
  • 1
Michael11B12
  • 286
  • 1
  • 3
  • 6
  • 2
    You can use `ShellExcecute`: http://support.microsoft.com/kb/170918 – Tim Williams Sep 20 '13 at 16:20
  • 7
    Use a hyperlink, like `ActiveWorkbook.FollowHyperlink "C:\test\myfile.txt"` – tigeravatar Sep 20 '13 at 17:24
  • This Q&A is for WINDOWS. For an Excel MAC solution, see [How To Open Non-Excel File with Excel Mac VBA](https://stackoverflow.com/questions/50690783/how-to-open-non-excel-file-with-excel-mac-vba) – JMichaelTX Jun 05 '18 at 01:33

5 Answers5

43

This works for me in Excel & Word

Sub runit()
   Dim Shex As Object
   Set Shex = CreateObject("Shell.Application")
   tgtfile = "C:\Nax\dud.txt"
   Shex.Open (tgtfile)
End Sub

or ... as per Expenzor's comment below

CreateObject("Shell.Application").Open("C:\Nax\dud.txt")

Tin Bum
  • 1,397
  • 1
  • 8
  • 16
15

VBA's Shell command wants an exe, so I've been launching the explorer.exe and passing in my file path as an argument. It also seems to work with *.lnk shortcuts and web urls.

Shell "explorer.exe C:\myfile.txt"
Tony Emrud
  • 361
  • 5
  • 4
7

The code below is a template. However you might want to update the default (working) directory to the location of the file.

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                   (ByVal hwnd As Long, ByVal lpszOp As String, _
                    ByVal lpszFile As String, ByVal lpszParams As String, _
                    ByVal LpszDir As String, ByVal FsShowCmd As Long) _

Function StartDoc(DocName As String) As Long
      Dim Scr_hDC As Long
      Scr_hDC = GetDesktopWindow()
      StartDoc = ShellExecute(Scr_hDC, "Open", DocName, _
      "", "C:\", SW_SHOWNORMAL)
 End Function
learnAsWeGo
  • 2,252
  • 2
  • 13
  • 19
AndASM
  • 9,458
  • 1
  • 21
  • 33
  • 1
    Isn't this the same what @TimWilliams suggested in the comments above? :) – Siddharth Rout Sep 20 '13 at 18:12
  • 8
    @SiddharthRout Oh, I don't always pay attention to the comments. I missed that one. I don't get why people put answers there, it seems to break the stack overflow model and mess up searches. – AndASM Sep 20 '13 at 20:34
  • 3
    No worries :) I miss the comment too sometimes. Regarding your question. In my personal opinion, some of us answer in comments because of several reasons. Mine are (not necessarily others)... `1` There is not too much Value Addition by posting an answer as the link says it all and that too for ex: an MSDN link `2` No Point re-inventing the Wheel. We are not here to collect Upvotes but to help people `3` The question has been asked many times. `4` It's a wonderful opportunity for the OP to learn. Hope that answers your question :) – Siddharth Rout Sep 21 '13 at 05:06
  • BTW + 1 For helping OP with an example :) – Siddharth Rout Sep 21 '13 at 05:07
  • 6
    @SiddharthRout I'd rather put something useful in the question as an answer and get it closed, so it's not a dead end, but also not floating around on the unanswered questions list. There are old questions on there that are worth answering, but they get lost in the mess. – AndASM Sep 21 '13 at 19:07
  • It's worth noting that the verb "Open" will not always be the same as running the file e.g. from Explorer. For Excel templates .xltx `ShellExecute` will open the template, while `CreateObject("Shell.Application").Open()` will create a new workbook from the template. – Andre Dec 03 '19 at 15:14
4

I can't comment on existing answers (not enough points), so I'm answering to add information.

Working from Access 2010, I ran into silent failures with the following syntax:

Dim URL As String
URL = "http://foo.com/"
CreateObject("Shell.Application").Open URL

I could get it to work if I wrapped URL in parentheses, but that just seems wrong for subroutine (instead of function) call syntax. I tried swallowing the return value, but that failed with function call syntax, unless I doubled up the parentheses. I realized that the parentheses weren't just syntactic sugar - they had to be doing something, which lead me to believe they might be facilitating implicit casting.

I noticed that Open expects a Variant, not a String. So I tried CVar, which did work. With that in mind, the follwing is my preferred approach since it minimizes the "why are there extraneous parentheses here?" questions.

Dim URL As String
URL = "http://foo.com/"
CreateObject("Shell.Application").Open CVar(URL)

The lesson is that when making OLE Automation calls, be explicit about having Access VBA cast things appropriately!

Toby Ovod-Everett
  • 61
  • 2
  • I found the explanation of the parentheses helpful -- I, too, tried without them, and this is the only answer which explained that they are not optional. – Frogrammer-Analyst Jul 13 '19 at 17:38
2

Shell32.Shell COM object aka Shell.Application can be used that wraps the ShellExecute Win32 API function:

  • Add a reference to Microsoft Shell Controls And Automation type library to VBA project via Tools->References..., then

    Dim a As New Shell32.Shell
Call a.ShellExecute("desktop.ini")

  • Alternatively, without any references:

    Call CreateObject("Shell.Application").ShellExecute("desktop.ini")

Interestingly, here (WinXP), when using a typed variable (that provides autocomplete), ShellExecute is missing from the members list (but works nonetheless).

ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152