VBA does not recognize file name /w spaces

[demodren]

Hi all, I am using VBA code to download files via ftp.. but it seems VBA does not recognize empty spaces? is there any way around this?

thanks so much

for example I am using

Print #lInt_FreeFile01, "recv myfile.txt " & "M:\test\New York\Manhattan Streets" & "\myfile.txt"

It seems it only works if the directory is all squeezed in

Print #lInt_FreeFile01, "recv myfile.txt " & "M:\test\NewYork\ManhattanStreets" & "\myfile.txt"

 

[demodren]

Anyone any thoughts? I am so close in getting this thing to work and can't figure out the last part

thanks so much

 

[Ruddles]

You're creating a batch file, yes?

Try wrapping the filename with the spaces in quotes:-

Print #lInt_FreeFile01, "recv myfile.txt " & "[COLOR=red][B]""[/B][/COLOR]M:\test\New York\Manhattan Streets" & "\myfile.txt[B][COLOR=red]""[/COLOR][/B]"

or:-

Print #lInt_FreeFile01, "recv myfile.txt " & "[COLOR=red][B]""[/B][/COLOR][COLOR=black]" & "[/COLOR]M:\test\New York\Manhattan Streets" & "\myfile.txt" & "[B][COLOR=#ff0000]""[/COLOR][/B]"

(You have to double quotes up when they're contained in a string.)

Check the contents of the file after you Close it to see the effect.

 

[demodren]

Hi Ruddles, yes I am creating a batch file which upon execution downloads the specified file from ftp.. I tried the below however I got an error 5, saying:" invalid procedure or call argument"

below is the code I use

Sub PullBR()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
On Error GoTo Err_Handler
    lStr_Dir = ThisWorkbook.Path
    lInt_FreeFile01 = FreeFile
    lInt_FreeFile02 = FreeFile
    '' ANW  07-Feb-2003 :
    strDirectoryList = lStr_Dir & "\Directory"
 
        '' Clean up files
    If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList & ".bat")
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
    If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList & ".txt")
 
    '' Delete completion file
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
    '' Create text file with FTP commands
    Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
    Print #lInt_FreeFile01, "open ftp22.12.net"
    Print #lInt_FreeFile01, "account name"
    Print #lInt_FreeFile01, "password"
    Print #lInt_FreeFile01, "cd ismftp"
    Print #lInt_FreeFile01, "ascii"
    Print #lInt_FreeFile01, "recv myfile.txt" & """M:\test\New York\Manhattan Streets" & "\myfile.txt"""

'it works if I use below line, but that just download to the current path(M:\test\New York) where my workbook is saved, I just need the files downloaded 1 folder 'deeper (M:\test\New York\Manhattan Streets)
Print #lInt_FreeFile01, "recv myfile.txt" & ThisWorkbook.Path & "\myfile.txt;"

Print #lInt_FreeFile01, "bye"
    Close #lInt_FreeFile01
    '' Create Batch program
    Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
    Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
    Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList & ".out"
    Close #lInt_FreeFile02
    '' Invoke Directory List generator
    Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
    'Wait for completion
    Do While Dir(strDirectoryList & ".out") = ""
        DoEvents
    Loop
    Application.Wait (Now + TimeValue("0:00:03"))
    '' Clean up files
    If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList & ".bat")
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
    If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList & ".txt")
 
    MsgBox "Done"
bye:
Exit Sub
Err_Handler:
    MsgBox "Error : " & Err.Number & vbCrLf & "Description : " & Err.Description, vbCritical
    Resume bye
End Sub

 

[xenou]

Did you try this bit of advice from Ruddles?

Check the contents of the file after you Close it to see the effect.

What's in the file should work exactly as it is from the command line too. Open a DOS prompt and try it line by line to see if it works.

You probably need a space between the source and destination arguments:
myfile.txt"M:\blahblahblah"
Should be:
myfile.txt "M:\blahblahblah"

 

[Ruddles]

You probably need a space between the source and destination arguments

In the original post and in my reply there was a space in "recv myfile.txt " but in post #4 it was missing.

 

[davidam]

sure is annoying when the original poster does not give a final post of the outcome