通过Excel VBA宏自动FTP [英] Automatic FTP via Excel VBA Macro

问题描述

我正在尝试通过VBA将电子表格上传到我的网站空间。

它无法正常工作。



我错过了什么显而易见？

我已经介入，它最终崩溃，转到msgbox并显示失败。

我认为问题出在HOSTNAME阶段，但对于我的生活我看不到它。



这是我正在使用的代码...

 ' 打开Internet对象 
 私有 声明 功能 InternetOpen _ 
  Lib    wininet.dll _ 
 别名   InternetOpenA _ 
（ ByVal  sAgent 作为 字符串，_ 
  ByVal  lAccessType 作为 长，_ 
  ByVal  sProxyName 作为 字符串，_ 
 < span class =code-keyword> ByVal  sProxyBypass 作为 字符串，_ 
  ByVal  lFlags 作为 长）作为 长 
 
 ' 连接到网络 
 私有 声明 功能 Interne tConnect _ 
  Lib    wininet.dll  _ 
 别名   InternetConnectA  _ 
（ ByVal  hInternetSession  As  长，_ 
  ByVal  sServerName 作为  String ，_ 
  ByVal  nServerPort  As  整数，_ 
  ByVal  sUsername  As  字符串，_ 
  ByVal  sPassword 作为 字符串，_ 
  ByVal  lService  As   Long ，_ 
  ByVal  lFlags  As  长，_ 
  ByVal  lContext 作为 长）作为 长 
 
  使用FTP获取文件 
 私有 声明 功能 FtpGetFile _ 
  Lib    wininet.dll _ 
  Alias    FtpGetFileA _ 
（ ByVal  hFtpSession 作为 长，_ 
  ByVal  lpszRemoteFile  As  字符串，_ 
  ByVal  lpszNewFile 作为  String ，_ 
  ByVal  fFailIfExists  As   Boolean ，_ 
  ByVal  dwFlagsAndAttributes  As  长，_ 
  ByVal  dwFlags  As  < span class =code-keyword>长，_ 
  ByVal  dwContext  As  长）作为 布尔 
 
 ' 使用FTP发送文件 
 私有 声明 功能 FtpPutFile _ 
  Lib    wininet.dll _ 
 别名   FtpPutFileA _ 
（ ByVal  hFtpSession 作为 长，_ 
  ByVal  lpszLocalFile 作为 字符串，_ 
 < span class =code-keyword> ByVal  lpszRemoteFile  As  字符串，_ 
  ByVal  dwFlags  As   Long ，_ 
  ByVal  dwContext 作为 长）作为  Boolean  
 
 ' 关闭互联网对象 
 私有 声明 功能 InternetCloseHandle _ 
  Lib    wininet.dll _ 
（ ByVal  hInet 作为 长）作为 整数 
 
  Sub  UploadFTP（）
 
  Dim  hostFile  As   String  
  Dim  INet  As   Long  
  Dim  INetConn 作为 长 
  Dim 密码作为 字符串 
  Dim  RetVal  As   Long  
  Dim  ServerName  As   String  
 昏暗成功作为 长 
  Dim  UserName  As   String  
 
  Const  ASCII_TRANSFER =  1  
  Const  BINARY_TRANSFER =  2  
 
 ServerName =   www.users.freenetname.co.uk 
 UserName =  我的用户名 
密码=  我的密码 
 localFile =   C：\ Users \Public\Public Documents\Tables.xlsm 
 hostFile =   \\\\ public_html \Tables.xlsm 
 
 RetVal =  False  
 INet = InternetOpen（  MyFTP Control，1&，vbNullString，vbNullString ，0&）
 如果 INet>  0  然后 
 INetConn = InternetConnect（INet，ServerName，0&，UserName，Password，1& ;，0&，0&）
 如果 INetConn>  0  然后 
成功= FtpPutFile（INetConn，localFile，hostFile，BINARY_TRANSFER，0&）
 RetVal = InternetCloseHandle（INetConn）
 结束 如果 
 RetVal = InternetCloseHandle （INet）
 结束 如果 
 
 如果成功<>  0  然后 
 MsgBox（ 上传过程已完成）
 否则 
 MsgBox   FTP文件错误！ 
 结束 如果 
 
 结束  Sub  

