HunterN
Active Member
- Joined
- Mar 19, 2002
- Messages
- 479
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
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