W
WhytheQ
Hello All,
Not too sure if this is actually possible. I found some code for
saving to an FTP site in this group - problem is that our customer has
come back to us with an SFTP site instead - now the code doesn't work.
I've changed a few names (to protect the innocent) but if anyone can
spot any way of getting the following to work I'd be very happy -
maybe just not possible to automate to an SFTP site.
atb
Jason
'=========================
Sub PublishFile()
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 = "\\imsfileserve\department$\DBA\Statistics\Reporting
\Blah\"
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile
'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"
'' 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
'=======================================================
'=======================================================
'address
Print #lInt_FreeFile01, "open sftp://upload1.Blah.com"
'login
Print #lInt_FreeFile01, "BlahBlah"
'password
Print #lInt_FreeFile01, "P38ccBlah"
'=======================================================
'=======================================================
Print #lInt_FreeFile01, "send \\imsfileserve\department$\DBA
\Statistics\Reporting\Blah\Blah_Daily_Extract.csv" 'targetdir/
helloworld.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")
bye:
Kill "\\imsfileserve\department$\DBA\Statistics\Reporting\Blah
\Blah_Daily_Extract.csv"
Exit Sub
Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye
End Sub
'=========================
Not too sure if this is actually possible. I found some code for
saving to an FTP site in this group - problem is that our customer has
come back to us with an SFTP site instead - now the code doesn't work.
I've changed a few names (to protect the innocent) but if anyone can
spot any way of getting the following to work I'd be very happy -
maybe just not possible to automate to an SFTP site.
atb
Jason
'=========================
Sub PublishFile()
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 = "\\imsfileserve\department$\DBA\Statistics\Reporting
\Blah\"
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile
'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"
'' 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
'=======================================================
'=======================================================
'address
Print #lInt_FreeFile01, "open sftp://upload1.Blah.com"
'login
Print #lInt_FreeFile01, "BlahBlah"
'password
Print #lInt_FreeFile01, "P38ccBlah"
'=======================================================
'=======================================================
Print #lInt_FreeFile01, "send \\imsfileserve\department$\DBA
\Statistics\Reporting\Blah\Blah_Daily_Extract.csv" 'targetdir/
helloworld.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")
bye:
Kill "\\imsfileserve\department$\DBA\Statistics\Reporting\Blah
\Blah_Daily_Extract.csv"
Exit Sub
Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye
End Sub
'=========================