Hi all i am trying to use a portable bit of code to run "Compiler.exe" ie
could be anywhere on users PC so i need code to find the program and run it

any help would be appreciated

Code running from command Button

cheers Arnie

Here is a pair of macros that will recursively find a file on the C: drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe" ie
could be anywhere on users PC so i need code to find the program and run it

any help would be appreciated

Code running from command Button

cheers Arnie

Hi Joel thank you for being so helpful

the code brings up the message box but does not tell you where it is

i was wondering if i could use this but replacing C:\Documents and
Settings\UserName with a wild card as the program will always be on the
Desktop but UserName will always be different

ID = Shell(Root & "C:\Documents and Settings\UserName\Desktop\Compiler.exe",
vbNormalFocus)

"Joel" wrote:

Here is a pair of macros that will recursively find a file on the C: drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe" ie
could be anywhere on users PC so i need code to find the program and run it

any help would be appreciated

Code running from command Button

cheers Arnie

I haven't used this code in a while. I tried it to make sure it still
worked. If you are
getting a not getting anything returned it its for one of three reasons

1) the capitalization of the filename is different

change from
If fl.Name = MyFileName Then

to

If ucase(fl.Name = MyFileName) Then

2) the file is in a directory you don't have permission to access

3) The file doesn't exist. Make suure you are spelling the filename
correctly including spaces.
"Arnie" wrote:

Hi Joel thank you for being so helpful

the code brings up the message box but does not tell you where it is

i was wondering if i could use this but replacing C:\Documents and
Settings\UserName with a wild card as the program will always be on the
Desktop but UserName will always be different

ID = Shell(Root & "C:\Documents and Settings\UserName\Desktop\Compiler.exe",
vbNormalFocus)

"Joel" wrote:

Here is a pair of macros that will recursively find a file on the C: drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe" ie
could be anywhere on users PC so i need code to find the program and run it

any help would be appreciated

Code running from command Button

cheers Arnie

I made a typo

from
If ucase(fl.Name = MyFileName)
to
If ucase(fl.Name) = ucase(MyFileName) then

"Joel" wrote:

I haven't used this code in a while. I tried it to make sure it still
worked. If you are
getting a not getting anything returned it its for one of three reasons

1) the capitalization of the filename is different

change from
If fl.Name = MyFileName Then

to

If ucase(fl.Name = MyFileName) Then

2) the file is in a directory you don't have permission to access

3) The file doesn't exist. Make suure you are spelling the filename
correctly including spaces.
"Arnie" wrote:

Hi Joel thank you for being so helpful

the code brings up the message box but does not tell you where it is

i was wondering if i could use this but replacing C:\Documents and
Settings\UserName with a wild card as the program will always be on the
Desktop but UserName will always be different

ID = Shell(Root & "C:\Documents and Settings\UserName\Desktop\Compiler.exe",
vbNormalFocus)

"Joel" wrote:

Here is a pair of macros that will recursively find a file on the C: drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe" ie
could be anywhere on users PC so i need code to find the program and run it

any help would be appreciated

Code running from command Button

cheers Arnie

Arnie,

If you know that the file 'Compiler.exe" is on the user's desktop, you can
make the path string retrieval much more efficient. Try something like what
I have between the lines below.

'----------------------------------------------------

Sub FindDesktopFile()

Dim objFSO
Dim objShell
Dim strDsk As String

' Get the path to current user desktop
Set objShell = CreateObject("Shell.Application")
Set objFolderDsk = objShell.Namespace(&H10&)
strDsk = objFolderDsk.Self.Path

' Use file system object to confirm file's presence
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strDsk & "\Compiler.exe") Then
MsgBox strDsk & "\Compiler.exe"
End If

Set objFSO = Nothing
Set objFolderDsk = Nothing
Set objShell = Nothing

End Sub


'---------------------------------------------------

Steve Yandl



"Arnie" wrote in message
...
Hi Joel thank you for being so helpful

the code brings up the message box but does not tell you where it is

