文章目录
一、需求分析：二、操作流程：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 1002.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, dDim myarray()On Error Resume NextSet mysheet1 = ThisWorkbook.Worksheets("sheetName自定义")Set fs = CreateObject("Scripting.FileSystemObject")'获取本地路径If mysheet1.Cells(2, 3) <> "" Thenstr3 = Replace(Sheet1.Cells(2, 3), "/", "\")str3 = Trim(str3)If Right(str3, 1) <> "\" Thenstr3 = str3 & "\"'MsgBox str6End IfEnd If'循环扫描文件名,生成一个只有文件名字的字符串For i = 4 To 100If mysheet1.Cells(i, 3) <> "" Thenstr1 = Replace(Sheet1.Cells(i, 3), "/", "\")str1 = Trim(str1)str4 = str3 & str1str5 = "Echo mput " & Chr(34) & str4 & Chr(34) & " >>ftp.up"str9 = str9 & " " & str5 'str9所有要上传的文件End IfNext'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.Closestr16 = "cmd.exe /c " & str10 '运行脚本'MsgBox str16Shell str16MsgBox "传输完成"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, dDim myarray(), MyFile As ObjectSet MyFile = CreateObject("Scripting.FileSystemObject")On Error Resume NextSet mysheet1 = ThisWorkbook.Worksheets("sheetName自定义")Set fs = CreateObject("Scripting.FileSystemObject")'获取本地路径If mysheet1.Cells(2, 3) <> "" Thenstr3 = Replace(Sheet1.Cells(2, 3), "/", "\")str3 = Trim(str3)If Right(str3, 1) <> "\" Thenstr3 = str3 & "\"'MsgBox str6End IfEnd If'循环扫描文件名,生成一个只有文件名字的字符串For i = 4 To 100If mysheet1.Cells(i, 3) <> "" Thenstr1 = Replace(Sheet1.Cells(i, 3), "/", "\")str1 = Trim(str1)str4 = str3 & str1If MyFile.FileExists(str4) = True ThenElseMsgBox str4 & " 文件不存在"End Ifstr5 = "Echo mput " & Chr(34) & str4 & Chr(34) & " >>ftp.up"str9 = str9 & " " & str5 'str9所有要上传的文件End IfNext'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.Closestr16 = "cmd.exe /c " & str10 '运行脚本'MsgBox str16Shell str16MsgBox "传输完成"End Sub
无校验版本
Sub 按钮1_Click()Dim i, str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, dDim myarray()On Error Resume NextSet mysheet1 = ThisWorkbook.Worksheets("sheetName自定义")Set fs = CreateObject("Scripting.FileSystemObject")'获取本地路径If mysheet1.Cells(2, 3) <> "" Thenstr3 = Replace(Sheet1.Cells(2, 3), "/", "\")str3 = Trim(str3)If Right(str3, 1) <> "\" Thenstr3 = str3 & "\"'MsgBox str6End IfEnd If'循环扫描文件名,生成一个只有文件名字的字符串For i = 4 To 100If mysheet1.Cells(i, 3) <> "" Thenstr1 = Replace(Sheet1.Cells(i, 3), "/", "\")str1 = Trim(str1)str4 = str3 & str1str5 = "Echo mput " & Chr(34) & str4 & Chr(34) & " >>ftp.up"str9 = str9 & " " & str5 'str9所有要上传的文件End IfNext'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.Closestr16 = "cmd.exe /c " & str10 '运行脚本'MsgBox str16Shell str16MsgBox "传输完成"End Sub
