Board logo

標題: [發問] 如何用VBA 上傳檔案至FTP? [打印本頁]
作者: PKKO    時間: 2016-10-13 23:13     標題: 如何用VBA 上傳檔案至FTP?

各位大大好,小弟想向大家請教上傳的方法
有找了範例的CODE都無法正常執行,不曉得是哪邊的錯誤,還請大大們指教一下
有猜測是我的FTP路徑輸入錯誤
假設我的FTP位置是ftp://test.com
我要放到該位置的下面
那範例的hostFile 要怎麼輸入?hostFile ="ftp://test.com/a.csv" 是這樣嗎?
  1. Private Declare Function InternetOpen _
  2.    Lib "wininet.dll" _
  3.      Alias "InternetOpenA" _
  4.        (ByVal sAgent As String, _
  5.         ByVal lAccessType As Long, _
  6.         ByVal sProxyName As String, _
  7.         ByVal sProxyBypass As String, _
  8.         ByVal lFlags As Long) As Long

  10. 'Connect to the network
  11. Private Declare Function InternetConnect _
  12.    Lib "wininet.dll" _
  13.      Alias "InternetConnectA" _
  14.        (ByVal hInternetSession As Long, _
  15.         ByVal sServerName As String, _
  16.         ByVal nServerPort As Integer, _
  17.         ByVal sUsername As String, _
  18.         ByVal sPassword As String, _
  19.         ByVal lService As Long, _
  20.         ByVal lFlags As Long, _
  21.         ByVal lContext As Long) As Long

  23. 'Get a file using FTP
  24. Private Declare Function FtpGetFile _
  25.    Lib "wininet.dll" _
  26.      Alias "FtpGetFileA" _
  27.        (ByVal hFtpSession As Long, _
  28.         ByVal lpszRemoteFile As String, _
  29.         ByVal lpszNewFile As String, _
  30.         ByVal fFailIfExists As Boolean, _
  31.         ByVal dwFlagsAndAttributes As Long, _
  32.         ByVal dwFlags As Long, _
  33.         ByVal dwContext As Long) As Boolean

  35. 'Send a file using FTP
  36. Private Declare Function FtpPutFile _
  37.    Lib "wininet.dll" _
  38.      Alias "FtpPutFileA" _
  39.        (ByVal hFtpSession As Long, _
  40.         ByVal lpszLocalFile As String, _
  41.         ByVal lpszRemoteFile As String, _
  42.         ByVal dwFlags As Long, _
  43.         ByVal dwContext As Long) As Boolean

  45. 'Close the Internet object
  46. Private Declare Function InternetCloseHandle _
  47.    Lib "wininet.dll" _
  48.      (ByVal hInet As Long) As Integer

  50. Sub UploadFTP()

  52.   Dim INet As Long
  53.   Dim INetConn As Long
  54.   Dim hostFile As String
  55.   Dim Password As String
  56.   Dim RetVal As Long
  57.   Dim ServerName As String
  58.   Dim Success As Long
  59.   Dim UserName As String
  60.   
  61.   Const ASCII_TRANSFER = 1
  62.   Const BINARY_TRANSFER = 2

  64.     ServerName = "ftp://test.com"
  65.     UserName = "UserName"
  66.     Password = "Password"
  67.     localFile = "C:\a.csv"
  68.     hostFile = "\\a.csv"

  70.       RetVal = False
  71.       INet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
  72.         If INet > 0 Then
  73.           INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
  74.             If INetConn > 0 Then
  75.               Success = FtpPutFile(INetConn, localFile, hostFile, BINARY_TRANSFER, 0&)
  76.               RetVal = InternetCloseHandle(INetConn)
  77.             End If
  78.          RetVal = InternetCloseHandle(INet)
  79.         End If

  81.       If Success <> 0 Then
  82.         MsgBox ("Upload process completed")
  83.       Else
  84.         MsgBox "FTP File Error!"
  85.       End If

  87. End Sub
複製代碼
作者: PKKO    時間: 2016-10-13 23:16

這是第二個範例,但也無法執行,想請教變數要如何輸入
我只想把C:\a.csv 的檔案上傳到"ftp://test.com" 的目錄下面而已
  1. Sub SaveToFTPSite()
  2. Dim ftpSite As String
  3. Dim UserID As String
  4. Dim UserPass As String
  5. Dim FTPPath As String
  6. Dim fPathFName As String
  7. Dim ftpWkb As Workbook
  8. ftpSite = "ftp://test.com"
  9. UserID = "UserID"
  10. UserPass = "UserPass"
  11. FTPPath = "upload"

  13. fPathFName = "C:\a.csv"

  15. Application.EnableEvents = False
  16. Application.DisplayAlerts = False

  18. Set ftpWkb = Workbooks.Open(fPathFName)
  19.    
  20. ActiveWorkbook.SaveAs Filename:="ftp://" & UserID & ":" & UserPass & "@" & ftpSite & "/" & FTPPath & "/" & ftpWkb.Name

  22. ftpWkb.Close

  24. Application.EnableEvents = True
  25. Application.DisplayAlerts = True
  26.         
  27. End Sub
複製代碼




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)