i was wondering if i could use this but replacing C:\Documents and
Settings\UserName with a wild card as the program will always be on the
Desktop but UserName will always be different

ID = Shell(Root & "C:\Documents and
Settings\UserName\Desktop\Compiler.exe",
vbNormalFocus)

"Joel" wrote:

Here is a pair of macros that will recursively find a file on the C:
drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\",
MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe"
ie
could be anywhere on users PC so i need code to find the program and
run it

any help would be appreciated

Code running from command Button

cheers Arnie

Great Steve finds it straight away

how do i get it to run when its found it?

"Steve Yandl" wrote:

Arnie,

If you know that the file 'Compiler.exe" is on the user's desktop, you can
make the path string retrieval much more efficient. Try something like what
I have between the lines below.

'----------------------------------------------------

Sub FindDesktopFile()

Dim objFSO
Dim objShell
Dim strDsk As String

' Get the path to current user desktop
Set objShell = CreateObject("Shell.Application")
Set objFolderDsk = objShell.Namespace(&H10&)
strDsk = objFolderDsk.Self.Path

' Use file system object to confirm file's presence
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strDsk & "\Compiler.exe") Then
MsgBox strDsk & "\Compiler.exe"
End If

Set objFSO = Nothing
Set objFolderDsk = Nothing
Set objShell = Nothing

End Sub


'---------------------------------------------------

Steve Yandl



"Arnie" wrote in message
...
Hi Joel thank you for being so helpful

the code brings up the message box but does not tell you where it is

i was wondering if i could use this but replacing C:\Documents and
Settings\UserName with a wild card as the program will always be on the
Desktop but UserName will always be different

ID = Shell(Root & "C:\Documents and
Settings\UserName\Desktop\Compiler.exe",
vbNormalFocus)

"Joel" wrote:

Here is a pair of macros that will recursively find a file on the C:
drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\",
MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe"
ie
could be anywhere on users PC so i need code to find the program and
run it

any help would be appreciated

Code running from command Button

cheers Arnie

Steve its ok worked it out

ID = Shell(Root & strDsk & "\Compiler.exe", vbNormalFocus)

thanks for your help

Arnie

"Steve Yandl" wrote:

Arnie,

If you know that the file 'Compiler.exe" is on the user's desktop, you can
make the path string retrieval much more efficient. Try something like what
I have between the lines below.

'----------------------------------------------------

Sub FindDesktopFile()

Dim objFSO
Dim objShell
Dim strDsk As String

' Get the path to current user desktop
Set objShell = CreateObject("Shell.Application")
Set objFolderDsk = objShell.Namespace(&H10&)
strDsk = objFolderDsk.Self.Path

' Use file system object to confirm file's presence
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strDsk & "\Compiler.exe") Then
MsgBox strDsk & "\Compiler.exe"
End If

Set objFSO = Nothing
Set objFolderDsk = Nothing
Set objShell = Nothing

End Sub


'---------------------------------------------------

Steve Yandl



"Arnie" wrote in message
...
Hi Joel thank you for being so helpful

the code brings up the message box but does not tell you where it is

i was wondering if i could use this but replacing C:\Documents and
Settings\UserName with a wild card as the program will always be on the
Desktop but UserName will always be different

ID = Shell(Root & "C:\Documents and
Settings\UserName\Desktop\Compiler.exe",
vbNormalFocus)

"Joel" wrote:

Here is a pair of macros that will recursively find a file on the C:
drive.
You could also use FileSearch but it doesn't work in Excel 2007.


Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "Compiler.exe"
'directory to start searching
strFolder = "c:\"
file_loc = ""
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\",
MyFileName)
If file_loc < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If file_loc = "" Then
For Each fl In folder.Files
If fl.Name = MyFileName Then
file_loc = folder.Name
End If
Next fl
End If
200 On Error GoTo 0

End Sub


"Arnie" wrote:

Hi all i am trying to use a portable bit of code to run "Compiler.exe"
ie
could be anywhere on users PC so i need code to find the program and
run it

any help would be appreciated

Code running from command Button

cheers Arnie