如何利用VBA巨集進行互動式FTP檔案下載 一鍵下載多個檔案 | VBA SUN VBA_FTP_013

快速學會如何設定
如何利用VBA巨集進行互動式FTP檔案下載並一鍵下載多個檔案
快來試試吧
#excelmacros #vbaftp #excelftp
Kevin MacLeod創作的「Canon in D Major」是依據 創用 CC (姓名標示) 4.0 授權使用。 creativecommon...
來源： incompetech.com...
藝人： incompetech.com/

[@VBA_SUN]

Sub FTP下載2_互動式欄位_單檔() Open Range("B7").Value & "\FTP_File.txt" For Output As #1 '指定文字檔的路徑和名稱 Print #1, "open " & Range("B3").Value '連接ftp主機，輸入ip或域名 Print #1, Range("B4").Value '輸入使用者帳號 Print #1, Range("B5").Value '輸入使用者密碼 Print #1, "cd " & Range("B6").Value '切換遠端ftp主機端的目錄 Print #1, "lcd " & Range("B7").Value '切換本機下載用目錄，ftp取得的檔案會下載到此路徑 Print #1, "bin" '設定傳送的檔案為二進位檔(Binary)，一定要此行，不然傳回的檔案會有問題 Print #1, "get " & """" & Range("B8").Value & """" '用「get」指令取得指定的檔案名稱，用" "雙引號把檔案名稱包起來，不然中間有空白時會失敗無法下載 Print #1, "bye" '結束ftp連線 Close #1 Shell "ftp -s:" & Range("B7").Value & "\FTP_File.txt" '呼叫ftp指令，並執行指定文字檔內容 End Sub Sub FTP下載2_互動式欄位_多檔() Open Range("B7").Value & "\FTP_File.txt" For Output As #1 '指定文字檔的路徑和名稱 Print #1, "open " & Range("B3").Value '連接ftp主機，輸入ip或域名 Print #1, Range("B4").Value '輸入使用者帳號 Print #1, Range("B5").Value '輸入使用者密碼 Print #1, "cd " & Range("B6").Value '切換遠端ftp主機端的目錄 Print #1, "lcd " & Range("B7").Value '切換本機下載用目錄，ftp取得的檔案會下載到此路徑 Print #1, "bin" '設定傳送的檔案為二進位檔(Binary)，一定要此行，不然傳回的檔案會有問題 Print #1, "prompt" '關閉交互提示，避免每次下載檔案時要求確認 Print #1, "mget " & Range("B8").Value '用「mget」指令取得符合模式的檔案 (例如：*.txt) Print #1, "bye" '結束ftp連線 Close #1 Shell "ftp -s:" & Range("B7").Value & "\FTP_File.txt" '呼叫ftp指令，並執行指定文字檔內容 End Sub Sub 欄位整理() ' ' 巨集2 巨集 ' ' Range("A1").Select ActiveCell.FormulaR1C1 = "這是互動式介面，可供使用者在以下欄位輸入FTP站台相關連線內容及下載檔案名稱後再按鈕執行巨集" Range("A3").Select ActiveCell.FormulaR1C1 = "FTP主機位址：" Range("A4").Select ActiveCell.FormulaR1C1 = "連線帳號：" Range("A5").Select ActiveCell.FormulaR1C1 = "連線密碼：" Range("A6").Select ActiveCell.FormulaR1C1 = "遠端主機的目錄：" Range("A7").Select ActiveCell.FormulaR1C1 = "本機儲存的目錄：" Range("A8").Select ActiveCell.FormulaR1C1 = "下載的文字檔名稱：" Columns("B:B").Select Selection.ColumnWidth = 31.17 Selection.ColumnWidth = 33.67 Columns("B:B").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:A").ColumnWidth = 19.33 Columns("B:B").ColumnWidth = 36 Columns("B:B").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A3:B8").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With End Sub