Question on using SFTP in vba to download a file

[HunterN]

Hi all,

I have a question on using SFTP to download a file from the Linux machine to my PC.

I have gotten this to work, but only if the directory that I have the excel program in, is in a directory path that does not have any spaces in it.

This program is on my PC: XP and using Excel 2007.

Here is my code that does work:

**** Start of Module
Sub SFTP_Get()

Dim strDirectoryList As String
Dim strDirectoryTemp As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
Dim filePath As String

Set fso = CreateObject("Scripting.FileSystemObject")

On Error GoTo Err_Handler

myPath = ThisWorkbook.Path & "\"
checkFile = myPath & UserSelection

lInt_FreeFile01 = FreeFile 'sets to integer 1
lInt_FreeFile02 = FreeFile 'sets to integer 1

lStr_Dir = ThisWorkbook.Path

'* Create a temporary name for where the SFTP command is located
strSFTPDir = "C:\Program Files\Attachmate\Reflection\"

'* Create a temporary ...\Directory file
strDirectoryList = lStr_Dir & "\Directory"
strFile_Temp = lStr_Dir & "\LookatFile"

'* Determine if UserName already has something in it.
If UserName = "" Then
UserName = Environ("USERNAME")
End If

'* Set up the parameters to pass in the LookatFile
Machine = "myMach.myplace.com"
filePath = myPath
LinuxDir = "/cpspb/asec/data/2010"
LinuxDir = LCase(LinuxDir)

Transferfrom = LinuxDir
Filename = "febstats.txt" 'UserSelection
strQuote = Chr$(34) ' ASCII character for Double quotes

'* Create text file with FTP commands - it is called Directory.txt
'* This (.txt) file will be created on the M:drive.

Open strFile_Temp & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "## transfer from Linux Machine to PC"
Print #lInt_FreeFile01, "lcd " & strQuote & filePath & strQuote
Print #lInt_FreeFile01, "cd " & Transferfrom
Print #lInt_FreeFile01, "ascii"
Print #lInt_FreeFile01, "get " & Filename
Print #lInt_FreeFile01, "quit"
Close #lInt_FreeFile01

'* Invoke the .txt file to do the SFTP
retVal = Shell(strSFTPDir & "sftp2 -B " & _
strFile_Temp & ".txt " & _
UserName & "@" & Machine, vbNormalFocus)

Application.Wait (Now + TimeValue("0:00:03"))

If retVal <> 0 Then
'* Create Completion File
End If

bye:
Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & _
"Description : " & Err.Description, vbCritical
Resume bye

End Sub

****
Like I said, this will work if the program is located in an area that does not have spaces in the directory path. So example: If the Excel program that contains the vba is in the following directory - "M:\MyDir\MyStuff\Year2010"
then it works.

But if the Excel program that contains the vba is in a directory that has spaces in it like -
"M:\MyDir\MyStuff\Year 2010"
then it does not work.
The command prompt will not come to the screen.
I believe it has something to do with the spaces in the path. I have tried other options of using quotes in the FTP command that I build, but with no luck. I had to just rename the directory folder.


I would appreciate any suggestions.
Thanks,
Nancy

 

[tweedle]

I believe that is the Unix under the Linux hood that doesn't like the spaces.

Once you ftp to a temporary no-spaces path, then copy it to where you want it.

 

[HunterN]

Hi all - here is the solution to my problem.

It does have to do with the spaces in the directory name where the text file is that is being read.

I added the following before the Shell command and see how I changed the Shell command.

    '* The following works because you encase the directory in double quotes
    testDir = """H:\My Documents\LookatFile.txt"""    ' Those are three quotes on each side
    retVal = Shell(strSFTPDir & "sftp2 -B " & _
          "" & testDir & "" & _
          " " & UserName & "@" & Machine, vbNormalFocus)

Hope this helps someone else.

Nancy