lincco的部落格

下面只是個簡單範例,教你怎麼下載或是上傳資料,

並且會將local端資料夾建立,再將資料匯入

下面程式碼有少一些步驟,

大家可以隨喜加入你要做的動作

如果想要知道更多訊息,可以參考下面網站的FTP寫法

http://blog.xuite.net/crdotlin/excel

Sub download fil()

'列出要連線多少台server下載資料
END_Y = Worksheets("主程式").Cells(65536, 1).End(xlUp).Row

for server_row = 1 to END_Y
 
      ' ##指定下載的目錄, 如果目錄不存在,自動建立
          tmp_path = "d:\temp\usr_log\"     
          Call creat_dir(tmp_path)
 

     '###這裡可以指定server的 IP/帳號/密碼,在sheets欄位內,再跑回圈去下載####
       IP_address = Sheets("主程式").Cells(server_row, 2)
       user_name = Sheets("主程式").Cells(server_row, 3)
       passwd = Sheets("主程式").Cells(server_row, 4)

    

     
             Open "d:\temp\ftp_get.bat" For Output As #1
            Print #1, "ftp -s:d:\temp\ftp_script"
            Close #1
    
            Open "d:\temp\ftp_script" For Output As #1
            Print #1, "open " & IP_address
            Print #1, user_name
            Print #1, passwd
            Print #1, ascii                                   '指定傳送是ASCII或是BIN

            Print #1, "lcd tmp_path"                     '指定下載目錄位置      
            Print #1, "cd \usr\data"

            
            '使用get下載單一個檔案,或是mget xxxx*  ,一次下載多個檔案
             Print #1, "get usr_log.txt"        

            '使用put 上傳單一個檔案,或是mput xxxx*  ,一次上傳在多個檔案
             Print #1, "put test_file"
             
             Print #1, "bye"
             Close #1
            
            
            
                         '## 將usr_log.txt匯入到EXCEL內 ##
                                 
                                 hh = 1
                     
                                Open tmp_path & "usr_log.txt" For Input As #2
                                    Do Until EOF(2)
                                    Line Input #2, mybuf
                               Worksheets("主程式").Cells(hh, 10) = mybuf
                                    
                                    hh = hh + 1
                                Loop
            
                                Close #2
                    
     
     
     next server_row
     
     
     End Sub

     
Sub creat_dir(newPath As String)
 
    Dim D1, D2, R1
 
    D1 = InStr(1, newPath, ":\") + 1
 
    Do
        D2 = InStr(D1 + 1, newPath, "\")
        R1 = Dir(Mid(newPath, 1, D2), vbDirectory)
        If R1 = "" Then
            MkDir Mid(newPath, 1, D2)
        End If
        D1 = D2
    Loop Until D2 = Len(newPath)
 
End Sub