FTP access does not work with spaces

[scriggs]

I am using some VBA coding to connect and download over FTP.

The code does not work when the path has a 'space' in it. Any ideas how to get round the issue?

I think its this line which goes wrong as the FTP software gets confused by the space which is inside sFLD

Print #iFile, "dir " & sFLD & " FileList.txt"

and you end up with the command

dir data/company/3rd april filelist.txt

I tried adding a trailing slash and quotes, neither seem to work:

dir data/company/3rd april/ filelist.txt
"dir data/company/3rd april" filelist.txt

Public Function GetFtpFolderFileList(sSVR As String, sFLD As String, sUID As String, sPWD As String) As String

   Dim sLocalFLD As String
   Dim sScrFile As String
   Dim iFile As Integer
   Dim sExe As String

   Const q As String * 1 = """"

   On Error GoTo Err_Handler
   sLocalFLD = CurrentProject.Path & "\" & sFLD
   
   ' will break if empty folder exist so error to pass
   ' must create folder first, so API calls work
   On Error Resume Next
   If Dir(sLocalFLD & "\") = "" Then MkDir (sLocalFLD)
   On Error GoTo Err_Handler
      
   sScrFile = sLocalFLD & "\FileDIR.scr"
   If Dir(sScrFile) = "FileDIR.scr" Then Kill sScrFile

   ' Open a new text file to hold the FTP script and load it with
   ' the appropriate commands.  (Thanks Dev Ashish !!!)
   iFile = FreeFile
   Open sScrFile For Output As iFile
   Print #iFile, "open " & sSVR
   Print #iFile, sUID
   Print #iFile, sPWD
   Print #iFile, "binary"
   Print #iFile, "lcd " & q & sLocalFLD & q
   Print #iFile, "dir " & sFLD & " FileList.txt"
   Print #iFile, "bye"
   Close #iFile
   
   sExe = Environ$("COMSPEC")
   sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
   Debug.Print sScrFile
   sExe = sExe & "ftp.exe -s:" & q & sScrFile & q
   
   ShellWait sExe, vbHide
   DoEvents
   
   If DCount("*", "MsysObjects", "[Name]='tblFTPFileList'") = 1 Then DoCmd.DeleteObject acTable, "tblFTPFileList"
   Debug.Print sLocalFLD & "\FileList.txt"
   DoCmd.TransferText acImportFixed, "FileList Import Specification", "tblFTPFileList", sLocalFLD & "\FileList.txt", False
   
   
Exit_Here:
   GetFtpFolderFileList = GetFileText(sScrFile)
   DoCmd.Hourglass False
   Exit Function
Err_Handler:
   MsgBox Err.Description, vbExclamation, "E R R O R"
   Resume Exit_Here
End Function

 

[PHV]

Perhaps this ?
Print #iFile, "dir """ & sFLD & "/FileList.txt"""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scriggs]

Thanks

I've worked out that the problem is FTP on windows server and support for long file names. Any ideas how to resolve long filenames back to 8.3 filenames?

 

[PHV]

Desn't this works ?
Print #iFile, "[!]get[/!] " & q & sFLD & "/FileList.txt" & q

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scriggs]

Thanks PHV, had tried most permeations of this.

I think the problem is the FTP app being called. I have logged in using command prompt and it doesn't support long filenames from the DIR command, even enclosed in quotes. But it does work if the 8.3 filename is used, i.e. dir data/company/3rdapr~1 filelist.txt.

Now I need to find a way around in FTP or a way to work out the 8.3 filename. Any ideas on that one.... will post up in FTP area.