vba: windows path dir invalid? spaces possibly causing the problem

[ratt2581]

I seem to be running into errors with invalid paths and can't quite figure it out.
What I have is a userform that allows the user to enter the path they wish to save to (tbSaveTo) - when they click send it checks this path and
1st) it substitutes out "/" with "\" then
2nd) it checks to see if it ends with "\" - if not it adds it
3rd) checks if the directory then exists. it works the majority of the time but every so often it runs into issues and it appears to be issues whereever there are spaces in the directory names (ie: "My Documents").

Below is the code i use up to where my error msg is called

Anyone have any suggestions on possible causes/solutions/ideas?

        fPath = WorksheetFunction.Substitute(tbSaveTo.Value, "/", "\")
        If Right(fPath, 1) <> "\" Then
            fPath = "" & fPath & "\" & ""
            'i've tried the above and also jsut fpath = fpath & "\"
        Else
            fPath = "" & fPath & ""
            'i've tried the above and also jsut fpath w/o ""double quotes
        End If
        If Len(Dir(fPath)) = 0 Then
            MsgBox "here is where my error msg is called"

 

[Smitty]

Ummm, why not avoid the error issue in the first place and just use the GetSaveAsFilename method:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> GetSaveAsFileName()<br>    <SPAN style="color:#00007F">Dim</SPAN> FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Filt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FilterIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN><br>        ChDir "C:\Documents and Settings\All Users\Desktop\"<br>    <SPAN style="color:#007F00">'   Set File Filter</SPAN><br>        Filt = "Excel Files (*.xls), *.xls"<br>    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN><br>        FilterIndex = 5<br>    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN><br>        Title = "Please select a different File"<br>    <SPAN style="color:#007F00">'   Get FileName</SPAN><br>        FileName = Application.GetSaveAsFileName(InitialFileName:=Range("A1"), FileFilter:=Filt, _<br>            FilterIndex:=FilterIndex, Title:=Title)<br>    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN><br>        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")<br>    <SPAN style="color:#007F00">'   Save & Close Workbook</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook<br>            .SaveAs FileName<br>            .Close<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[ratt2581]

thank you - wasn't even aware of that function.

very helpful and able to tweak to meet my needs - thanks again