Results 1 to 10 of 10

Finding where the Excel.exe resides

This is a discussion on Finding where the Excel.exe resides within the Excel Questions forums, part of the Question Forums category; I have completed an Excel Project (with lots of VBA code). In some lines the VBA code references directory paths ...

  1. #1
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    471

    Default Finding where the Excel.exe resides

    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

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  3. #3
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    471

    Default

    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

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  5. #5
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    471

    Default

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


    Thanks again,
    Nancy

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  7. #7
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    471

    Default

    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!


    Nancy

  8. #8
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    471

    Default

    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

  9. #9
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default

    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.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  10. #10
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    471

    Default

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com