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:
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479

ADVERTISEMENT

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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479

ADVERTISEMENT

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
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
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
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,062
Office Version
  1. 2016
Platform
  1. Windows
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.
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,389
Messages
5,571,850
Members
412,421
Latest member
Rimo86
Top