Macro to open external file

hello,

you can run any .exe file using the shell command

Sub ShellOut()
Dim X
X = Shell("NOTEPAD.EXE", vbNormalFocus)
X = Shell("NOTEPAD.EXE " & "C:\Test.txt", vbNormalFocus)
X = Shell("CALC.EXE", vbNormalFocus)
End Sub

There is also great code around the web for SHELL AND WAIT this is great when you want to open a program that will automataclly process some information (resize images for example) and the VBA code will wait on that line for the executing handle to finish its job before continuning.

hope this helps
cheers

duplicate post

Hi,

I rescently had a similar problem solved for me in this forumn, all the files I wished to open with the button were saved in a folder on my desktop 'ProcesSpecifications' this folder contains loads of other folders containing .doc/.pdf/.xls etc etc. please see code below:

Private Sub CommandButton1_Click()
'Process Specifications
Dim varFNamePath As Variant
Dim colFoundFiles As New Collection
Dim strFilter As String
Dim strPath As String
Dim myRow As Long

strPath = "C:\Documents and Settings\user\Desktop\ProcessSpecifications"

strFilter = "*" & Sheets("Sheet1").Range("E17").Value & "*.*"

Call FileSearch(colFoundFiles, strPath, strFilter, True)

If colFoundFiles.Count = 0 Then
    MsgBox "No file was found!"
    Exit Sub
End If

' Opens first file found

'ActiveWorkbook.FollowHyperlink Address:=colFoundFiles(1)
'Application.CommandBars("Web").Visible = False

' Opens all files found

'For Each varFNamePath In colFoundFiles
'    ActiveWorkbook.FollowHyperlink Address:=varFNamePath
'    Application.CommandBars("Web").Visible = False
'Next varFNamePath

' Creates hyperlinks to all files found in Column A

myRow = 1
Range("A:A").Clear
For Each varFNamePath In colFoundFiles
    Sheets("Sheet1").Hyperlinks.Add Anchor:=Range("A" & myRow), Address:=varFNamePath _
        , TextToDisplay:=varFNamePath
    myRow = myRow + 1
Next varFNamePath

End Sub


Private Sub FileSearch(colFoundFiles As Collection, strPath As String, _
    strMask As String, blnIncSubDir As Boolean)

Dim strDirFile As String
Dim varColItem As Variant
Dim colSubDir As New Collection

strPath = Trim(strPath)
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

strDirFile = Dir(strPath & strMask)
Do While strDirFile <> ""
    colFoundFiles.Add strPath & strDirFile
    strDirFile = Dir
Loop

If Not blnIncSubDir Then Exit Sub

strDirFile = Dir(strPath & "*", vbDirectory)
Do While strDirFile <> ""
    If strDirFile <> "." And strDirFile <> ".." Then If ((GetAttr(strPath & strDirFile) And _
        vbDirectory) = 16) Then colSubDir.Add strPath & strDirFile
    strDirFile = Dir
Loop

For Each varColItem In colSubDir
     Call FileSearch(colFoundFiles, CStr(varColItem), strMask, blnIncSubDir)
Next

End Sub

You would have to change the line that appertains to where you want to search

strPath = "C:\Documents and Settings\user\Desktop\ProcessSpecifications"

you will also see the below reference:-

strFilter = "*" & Sheets("Sheet1").Range("E17").Value & "*.*"

the ("E17") referes to the cell number in which I type the file name or part file name of the file I wish to search for befor clicking the button to search, works brilliantly for me. I'm not a VBA enabled person and if there is any problems with the coding for you I will probably not be able to help sort. As I said, works perfectly for me,

Good Luck,

Andy.

Hi,
I think solution suggested by D_Rennie is exactly what you want. Why do you think it doesn't fit your needs?

though if i understand you correctly you want to give a file name say test.txt though the file can be located on diffrent dirves. well if the file is located say at ?:\Folder\Test.txt you will need to use that Dir function , ive couppled with follow hyperlink,

Sub DirFile()
Dim X As Long
Dim DirArray As Variant
Dim FolderFile As String, FoundLocation As String
DirArray = Array("A", "C", "D", "F")
FolderFile = ":\Folder\Test.txt"
For X = LBound(DirArray) To UBound(DirArray)
On Error Resume Next
    FoundLocation = Dir(DirArray(X) & FolderFile)
    If FoundLocation <> vbNullString Then
        ActiveWorkbook.FollowHyperlink DirArray(X) & FolderFile
        GoTo EndLoop
    End If
Next X
EndLoop:
On Error GoTo 0
End Sub

The code abouve could mostlikley be compressed somewhat though it gives you the idear. If this is not what you are looking for you will need to explain again as im a little slow,

cheers