| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Dir() to fill out missing components from Path?
I’d like a way to flesh out a file’s path the way the Dir() function
does. The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is C:\BigFolder Then all of the following Dir(“SubFolder\myfile.xls”) Dir(“C:SubFolder\myfile.xls”) Dir(“C:\BigFolder\SubFolder\myfile.xls”) ...will be treated as attempts to find this file: C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: You are trying to access C:\BigFolder\SubFolder\myfile.xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: You are trying to access C:\BigFolder\OtherFile.xls ...etc. I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? I’m guessing you’d have to treat all these cases separately. Or does someone have an already-written routine that would do this? Dan Williams danwPlanet |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
You can use the CurDir function to see what the current directory is. For
example... MsgBox "You are trying to access " & CurDir & "\" & UserEnteredFileName Rick "Dan Williams" wrote in message ... I’d like a way to flesh out a file’s path the way the Dir() function does. The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is C:\BigFolder Then all of the following Dir(“SubFolder\myfile.xls”) Dir(“C:SubFolder\myfile.xls”) Dir(“C:\BigFolder\SubFolder\myfile.xls”) ....will be treated as attempts to find this file: C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: You are trying to access C:\BigFolder\SubFolder\myfile.xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: You are trying to access C:\BigFolder\OtherFile.xls ....etc. I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? I’m guessing you’d have to treat all these cases separately. Or does someone have an already-written routine that would do this? Dan Williams danwPlanet |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
No, see my examples. Inserting the current path before the User's
string would not make sense with any of the example strings I listed. Dan On Apr 25, 9:41*am, "Rick Rothstein \(MVP - VB\)" wrote: You can use the CurDir function to see what the current directory is. For example... MsgBox "You are trying to access " & CurDir & "\" & UserEnteredFileName Rick "Dan Williams" wrote in message ... I’d like a way to flesh out a file’s path the way the Dir() function does. *The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is * * * * * C:\BigFolder Then all of the following * * * * * Dir(“SubFolder\myfile.xls”) * * * * * Dir(“C:SubFolder\myfile.xls”) * * * * * Dir(“C:\BigFolder\SubFolder\myfile.xls”) ...will be treated as attempts to find this file: * * * * * C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: * * * * * You are trying to access C:\BigFolder\SubFolder\myfile..xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: * * * * * You are trying to access C:\BigFolder\OtherFile.xls ...etc. *I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? *I’m guessing you’d have to treat all these cases separately. *Or does someone have an already-written routine that would do this? Dan Williams danwPlanet |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
On Apr 25, 9:59*am, Dan Williams
wrote: No, see my examples. *Inserting the current path before the User's string would not make sense with any of the example strings I listed. Dan On Apr 25, 9:41*am, "Rick Rothstein \(MVP - VB\)" wrote: You can use the CurDir function to see what the current directory is. For example... MsgBox "You are trying to access " & CurDir & "\" & UserEnteredFileName Rick "Dan Williams" wrote in message ... I’d like a way to flesh out a file’s path the way the Dir() function does. *The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is * * * * * C:\BigFolder Then all of the following * * * * * Dir(“SubFolder\myfile.xls”) * * * * * Dir(“C:SubFolder\myfile.xls”) * * * * * Dir(“C:\BigFolder\SubFolder\myfile.xls”) ...will be treated as attempts to find this file: * * * * * C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: * * * * * You are trying to access C:\BigFolder\SubFolder\myfile.xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: * * * * * You are trying to access C:\BigFolder\OtherFile.xls ...etc. *I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? *I’m guessing you’d have to treat all these cases separately. *Or does someone have an already-written routine that would do this? Dan Williams danwPlanet- Hide quoted text - - Show quoted text - (Oops, except the first exsmple.) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
I'm relatively familiar with the Dir function, I suspect Rick even more so.
However it is not at all clear from your OP what you are doing or what you want. Regards, Peter T "Dan Williams" wrote in message ... No, see my examples. Inserting the current path before the User's string would not make sense with any of the example strings I listed. Dan |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
Okay, I *think* I see what you are trying to do. Does this function do what
you want? Function CompletePath(ByVal UserEnteredPath As String) As String Dim X As Long Dim Lengths As Long Dim CurrentPath As String Dim FileName As String Dim Path As String Dim TempPath As String Dim Folders() As String If Left(UserEnteredPath, 1) < Left(UserEnteredPath, 1) And _ InStr(UserEnteredPath, ":") 0 Then CompletePath = UserEnteredPath Else Path = Mid$(UserEnteredPath, InStr(UserEnteredPath, ":") + 1) If Left(Path, 1) = "\" Then Path = Mid(Path, 2) Folders = Split(Path, "\") For X = 0 To UBound(Folders) - 1 CurrentPath = CurrentPath & "\" & Folders(X) If InStr(CurDir, CurrentPath) = 0 Then Exit For Else Folders(X) = "" End If Next CompletePath = CurDir & "\" & Join(Folders, "\") Do While InStr(CompletePath, "\\") CompletePath = Replace(CompletePath, "\\", "\") Loop End If End Function Rick "Dan Williams" wrote in message ... No, see my examples. Inserting the current path before the User's string would not make sense with any of the example strings I listed. Dan On Apr 25, 9:41 am, "Rick Rothstein \(MVP - VB\)" wrote: You can use the CurDir function to see what the current directory is. For example... MsgBox "You are trying to access " & CurDir & "\" & UserEnteredFileName Rick "Dan Williams" wrote in message ... I’d like a way to flesh out a file’s path the way the Dir() function does. The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is C:\BigFolder Then all of the following Dir(“SubFolder\myfile.xls”) Dir(“C:SubFolder\myfile.xls”) Dir(“C:\BigFolder\SubFolder\myfile.xls”) ...will be treated as attempts to find this file: C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: You are trying to access C:\BigFolder\SubFolder\myfile.xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: You are trying to access C:\BigFolder\OtherFile.xls ...etc. I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? I’m guessing you’d have to treat all these cases separately. Or does someone have an already-written routine that would do this? Dan Williams danwPlanet |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
Wow, and here I thought I had included too much detail.
Rick, your function is pretty good -- it works for all 4 of my examples -- I really didn't want anyone to write something from scratch. But let me explain it again this way: Suppose the User's string is put into a Dir() function and no file is found. I want to be able to tell the User precisely WHAT file was not found, i.e., I want it to show what the Dir() function looked for, even if it had to fill in parts of the path. Here are the only additional examples I can think of that don't work with your function, but don't spend more time on it, since it should be easy for me to modify your code to include them. Current Path: C:\BigFolder User Input: \radomfile.xls Response: You are trying to access C:\radomfile.xls User Input: D:\radomfile.xls Response: You are trying to access D:\radomfile.xls User Input: D:radomfile.xls Response: You are trying to access D:\radomfile.xls Thanks for the code! Dan On Apr 25, 1:05*pm, "Rick Rothstein \(MVP - VB\)" wrote: Okay, I *think* I see what you are trying to do. Does this function do what you want? Function CompletePath(ByVal UserEnteredPath As String) As String * Dim X As Long * Dim Lengths As Long * Dim CurrentPath As String * Dim FileName As String * Dim Path As String * Dim TempPath As String * Dim Folders() As String * If Left(UserEnteredPath, 1) < Left(UserEnteredPath, 1) And _ * * *InStr(UserEnteredPath, ":") 0 Then * * CompletePath = UserEnteredPath * Else * * Path = Mid$(UserEnteredPath, InStr(UserEnteredPath, ":") + 1) * * If Left(Path, 1) = "\" Then Path = Mid(Path, 2) * * Folders = Split(Path, "\") * * For X = 0 To UBound(Folders) - 1 * * * CurrentPath = CurrentPath & "\" & Folders(X) * * * If InStr(CurDir, CurrentPath) = 0 Then * * * * Exit For * * * Else * * * * Folders(X) = "" * * * End If * * Next * * CompletePath = CurDir & "\" & Join(Folders, "\") * * Do While InStr(CompletePath, "\\") * * * CompletePath = Replace(CompletePath, "\\", "\") * * Loop * End If End Function Rick "Dan Williams" wrote in message ... No, see my examples. *Inserting the current path before the User's string would not make sense with any of the example strings I listed. Dan On Apr 25, 9:41 am, "Rick Rothstein \(MVP - VB\)" wrote: You can use the CurDir function to see what the current directory is. For example... MsgBox "You are trying to access " & CurDir & "\" & UserEnteredFileName Rick "Dan Williams" wrote in message ... I’d like a way to flesh out a file’s path the way the Dir() function does. The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is C:\BigFolder Then all of the following Dir(“SubFolder\myfile.xls”) Dir(“C:SubFolder\myfile.xls”) Dir(“C:\BigFolder\SubFolder\myfile.xls”) ...will be treated as attempts to find this file: C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: You are trying to access C:\BigFolder\SubFolder\myfile.xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: You are trying to access C:\BigFolder\OtherFile.xls ...etc. I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? I’m guessing you’d have to treat all these cases separately. Or does someone have an already-written routine that would do this? Dan Williams danwPlanet- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Dir() to fill out missing components from Path?
Oh, I see that the switch from the C: drive to another drive is
already handled in your code, but you made a typo -- the first line should be If Left(UserEnteredPath, 1) < Left(CurDir, 1) And _ InStr(UserEnteredPath, ":") 0 Then On Apr 25, 2:33*pm, Dan Williams wrote: Wow, and here I thought I had included too much detail. Rick, your function is pretty good -- it works for all 4 of my examples -- I really didn't want anyone to write something from scratch. *But let me explain it again this way: *Suppose the User's string is put into a Dir() function and no file is found. *I want to be able to tell the User precisely WHAT file was not found, i.e., I want it to show what the Dir() function looked for, even if it had to fill in parts of the path. Here are the only additional examples I can think of that don't work with your function, but don't spend more time on it, since it should be easy for me to modify your code to include them. * * * * * Current Path: *C:\BigFolder * * * * * User Input: *\radomfile.xls * * * * * Response: *You are trying to access C:\radomfile.xls * * * * * User Input: *D:\radomfile.xls * * * * * Response: *You are trying to access D:\radomfile.xls * * * * * User Input: *D:radomfile.xls * * * * * Response: *You are trying to access D:\radomfile.xls Thanks for the code! Dan On Apr 25, 1:05*pm, "Rick Rothstein \(MVP - VB\)" wrote: Okay, I *think* I see what you are trying to do. Does this function do what you want? Function CompletePath(ByVal UserEnteredPath As String) As String * Dim X As Long * Dim Lengths As Long * Dim CurrentPath As String * Dim FileName As String * Dim Path As String * Dim TempPath As String * Dim Folders() As String * If Left(UserEnteredPath, 1) < Left(UserEnteredPath, 1) And _ * * *InStr(UserEnteredPath, ":") 0 Then * * CompletePath = UserEnteredPath * Else * * Path = Mid$(UserEnteredPath, InStr(UserEnteredPath, ":") + 1) * * If Left(Path, 1) = "\" Then Path = Mid(Path, 2) * * Folders = Split(Path, "\") * * For X = 0 To UBound(Folders) - 1 * * * CurrentPath = CurrentPath & "\" & Folders(X) * * * If InStr(CurDir, CurrentPath) = 0 Then * * * * Exit For * * * Else * * * * Folders(X) = "" * * * End If * * Next * * CompletePath = CurDir & "\" & Join(Folders, "\") * * Do While InStr(CompletePath, "\\") * * * CompletePath = Replace(CompletePath, "\\", "\") * * Loop * End If End Function Rick "Dan Williams" wrote in message ... No, see my examples. *Inserting the current path before the User's string would not make sense with any of the example strings I listed. Dan On Apr 25, 9:41 am, "Rick Rothstein \(MVP - VB\)" wrote: You can use the CurDir function to see what the current directory is. For example... MsgBox "You are trying to access " & CurDir & "\" & UserEnteredFileName Rick "Dan Williams" wrote in message .... I’d like a way to flesh out a file’s path the way the Dir() function does. The Dir() function (like the DIR command in DOS) fills in missing components depending on what the current Path is set to. Suppose the current Path is C:\BigFolder Then all of the following Dir(“SubFolder\myfile.xls”) Dir(“C:SubFolder\myfile.xls”) Dir(“C:\BigFolder\SubFolder\myfile.xls”) ...will be treated as attempts to find this file: C:\BigFolder\SubFolder\myfile.xls If the User enters any one of the above strings, I’d like to tell them: You are trying to access C:\BigFolder\SubFolder\myfile.xls And if they just enter the string “OtherFile.xls”, I’d like to respond with: You are trying to access C:\BigFolder\OtherFile.xls ...etc. I’d like to be able to make the responses WHETHER THE FILE EXISTS OR NOT, so that if the file does NOT exist, my error message can include the whole attempted path. Is there some easy way I haven’t thought of to do this? I’m guessing you’d have to treat all these cases separately. Or does someone have an already-written routine that would do this? Dan Williams danwPlanet- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Missing fill handle | Excel Discussion (Misc queries) | |||
| Fill-in missing information below | Excel Discussion (Misc queries) | |||
| FILL COLOR BOX MISSING | Excel Discussion (Misc queries) | |||
| Fill in missing months | Excel Discussion (Misc queries) | |||
| Missing WrapText from range object in Office Web Components | Excel Programming | |||
Hybrid Mode
