Finding where the Excel.exe resides

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
I have completed an Excel Project (with lots of VBA code). In some lines the VBA code references directory paths like C:\myfiles .... , and also c:\program files\sas\sas 9.1\sas.exe. I need to move the project to another drive and directory. Many people will be able to use this project once its on the new drive. I need a way to figure out where the SAS executable resides on the network, just in case it it not in the same place that it was when I created it on my hard drive.

Then once I find the .exe files, I can put this into a variable name and use the variable name for my shell calls. For example:

Call Shell("c:\program files\sas\sas 9.1\sas.exe -splash -icon -sysin 'c:\myfiles\goodlist_to_sas.sas'", vbMaximizedFocus)


This directory path to SAS, may not be the same on someone else's computer that is why I am thinking of doing this.

Thanks for any help here.
Nancy :confused:
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
This will document the users track to the files path using a Dialog, it only gets the Path and does not open or get the file!

Note: Run it to see how it works, the actual code is misleading, due to the Dialog Application.


Sub trackFilePath()
Dim myFile As String

On Error GoTo 0
myFile = Application.GetOpenFilename

MsgBox "Folder: " & myFile
End Sub
 
Upvote 0
Hi,

Thanks for trying to help - but that doesn't seem to be what I need.

I don't want them to have to open up anything. What I want is a way using
VBA to find on their hard drive where the SAS executable resides.

Thanks,
Nancy
 
Upvote 0
Sub myFilePath()

Set fs = Application.FileSearch

With fs
.LookIn = "C:"
.SearchSubFolders = True
.Filename = "sas.exe"

If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."

For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub
 
Upvote 0
I tried the above code, but it just gives me the following error:

Method 'Execute' of object 'FileSearch' failed

I know the sas executable is in my 'C:\Program Files\...\
:oops:

Thanks again,
Nancy
 
Upvote 0
The FileSearch object only works in Windows.

My test of the posted code worked with no errors!

Did you run it from a Standard Module, like: Module1

Check your VBE references, Toolbar: Tools - References

You should have:
VBA
Excel Library
OLE
Office Library
 
Upvote 0
Hi,

I ran it from a new module. I also looked at the References and I have all those already checked. When stepping through the module in debug I still get the same error.

I am still stumped!
:confused:

Nancy
 
Upvote 0
OK, I got it to work! But, only with giving it some of the file path.

This is what I had to do:

Sub mysasFilePath()
Dim testFilePath As String
Dim msg As String

Set fs = Application.FileSearch

With fs
.LookIn = "C:\Program Files\SAS"
.SearchSubFolders = True
.Filename = "sas.exe"
.MatchTextExactly = True

If .Execute() > 0 Then
' MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."


For I = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(I)
testFilePath = .FoundFiles(I)
testFile = Right(testFilePath, 7)
If testFile = "sas.exe" Then

sasFilePath = testFilePath
End If
Next I
Else
msg = "The Path for SAS.exe was not found!"
msg = msg & vbCrLf
msg = msg & "Please check for the SAS9 executable"
MsgBox msg & vbCrLf, vbCritical, "SAS9 Executable Search"
End If
End With

End Sub


If I went back one directory it gave me an error!

Thanks for your help.

Nancy
 
Upvote 0
Hi Nancy,

Instead of searching and looping inside the Program Files directory which in the end may not be where the SAS App resides , why not use the FindExecutable API function which will always retrieve the EXE location and doesn't need looping so it is faster than iterating the folders via the Fileseache object.

Place in a Standard Module and run the Test procedure for a demo:

Code:
Declare Function FindExecutable Lib "shell32.dll" _
Alias "FindExecutableA" (ByVal lpFile As String, _
ByVal lpDirectory As String, ByVal sResult As String) As Long

Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA" (ByVal nSize As Long, ByVal lpBuffer As String) As Long


Function GetExecutablePath() As String

    Dim strBuffer As String
    Dim strTempFolder As String
    Dim lngRet, hFile As Long
    
    strBuffer = Space(256)
    lngRet = GetTempPath(Len(strBuffer), strBuffer)
    strTempFolder = Left(strBuffer, lngRet - 1)
    hFile = FreeFile
    Open strTempFolder & "\Dummy.sas" For Output As #hFile
    Close #hFile
    FindExecutable "Dummy.sas", strTempFolder, strBuffer
    Kill strTempFolder & "\Dummy.sas"
    GetExecutablePath = Left(strBuffer, InStr(strBuffer, vbNullChar) - 1)
   
End Function


Sub test()

    MsgBox "The SAS Executable resides in :   " & GetExecutablePath(), vbInformation

End Sub


Regards.
 
Upvote 0
Hi, Rafaaj2000

I opened up Excel and opened VB. I inserted a module to test with, and copied your code to the module. I stepped through it and what happens is it comes up with a different executable first. There is a sv.exe that it finds and displays that one. This excutable is in the SAS directory, but in a different folder. Why it finds the 'sv.exe' is beyond me.

Thanks for giving me a different option to try!

Nancy
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top