VBA通过FTP提取指定数据的文件名

Sub 提取文件名()


Dim strPNAME As String
Dim nFNO As Integer

Shell "cmd.exe /c DEL " & ThisWorkbook.Path & "\*.log"
'Shell "cmd.exe /c DEL " & ThisWorkbook.Path & "\input\*.bat"
'Shell "cmd.exe /c DEL " & ThisWorkbook.Path & "\input\*.txt"
'Shell "cmd.exe /c DEL " & ThisWorkbook.Path & "\output\*.log"

x = Application.CountA(Worksheets("信息").Range("A:A"))
y = Application.CountA(Worksheets("信息").Range("E:E"))
For I = 2 To x

 For J = 2 To y
  

  strPNAME = ThisWorkbook.Path & "\input\ftptest" & I & J & ".txt"
  nFNO = FreeFile

  Open strPNAME For Output As #nFNO

  Print #nFNO, "open " + Worksheets("信息").Cells(I, 1) '
  Print #nFNO, Worksheets("信息").Cells(I, 2)
  Print #nFNO, Worksheets("信息").Cells(I, 3)
  Print #nFNO, "CD " & Worksheets("信息").Cells(I, 4) & Worksheets("信息").Cells(J, 5)
  Print #nFNO, "ls "
  Print #nFNO, "bye"
  Close #nFNO '关闭
  Open ThisWorkbook.Path & "\input\choosefile" & I & J & ".bat" For Output As #1
  Print #1, "ftp -s:ftptest" & I & J & ".txt>" & ThisWorkbook.Path & "\output\" & Worksheets("信息").Cells(I, 1) & "_" & Worksheets("信息").Cells(J, 5) & ".log"
  Close #1
   'ChDrive "E"
   ChDir ThisWorkbook.Path & "\input"
   Shell "choosefile" & I & J & ".bat"
  Next


Next

'shell启动FTP -n -s为参数
'Shell ("ftp  -s:" & strPNAME)
'ChDrive "E"

'Shell ("ftp  -s:" & strPNAME & "test.log")




MsgBox ("ok")
End Sub
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
'模板: Option Explicit Public 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 Public 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 Public 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 Public 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 Public Declare Function FtpDeleteFile Lib "wininet.dll" Alias "FtpDeleteFileA" _ (ByVal hFtpSession As Long, ByVal lpszFileName As String) As Boolean Public Declare Function FtpRenameFile Lib "wininet.dll" Alias "FtpRenameFileA" _ (ByVal hFtpSession As Long, ByVal lpszExsiting As String, ByVal lpszNew As String) As Boolean Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _ (ByVal hFtpSession As Long, ByVal lpszSearchFile As String, _ lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _ ByVal dwContent As Long) As Long Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _ (ByVal hFind As Long, lpvFndData As WIN32_FIND_DATA) As Long Public Type FILETIME dwLowDateTime As Long dwHighDateTime As Long End Type Public Type WIN32_FIND_DATA dwFilAttributes As Long ftCreationTime As FILETIME ftLastAccessTime As FILETIME ftLastWriteTime As FILETIME nFileSizeHigh As Long nFileSizeLow As Long dwReserved0 As Long dwReserved1 As Long cFileName As String * 260 cAlternate As String * 14 End Type Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String End Type '窗体: Private Sub Command1_Click() 'FTP下载 lnginet = InternetOpen(vbNullString, INTERNET_OPEN_TYPE_PRECONFIG, _ vbNullString, vbNullString, 0&) If lnginet Then lnginetconn = InternetConnect(lnginet, "219.131.192.243", 0, _ "posui", "djposui", 1, 0, 0) If lnginetconn Then blnRC = FtpGetFile(lnginetconn, "/load.txt", "c:\load.txt", 0, 0, 1, 0) If blnRC Then MsgBox "download ok!!!" End If InternetCloseHandle lnginetconn InternetCloseHandle lnginet MsgBox "close ok!!!" Else MsgBox "can't connect" End If Else MsgBox "ftp wrong" End If End Sub Private Sub Command2_Click() 'FTP上传 lnginet = InternetOpen(vbNullString, INTERNET_OPEN_TYPE_PRECONFIG, _ vbNullString, vbNullString, 0&) If lnginet Then lnginetconn = InternetConnect(lnginet, "219.131.192.243", 0, _ "administrator", "vai8888", 1, 0, 0) If lnginetconn Then blnRC = FtpPutFile(lnginetconn, "c:\1.txt", "/1.txt", 0, 0) If blnRC Then MsgBox "download ok!!!" End If InternetCloseHandle lnginetconn InternetCloseHandle lnginet MsgBox "close ok!!!" Else MsgBox "can't connect" End If Else MsgBox "ftp wrong" End If End Sub 方法2: '部件INET Private Sub Command1_Click() Me.Inet1.Execute Me.Inet1.URL, "send c:\11.txt /1.txt" '保存 End Sub Private Sub Command2_Click() Me.Inet1.Execute Me.Inet1.URL, "get /2.txt c:\2.txt" '下载 End Sub
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

qq_44390640

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值