snasui.com ยินดีต้อนรับ ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ ระบุ Version ของ Excel
'download File to FTP
Const cFTPServer As String = "ftp.svi.co.th" 'CHANGE THIS.
Const cFTPPort = 21
Const cFTPCommandsFile As String = "FTP_commands.txt"
Sub SendFileViaFTP_Click()
Dim inputValue As Variant
Dim FTPusername As String, FTPpassword As String
Dim filenum As Integer
Dim FTPcommand As String
Dim wsh As Object
File_Path = Range("b3").Value
inputValue = InputBox("Enter username", cFTPServer)
If inputValue = False Or inputValue = "" Then Exit Sub
FTPusername = CStr(inputValue)
inputValue = InputBox("Enter password for username " & FTPusername, cFTPServer)
If inputValue = False Or inputValue = "" Then Exit Sub
FTPpassword = CStr(inputValue)
'Create file containing ftp commands. The file has to contain the username and password
'to connect to the ftp server. Creating the file and deleting it immediately after use gives some degree of
'security in preference to having a static file containing the username and password
filenum = FreeFile
Open cFTPCommandsFile For Output As #filenum
Print #filenum, "!REM upload .csv file" 'Use !REM for comments with a Windows ftp server
'Print #filenum, "!# upload .csv file" 'Use '# for comments with a Unix ftp server
Print #filenum, "open " & cFTPServer & " " & cFTPPort
Print #filenum, "user " & FTPusername & " " & FTPpassword
Print #filenum, "cd Website-Search"
Print #filenum, "binary"
Print #filenum, "put " & QQ(File_Path & "\" & "*.xlsx")
'The next line is temporarily commented out to omit the bye command from the ftp commands script.
'This keeps the command window open to show whether the ftp commands worked successfully or not.
'If the bye command is omitted you must type bye in the command window to exit ftp and end this procedure
'Print #filenum, "bye"
Close #filenum
'Construct ftp command line, specifying the file containing FTP commands. The -n parameter suppresses auto-login
'upon initial connection because we want to use the username and password specified in the command file
FTPcommand = "ftp -i -n -s:" & QQ(cFTPCommandsFile)
'Run ftp command synchronously, waiting for the command to return
'WindowStyle 1 displays the command window. Use this, together with omitting the bye command, to verify
'whether or not the ftp commands worked successfully.
'WindowStyle 0 hides the command window.
'See http://msdn.microsoft.com/en-us/library/d5fk67ky%28VS.85%29.aspx
CreateObject("WScript.Shell").Run Command:=FTPcommand, WindowStyle:=1, waitonreturn:=True
'Delete the ftp commands file so that the username and password are not left lying around
Kill cFTPCommandsFile
MsgBox "Finished"
End Sub
Private Function QQ(text As String) As String
QQ = Chr(34) & text & Chr(34)
End Function
Sub SendFileViaFTP_Click()
Dim inputValue As Variant
Dim FTPusername As String, FTPpassword As String
Dim filenum As Integer
Dim FTPcommand As String
Dim wsh As Object
Dim fileName As String
file_path = Range("b3").Value
inputValue = InputBox("Enter username", cFTPServer)
If inputValue = False Or inputValue = "" Then Exit Sub
FTPusername = CStr(inputValue)
inputValue = InputBox("Enter password for username " & FTPusername, cFTPServer)
If inputValue = False Or inputValue = "" Then Exit Sub
FTPpassword = CStr(inputValue)
'Create file containing ftp commands. The file has to contain the username and password
'to connect to the ftp server. Creating the file and deleting it immediately after use gives some degree of
'security in preference to having a static file containing the username and password
fileName = Dir(file_path & "\*.xls")
Do While Len(fileName) > 0
filenum = FreeFile
Open cFTPCommandsFile For Output As #filenum
Print #filenum, "!REM upload .csv file" 'Use !REM for comments with a Windows ftp server
'Print #filenum, "!# upload .csv file" 'Use '# for comments with a Unix ftp server
Print #filenum, "open " & cFTPServer & " " & cFTPPort
Print #filenum, "user " & FTPusername & " " & FTPpassword
Print #filenum, "cd Website-Search"
Print #filenum, "binary"
Print #filenum, "put " & QQ(file_path & "\" & fileName)
'The next line is temporarily commented out to omit the bye command from the ftp commands script.
'This keeps the command window open to show whether the ftp commands worked successfully or not.
'If the bye command is omitted you must type bye in the command window to exit ftp and end this procedure
'Print #filenum, "bye"
Close #filenum
'Construct ftp command line, specifying the file containing FTP commands. The -n parameter suppresses auto-login
'upon initial connection because we want to use the username and password specified in the command file
FTPcommand = "ftp -i -n -s:" & QQ(cFTPCommandsFile)
'Run ftp command synchronously, waiting for the command to return
'WindowStyle 1 displays the command window. Use this, together with omitting the bye command, to verify
'whether or not the ftp commands worked successfully.
'WindowStyle 0 hides the command window.
'See http://msdn.microsoft.com/en-us/library/d5fk67ky%28VS.85%29.aspx
CreateObject("WScript.Shell").Run Command:=FTPcommand, WindowStyle:=1, waitonreturn:=True
'Delete the ftp commands file so that the username and password are not left lying around
Kill cFTPCommandsFile
fileName = Dir
Loop
MsgBox "Finished"
Sub Sendmail()
Dim inputValue As Variant
Dim FTPusername As String, FTPpassword As String
Dim filenum As Integer
Dim FTPcommand As String
Dim wsh As Object
Dim fileName As String
file_path = Range("b3").Value
inputValue = InputBox("Enter username", cFTPServer)
If inputValue = False Or inputValue = "" Then Exit Sub
FTPusername = CStr(inputValue)
inputValue = InputBox("Enter password for username " & FTPusername, cFTPServer)
If inputValue = False Or inputValue = "" Then Exit Sub
FTPpassword = CStr(inputValue)
'Create file containing ftp commands. The file has to contain the username and password
'to connect to the ftp server. Creating the file and deleting it immediately after use gives some degree of
'security in preference to having a static file containing the username and password
fileName = Dir(file_path & "\*.xls")
Do While Len(fileName) > 0
filenum = FreeFile
Open cFTPCommandsFile For Output As #filenum
Print #filenum, "!REM upload .csv file" 'Use !REM for comments with a Windows ftp server
'Print #filenum, "!# upload .csv file" 'Use '# for comments with a Unix ftp server
Print #filenum, "open " & cFTPServer & " " & cFTPPort
Print #filenum, "user " & FTPusername & " " & FTPpassword
Print #filenum, "cd Website-Search"
Print #filenum, "binary"
Print #filenum, "put " & QQ(file_path & "\" & fileName)
'The next line is temporarily commented out to omit the bye command from the ftp commands script.
'This keeps the command window open to show whether the ftp commands worked successfully or not.
'If the bye command is omitted you must type bye in the command window to exit ftp and end this procedure
'Print #filenum, "bye"
Close #filenum
'Construct ftp command line, specifying the file containing FTP commands. The -n parameter suppresses auto-login
'upon initial connection because we want to use the username and password specified in the command file
FTPcommand = "ftp -i -n -s:" & QQ(cFTPCommandsFile)
'Run ftp command synchronously, waiting for the command to return
'WindowStyle 1 displays the command window. Use this, together with omitting the bye command, to verify
'whether or not the ftp commands worked successfully.
'WindowStyle 0 hides the command window.
'See http://msdn.microsoft.com/en-us/library/d5fk67ky%28VS.85%29.aspx
CreateObject("WScript.Shell").Run Command:=FTPcommand, WindowStyle:=1, waitonreturn:=True
'Delete the ftp commands file so that the username and password are not left lying around
Kill cFTPCommandsFile
fileName = Dir
Loop
MsgBox "Finished"
End Sub
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
