Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with my Else statement
I have been working on this little project for a few days now and I cannot
figure out how to do this. I need to search a users hard drive for two files; first in thier defualt locations. If they are both found in default locations then they passed into the shell script and run, otherwise the parts that are not in thier default locations are searched for and then passed into the shell and run, else error messages for the file or files that were nto found are sent to the user. Here is my code for this if you have any ideas or tips please let me know. Sub AA() Dim sStr As String, bFound As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = ("Z -config ") sStr = ("Z -config F") bFound = False With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If Dir(exe_path) < "" Then GoTo ConfigSearch Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = Replace(sStr, "Z", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox "RealVNC not found" Else MsgBox "RealVNC not found." End If End If End With ConfigSearch: 'label With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If Dir(config_path) < "" Then Shell (exe_path & " -config " & config_path) Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound = True sStr = Replace(sStr, "F", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox ".VNC File Not Found." Else MsgBox ".VNC File Not Found." End If End If End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with my Else statement
Sub AA()
Dim sStr As String, bFound As Boolean Dim sStr1 As String, bFound1 As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = "" bFound = False If Dir(exe_path) < "" Then ' found at default bFound = True sStr = exe_path Else With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = .FoundFiles(i) Exit For End If Next i End If End With End If sStr1 = "" bFound1 = False If Dir(config_path) < "" Then bFound1 = True sStr1 = config_path Else With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound1 = True sStr1 = .FoundFiles(i) End If Next i End If End With End If If bFound And bFound1 Then ' both file found Shell sStr & " -config " & sStr1 ElseIf bFound Then ' Only viewer found Shell sStr ElseIf bFound1 Then MsgBox "Only config file found, no viewer" Else MsgBox "viewer and config file not found" End If End Sub -- Regards, Tom Ogilvy "SATATOM" wrote in message ... I have been working on this little project for a few days now and I cannot figure out how to do this. I need to search a users hard drive for two files; first in thier defualt locations. If they are both found in default locations then they passed into the shell script and run, otherwise the parts that are not in thier default locations are searched for and then passed into the shell and run, else error messages for the file or files that were nto found are sent to the user. Here is my code for this if you have any ideas or tips please let me know. Sub AA() Dim sStr As String, bFound As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = ("Z -config ") sStr = ("Z -config F") bFound = False With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If Dir(exe_path) < "" Then GoTo ConfigSearch Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = Replace(sStr, "Z", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox "RealVNC not found" Else MsgBox "RealVNC not found." End If End If End With ConfigSearch: 'label With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If Dir(config_path) < "" Then Shell (exe_path & " -config " & config_path) Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound = True sStr = Replace(sStr, "F", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox ".VNC File Not Found." Else MsgBox ".VNC File Not Found." End If End If End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with my Else statement
Thanks Tom, you have been a huge help. But there is one small problem that
im having, if the .vnc config file is not located in its default location it gives an error saying cannot open specified config file. Not sure why this isnt working, is it trying to open the config file before the vncviewer.exe? Tom, if you get a chance to answer this great, but either way you have been very helpful. Thanks, Tom. "Tom Ogilvy" wrote: Sub AA() Dim sStr As String, bFound As Boolean Dim sStr1 As String, bFound1 As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = "" bFound = False If Dir(exe_path) < "" Then ' found at default bFound = True sStr = exe_path Else With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = .FoundFiles(i) Exit For End If Next i End If End With End If sStr1 = "" bFound1 = False If Dir(config_path) < "" Then bFound1 = True sStr1 = config_path Else With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound1 = True sStr1 = .FoundFiles(i) End If Next i End If End With End If If bFound And bFound1 Then ' both file found Shell sStr & " -config " & sStr1 ElseIf bFound Then ' Only viewer found Shell sStr ElseIf bFound1 Then MsgBox "Only config file found, no viewer" Else MsgBox "viewer and config file not found" End If End Sub -- Regards, Tom Ogilvy "SATATOM" wrote in message ... I have been working on this little project for a few days now and I cannot figure out how to do this. I need to search a users hard drive for two files; first in thier defualt locations. If they are both found in default locations then they passed into the shell script and run, otherwise the parts that are not in thier default locations are searched for and then passed into the shell and run, else error messages for the file or files that were nto found are sent to the user. Here is my code for this if you have any ideas or tips please let me know. Sub AA() Dim sStr As String, bFound As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = ("Z -config ") sStr = ("Z -config F") bFound = False With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If Dir(exe_path) < "" Then GoTo ConfigSearch Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = Replace(sStr, "Z", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox "RealVNC not found" Else MsgBox "RealVNC not found." End If End If End With ConfigSearch: 'label With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If Dir(config_path) < "" Then Shell (exe_path & " -config " & config_path) Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound = True sStr = Replace(sStr, "F", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox ".VNC File Not Found." Else MsgBox ".VNC File Not Found." End If End If End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with my Else statement
from what I can see, if the config file is found in the default location,
the sStr1 = Config_path so sStr1 = "C:\temp\v4-5900.vnc" and the shell would be Shell C:\Program Files\RealVNC\vncviewer.exe -Config C:\temp\v4-5900.vnc (for demo assuming vncviewer.exe is in the default). Since you have a space in the path, perhaps it should be Shell "C:\Program Files\RealVNC\vncviewer.exe" -Config C:\temp\v4-5900.vnc You will have to play with it to see what works for spaces in the path of either or both arguments. (use the Command Prompt window) -- Regards, Tom Ogilvy "SATATOM" wrote in message ... Thanks Tom, you have been a huge help. But there is one small problem that im having, if the .vnc config file is not located in its default location it gives an error saying cannot open specified config file. Not sure why this isnt working, is it trying to open the config file before the vncviewer.exe? Tom, if you get a chance to answer this great, but either way you have been very helpful. Thanks, Tom. "Tom Ogilvy" wrote: Sub AA() Dim sStr As String, bFound As Boolean Dim sStr1 As String, bFound1 As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = "" bFound = False If Dir(exe_path) < "" Then ' found at default bFound = True sStr = exe_path Else With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = .FoundFiles(i) Exit For End If Next i End If End With End If sStr1 = "" bFound1 = False If Dir(config_path) < "" Then bFound1 = True sStr1 = config_path Else With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound1 = True sStr1 = .FoundFiles(i) End If Next i End If End With End If If bFound And bFound1 Then ' both file found Shell sStr & " -config " & sStr1 ElseIf bFound Then ' Only viewer found Shell sStr ElseIf bFound1 Then MsgBox "Only config file found, no viewer" Else MsgBox "viewer and config file not found" End If End Sub -- Regards, Tom Ogilvy "SATATOM" wrote in message ... I have been working on this little project for a few days now and I cannot figure out how to do this. I need to search a users hard drive for two files; first in thier defualt locations. If they are both found in default locations then they passed into the shell script and run, otherwise the parts that are not in thier default locations are searched for and then passed into the shell and run, else error messages for the file or files that were nto found are sent to the user. Here is my code for this if you have any ideas or tips please let me know. Sub AA() Dim sStr As String, bFound As Boolean Dim i As Long Dim exe_path As String Dim config_path As String exe_path = "C:\Program Files\RealVNC\vncviewer.exe" config_path = "C:\temp\v4-5900.vnc" sStr = ("Z -config ") sStr = ("Z -config F") bFound = False With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "vncviewer.exe" .FileType = msoFileTypeAllFiles If Dir(exe_path) < "" Then GoTo ConfigSearch Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "\vncviewer.exe", vbTextCompare) Then bFound = True sStr = Replace(sStr, "Z", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox "RealVNC not found" Else MsgBox "RealVNC not found." End If End If End With ConfigSearch: 'label With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .Filename = "v4-5900.vnc" .FileType = msoFileTypeAllFiles If Dir(config_path) < "" Then Shell (exe_path & " -config " & config_path) Else If .Execute() 0 Then For i = 1 To .FoundFiles.Count If InStr(1, .FoundFiles(i), "v4-5900.vnc", vbTextCompare) Then bFound = True sStr = Replace(sStr, "F", .FoundFiles(i)) Shell sStr Exit For End If Next i If Not bFound Then _ MsgBox ".VNC File Not Found." Else MsgBox ".VNC File Not Found." End If End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Problem | Excel Worksheet Functions | |||
Problem with If Statement | Excel Worksheet Functions | |||
IF STATEMENT PROBLEM | Excel Worksheet Functions | |||
if statement problem | Excel Worksheet Functions | |||
IF statement problem | Excel Worksheet Functions |