vba上传指定文件ftp服务器

文章目录

• ​​一、需求分析：​​
• ​​二、操作流程：​​

• ​​2.1 【开发工具】-【宏】​​
• ​​2.2 【宏】-【编辑】​​
• ​​2.3 【把脚本复制进去】​​
• ​​2.4 脚本如下​​
• ​​2.5 修改位置​​

• ​​2.5.1 修改sheet名称和表格一致​​
• ​​2.5.2 修改Cells(2,3)​​
• ​​2.5.3 修改4 to 100​​
• ​​2.5.4 修改Cells(i,3)​​
• ​​2.5.5 修改发ftp信息​​
• ​​2.5.6 保存脚本​​

• ​​三、添加上传按钮​​
• ​​效果图：​​

一、需求分析：

• 在excel表格中指定文件路径，将指定文件上传ftp服务器

二、操作流程：

2.1 【开发工具】-【宏】

2.2 【宏】-【编辑】

2.3 【把脚本复制进去】

2.4 脚本如下

Sub 按钮1_Click()
Dim i, str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, d
Dim myarray()
On Error Resume Next
Set mysheet1 = ThisWorkbook.Worksheets("sheetName自定义")
Set fs = CreateObject("Scripting.FileSystemObject")

'获取本地路径
If mysheet1.Cells(2, 3) <> "" Then
str3 = Replace(Sheet1.Cells(2, 3), "/", "\")
str3 = Trim(str3)
If Right(str3, 1) <> "\" Then
str3 = str3 & "\"
'MsgBox str6
End If
End If

'循环扫描文件名,生成一个只有文件名字的字符串
For i = 4 To 100
If mysheet1.Cells(i, 3) <> "" Then
str1 = Replace(Sheet1.Cells(i, 3), "/", "\")
str1 = Trim(str1)
str4 = str3 & str1
str5 = "Echo mput " & Chr(34) & str4 & Chr(34) & " >>ftp.up"
str9 = str9 & " " & str5 'str9所有要上传的文件
End If
Next
'MsgBox str9

'上传
Set fsd = CreateObject("Scripting.FileSystemObject")
str10 = str3 & "1.bat" '脚本
str11 = "Echo open IP地址>ftp.up" '远程路径
str12 = "Echo 用户名>>ftp.up" '账号
str13 = "Echo 密码>>ftp.up" '密码

Set fid = fsd.CreateTextFile(str10, True) '后面开始写脚本
fid.WriteLine ("@Echo Off ") '开远程
fid.WriteLine (str11)
fid.WriteLine (str12)
fid.WriteLine (str13)
fid.WriteLine ("Echo Cd .\User >>ftp.up")
fid.WriteLine ("Echo binary>>ftp.up")
fid.WriteLine ("Echo prompt >>ftp.up")
fid.WriteLine ("Echo lcd " & Chr(34) & str3 & Chr(34) & ">>ftp.up")
fid.WriteLine (str9)
fid.WriteLine ("Echo bye>>ftp.up")
fid.WriteLine ("FTP -s:ftp.up")
fid.WriteLine ("del ftp.up /q")
fid.Close
str16 = "cmd.exe /c " & str10 '运行脚本
'MsgBox str16
Shell str16

 MsgBox "传输完成"
End Sub      

2.5 修改位置

2.5.1 修改sheet名称和表格一致

2.5.2 修改Cells(2,3)

• 指的是地2行

2.5.3 修改4 to 100

• 4指的是从第4行的开始
• 100指的是从第100行开始

2.5.4 修改Cells(i,3)

• i指的是行，3指的是第几列

2.5.5 修改发ftp信息

• ip地址
• 用户名
• 密码

2.5.6 保存脚本

三、添加上传按钮

• 【开发工具】-【插入表单控件】



• -【指定宏】



• -【选择指定的宏名】



• 【双击修改按钮名称】

效果图：

Sub 文件上传ftp服务器()
Dim i, str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, d
Dim myarray(), MyFile As Object
Set MyFile = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set mysheet1 = ThisWorkbook.Worksheets("sheetName自定义")
Set fs = CreateObject("Scripting.FileSystemObject")

'获取本地路径
If mysheet1.Cells(2, 3) <> "" Then
str3 = Replace(Sheet1.Cells(2, 3), "/", "\")
str3 = Trim(str3)
If Right(str3, 1) <> "\" Then
str3 = str3 & "\"
'MsgBox str6
End If
End If

'循环扫描文件名,生成一个只有文件名字的字符串
For i = 4 To 100
If mysheet1.Cells(i, 3) <> "" Then
str1 = Replace(Sheet1.Cells(i, 3), "/", "\")
str1 = Trim(str1)
str4 = str3 & str1

If MyFile.FileExists(str4) = True Then
Else
MsgBox str4 & " 文件不存在"
End If

str5 = "Echo mput " & Chr(34) & str4 & Chr(34) & " >>ftp.up"
str9 = str9 & " " & str5 'str9所有要上传的文件
End If
Next
'MsgBox str9



'上传
Set fsd = CreateObject("Scripting.FileSystemObject")
str10 = str3 & "1.bat" '脚本
str11 = "Echo open ip地址>ftp.up" '远程路径
str12 = "Echo 用户名>>ftp.up" '账号
str13 = "Echo 密码>>ftp.up" '密码

Set fid = fsd.CreateTextFile(str10, True) '后面开始写脚本
fid.WriteLine ("@Echo Off ") '开远程
fid.WriteLine (str11)
fid.WriteLine (str12)
fid.WriteLine (str13)
fid.WriteLine ("Echo Cd .\User >>ftp.up")
fid.WriteLine ("Echo binary>>ftp.up")
fid.WriteLine ("Echo prompt >>ftp.up")
fid.WriteLine ("Echo lcd " & Chr(34) & str3 & Chr(34) & ">>ftp.up")
fid.WriteLine (str9)
fid.WriteLine ("Echo bye>>ftp.up")
fid.WriteLine ("FTP -s:ftp.up")
fid.WriteLine ("del ftp.up /q")
fid.Close
str16 = "cmd.exe /c " & str10 '运行脚本
'MsgBox str16
Shell str16

 MsgBox "传输完成"
End Sub      

Sub 按钮1_Click()
Dim i, str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, d
Dim myarray()
On Error Resume Next
Set mysheet1 = ThisWorkbook.Worksheets("sheetName自定义")
Set fs = CreateObject("Scripting.FileSystemObject")

'获取本地路径
If mysheet1.Cells(2, 3) <> "" Then
str3 = Replace(Sheet1.Cells(2, 3), "/", "\")
str3 = Trim(str3)
If Right(str3, 1) <> "\" Then
str3 = str3 & "\"
'MsgBox str6
End If
End If

'循环扫描文件名,生成一个只有文件名字的字符串
For i = 4 To 100
If mysheet1.Cells(i, 3) <> "" Then
str1 = Replace(Sheet1.Cells(i, 3), "/", "\")
str1 = Trim(str1)
str4 = str3 & str1
str5 = "Echo mput " & Chr(34) & str4 & Chr(34) & " >>ftp.up"
str9 = str9 & " " & str5 'str9所有要上传的文件
End If
Next
'MsgBox str9



'上传
Set fsd = CreateObject("Scripting.FileSystemObject")
str10 = str3 & "1.bat" '脚本
str11 = "Echo open ip地址>ftp.up" '远程路径
str12 = "Echo 用户名>>ftp.up" '账号
str13 = "Echo 密码>>ftp.up" '密码

Set fid = fsd.CreateTextFile(str10, True) '后面开始写脚本
fid.WriteLine ("@Echo Off ") '开远程
fid.WriteLine (str11)
fid.WriteLine (str12)
fid.WriteLine (str13)
fid.WriteLine ("Echo Cd .\User >>ftp.up")
fid.WriteLine ("Echo binary>>ftp.up")
fid.WriteLine ("Echo prompt >>ftp.up")
fid.WriteLine ("Echo lcd " & Chr(34) & str3 & Chr(34) & ">>ftp.up")
fid.WriteLine (str9)
fid.WriteLine ("Echo bye>>ftp.up")
fid.WriteLine ("FTP -s:ftp.up")
fid.WriteLine ("del ftp.up /q")
fid.Close
str16 = "cmd.exe /c " & str10 '运行脚本
'MsgBox str16
Shell str16

 MsgBox "传输完成"
End Sub