从 Excel 连接到 FTP 以自动共享文件（VBA 初学者）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44781828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connecting to FTP from Excel to automate file sharing (VBA Beginner)
提问by user90823745
I'm a beginner and new to Excel VBA, but I'm trying to automate some file sharing in FTP (WinSCP) by connecting to Excel and maybe creating a macro that will help. In FTP I went to Session > Generate Session URL/code > Script (script file) and the following code is there:
我是 Excel VBA 的初学者和新手，但我正在尝试通过连接到 Excel 并可能创建一个有用的宏来在 FTP (WinSCP) 中自动共享一些文件。在 FTP 中，我转到 Session > Generate Session URL/code > Script (script file)，下面的代码在那里：
open ftp://myUsername:myPassword@theHostname/
# Your command 1
# Your command 2
exit
I'm assuming the open line would connect Excel to FTP. I'm referencing code from this site to put into the '# command' area: https://www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html
我假设开放线路将 Excel 连接到 FTP。我正在引用此站点中的代码以放入“# 命令”区域：https: //www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html
open ftp://myUsername:myPassword@theHostname/
Option Explicit
Sub FtpTest()
MsgBox fnDownloadFile("ftp://yoursite", "username", "password", _
"The name of your file", _
"C:\The name of your file to save as")
End Sub
Function fnDownloadFile(ByVal strHostName As String, _
ByVal strUserName As String, _
ByVal strPassWord As String, _
ByVal strRemoteFileName As String, _
ByVal strLocalFileName As String) As String
'// Set a reference to: Microsoft Internet Transfer Control
'// This is the Msinet.ocx
Dim FTP As Inet 'As InetCtlsObjects.Inet
Set FTP = New Inet 'InetCtlsObjects.Inet
On Error GoTo Errh
With FTP
.URL = strHostName
.Protocol = 2
.UserName = strUserName
.Password = strPassWord
.Execute , "Get " + strRemoteFileName + " " + strLocalFileName
Do While .StillExecuting
DoEvents
Loop
fnDownloadFile = .ResponseInfo
End With
Xit:
Set FTP = Nothing
Exit Function
Errh:
fnDownloadFile = "Error:-" & Err.Description
Resume Xit
End Function
exit
I did as this site said to go to VBA Editor > Tools > reference and check off Microsoft Internet Control.
我按照本网站所说的去做，转到 VBA 编辑器 > 工具 > 参考并检查 Microsoft Internet Control。
1) Am I using the code right? Did I place it in the right area (in the '# command' area)? And right now I put the entire code in a Command Button, but when I click it it just gives me a Syntax Error highlighting the first line: Private Sub CommandButton1_Click())
1）我使用的代码正确吗？我是否将它放在正确的区域（在“#command”区域）？现在我将整个代码放在一个命令按钮中，但是当我点击它时，它只会给我一个语法错误，突出显示第一行：Private Sub CommandButton1_Click())
2) Do I leave the Msgbox on the 3rd line as is to wait for user input or do I fill out with my username/password/hostname? (I'm not very good with functions in VBA yet) If I do fill it out in the code, what do I put for the "yoursite" value since I'm not accessing a website?
2) 我是将 Msgbox 留在第 3 行以等待用户输入还是填写我的用户名/密码/主机名？（我还不太擅长 VBA 中的函数）如果我在代码中填写它，由于我没有访问网站，我应该为“yoursite”值输入什么？
I'm sorry I'm so confused :( Any help would be great and thank you in advance!
对不起，我很困惑:(任何帮助都会很棒，提前谢谢你！
采纳答案by Mikisz
I think that You should take a look here - Excel VBA reference for Inet objectsit is shown here how to add refernce for INet objects in vba. Furthermore when You just want to test if the code works, instead of assigning macro to button and so on, if You use "Function" then when You go to worksheet cell and start to type =fnDown ... You should see Your macro - there You can put Your function parameters. However first of all You have to take care of the reference to Inet.
我认为你应该看看这里 - Inet 对象的 Excel VBA 参考，这里展示了如何在 vba 中添加 INet 对象的引用。此外，当您只想测试代码是否有效，而不是将宏分配给按钮等时，如果您使用“功能”，那么当您转到工作表单元格并开始键入 =fnDown 时……您应该看到您的宏 -在那里你可以把你的函数参数。但是，首先您必须注意对 Inet 的引用。
This link might also be helpful: VBA Excel and FTP with MSINET.OCX and Inet type