解决方案

如果Success返回0那么我会查看你的FtpPutFile（）函数，我会看一下转移参数。它看起来不像这样：



MSDN - FtpPutFile（） [ ^ ]





试试使用此页面链接中列出的值，看看会发生什么。只是一个想法。



祝你好运，戴夫！

我看到2个可能的错误原因：

1）你试图把当前文件（文件不能放/发送自己），

2）你没有设置目标文件夹（public_html）



这里 [ ^ ]你可以找到功能描述和完整的工作实例（经过一些简单的修改）。



我希望它会有所帮助。

更改URL   FTP。工作  me ... 
 
 hostFile =   /的public_html / Tables.xlsm 

I am trying to upload a spreadsheet to my webspace via VBA.
It isn't working.

Am I missing something obvious?
I have stepped through and it crashes at the end, going to the msgbox and showing a fail.
I think the problem is at the HOSTNAME stage, but for the life of me I cannot see it.

Here is the code I am using...

'Open the Internet object
 Private Declare Function InternetOpen _
   Lib "wininet.dll" _
     Alias "InternetOpenA" _
       (ByVal sAgent As String, _
        ByVal lAccessType As Long, _
        ByVal sProxyName As String, _
        ByVal sProxyBypass As String, _
        ByVal lFlags As Long) As Long

'Connect to the network
 Private Declare Function InternetConnect _
   Lib "wininet.dll" _
     Alias "InternetConnectA" _
       (ByVal hInternetSession As Long, _
        ByVal sServerName As String, _
        ByVal nServerPort As Integer, _
        ByVal sUsername As String, _
        ByVal sPassword As String, _
        ByVal lService As Long, _
        ByVal lFlags As Long, _
        ByVal lContext As Long) As Long

'Get a file using FTP
 Private Declare Function FtpGetFile _
   Lib "wininet.dll" _
     Alias "FtpGetFileA" _
       (ByVal hFtpSession As Long, _
        ByVal lpszRemoteFile As String, _
        ByVal lpszNewFile As String, _
        ByVal fFailIfExists As Boolean, _
        ByVal dwFlagsAndAttributes As Long, _
        ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean

'Send a file using FTP
 Private Declare Function FtpPutFile _
   Lib "wininet.dll" _
     Alias "FtpPutFileA" _
       (ByVal hFtpSession As Long, _
        ByVal lpszLocalFile As String, _
        ByVal lpszRemoteFile As String, _
        ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean

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

Sub UploadFTP()

  Dim hostFile As String
  Dim INet As Long
  Dim INetConn As Long
  Dim Password As String
  Dim RetVal As Long
  Dim ServerName As String
  Dim Success As Long
  Dim UserName As String
  
  Const ASCII_TRANSFER = 1
  Const BINARY_TRANSFER = 2

    ServerName = "www.users.freenetname.co.uk"
    UserName = "My User Name"
    Password = "My Password"
    localFile = "C:\Users\Public\Public Documents\Tables.xlsm"
    hostFile = "\\public_html\Tables.xlsm"

      RetVal = False
      INet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
        If INet > 0 Then
          INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
            If INetConn > 0 Then
              Success = FtpPutFile(INetConn, localFile, hostFile, BINARY_TRANSFER, 0&)
              RetVal = InternetCloseHandle(INetConn)
            End If
         RetVal = InternetCloseHandle(INet)
        End If

      If Success <> 0 Then
        MsgBox ("Upload process completed")
      Else
        MsgBox "FTP File Error!"
      End If

End Sub

解决方案

If "Success" is returning a "0" then I would look at your FtpPutFile() function and I would look at the transfer parameter. It doesn't look like this:

MSDN - FtpPutFile()[^]


Try using the values listed in this page link and see what happens. Just a thought.

Good luck, Dave!

I see 2 potential reasons of error:
1) you are trying to put current file (file can't put/send itself),
2) you don't set destination folder (public_html)

Here[^] you can find the description of function and complete, working example (after few simple modifications).

I hope it will be helpful.

Change the URL for FTP. Worked for me...

hostFile = "/public_html/Tables.xlsm"

这篇关于通过Excel VBA宏自动FTP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！