从Excel连接到FTP以自动化文件共享（VBA初学者） [英] Connecting to FTP from Excel to automate file sharing (VBA Beginner)

问题描述

我是Excel VBA的初学者，而我正在尝试通过连接到Excel来自动执行FTP（WinSCP）中的一些文件共享，也可能创建一个可以帮助的宏。在FTP中，我去了会话>生成会话URL /代码>脚本（脚本文件），并且有以下代码：

 打开ftp：// myUsername：myPassword @ theHostname / 
 
＃你的命令1 
＃你的命令2 
 
 exit 
  

我假设开放的行将Excel连接到FTP。我引用此网站的代码放入'＃命令'区域： https://www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html

 打开ftp：// myUsername：myPassword @ theHostname / 
 
选项显式
 
 Sub FtpTest（）
 MsgBox fnDownloadFile（ ftp：// yoursite，username，password，_ 
你的文件的名称，_ 
C：\你要保存的文件的名称）
 End Sub 
 
函数fnDownloadFile（ByVal strHostName As String，_ 
 ByVal strUserName As String，_ 
 ByVal strPassWord As String，_ 
 ByVal strRemoteFileName As String， _ 
 ByVal strLocalFileName As String）As String 
 
'//设置引用：Microsoft Internet Transfer Control 
'//这是Msinet.ocx 
 
 Dim FTP As Inet作为InetCtlsObjects。 Inet 
 
设置FTP =新的Inet InetCtlsObjects.Inet 
 
错误GoTo Errh 
使用FTP 
 .URL = strHostName 
 .Protocol = 2 
 .UserName = strUserName 
 .Password = strPassWord 
 .Execute，Get+ strRemoteFileName ++ strLocalFileName 
尽管.StillExecuting 
 DoEvents 
循环
 fnDownloadFile = .ResponseInfo 
结束
 Xit：
设置FTP =没有
退出函数
 
 Errh：
 fnDownloadFile =Error： - & Err.Description 
简历Xit 
结束功能
 
退出
  

我做了这个网站说要去VBA编辑器>工具>参考并检查Microsoft Internet Control。

1）我使用的代码正确吗？我把它放在正确的区域（在'＃command'区域）吗？现在我将整个代码放在命令按钮中，但是当我点击它时，它只会给出一个高速显示第一行的语法错误：
Private Sub CommandButton1_Click（））

2）我将Msgbox放在第3行，等待用户输入，还是用我的用户名/密码/主机名填写？ （我对VBA中的功能还不是很好）如果我在代码中填写了，我从没有访问网站的时候，为了yoursite的值是多少？

我很抱歉我很困惑:(任何帮助都会很棒，谢谢你提前！

解决方案

我认为您应该看看这里 - Inet对象的Excel VBA参考
这里显示的是如何在vba中为INet对象添加引用。此外，当您只想测试代码是否工作时，而不是将宏分配给按钮等，如果使用功能那么当你去工作表单元格，并开始键入= fnDown ...你应该看到你的宏 - 在那里你可以放置你的函数参数。但是首先你必须关心对Inet的引用。

此链接也可能有帮助： VBA Excel和FTP与MSINET.OCX和Inet类型

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:

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

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.

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())

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?

I'm sorry I'm so confused :( Any help would be great and thank you in advance!

解决方案

I think that You should take a look here - Excel VBA reference for Inet objects it 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.

This link might also be helpful: VBA Excel and FTP with MSINET.OCX and Inet type

这篇关于从Excel连接到FTP以自动化文件共享（VBA初学者）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！