How can Excel VBA open file using default application

Question

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!

Proconsulate

You can use ShellExcecute: support.microsoft.com/kb/170918 – Dola

Use a hyperlink, like ActiveWorkbook.FollowHyperlink "C:\test\myfile.txt" – Pernick

This Q&A is for WINDOWS. For an Excel MAC solution, see How To Open Non-Excel File with Excel Mac VBA – Karlee

Answer 1

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

Montymonument

This worked for me in Access too as a way to open attachments! – Birr

Or a one-liner: CreateObject("Shell.Application").Open("C:\Nax\dud.txt") – Prothalamium

Answer 2

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"

Hage

Answer 3

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

Heartworm

Isn't this the same what @TimWilliams suggested in the comments above? :) – Garcon

@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. – Heartworm

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 :) – Garcon

BTW + 1 For helping OP with an example :) – Garcon

@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. – Heartworm

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. – Madera

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

Inane

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. – Sausauce

Answer 5

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

Sordello