launch any file type from excel macro

A

arron laing

I have a macro that lists all the files relating to a project in
single worksheet. As the file list and the directory tree is ver
large, I am trying to write a macro that will launch the selected fil
from the list.

I have managed to work out how to launch .xls and .doc files
using the following code by taking 'file' from the active cell


' file is the full path

If Right(file, 3) = "xls" Then
Workbooks.Open file
End If

If Right(file, 3) = "doc" Then
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open file
.Visible = True
End With
End If

but this seems a bit cumbersome as there are CAD files, MS projec
files, JPGs, Acrobat files etc.

Is there a universal command that will launch any file type or som
other clever work around.

Cheers

Arro
 
T

Tom Ogilvy

Use the hyperlink worksheet function to build a hyperlink


or if you want code, just use the shell command with fully qualified file
name.

As long as the extension is mapped to an application, it should work the
same as double clicking on the file in explorer.
 
A

arron laing

I would prefer to use the code option but I have tried the help on SHEL
and I can't seem to make it work unless the file is a .exe.

I assumed the comand would be -

SHELL(file,1)

where file is the full path and filename (ie k:\project\...\test.dwg

What am I doing wrong?

Cheers

Arro
 
T

Tom Ogilvy

Add START in the command line:

Shell ("Start C:\Bridge Scorer\Manual.doc")

but John Walkenbach previously posted:

The DOS Start command does not work with all versions of Windows. Try using
the ShellExecute API function.

Put this at the top of your module:

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

Then use a procedure like this:

Sub OpenWordDoc()
WordDoc = "C:\Bridge Scorer\Manual.doc"
ShellExecute 0&, vbNullString, WordDoc, vbNullString, vbNullString,
vbNormalFocus
End Sub

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss
 
A

arron laing

Thanks Tom

I found trhat previous post late on last night and managed to get i
working.

Cheers

Arro
 
Top