lincco的部落格

*.使用URLDownloadToFile去下載

      兩個範例:

http://www.myengineeringworld.net/2013/11/excel-vba-download-internet-files.html

http://www.cpearson.com/excel/downloadfile.aspx

URLDownloadToFile 要用前一樣要先宣告

Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

語法： URLDownloadToFile(0, URL, LocalFilename, 0, 0)

urldownloadtofile第二次下載的時候，會從暫存檔中下載這點bug  網路上有一些解法

<還沒寫完 , 有空再補了>

*.使用Microsoft.XMLHTTP 去下載(透過IE下載)

sub test ()

Dim myURL As String

'來源檔案
myURL = "http://www.somesite.com/file.csv"

'如果有帳號密碼,可以嘗試下面方法,但不一定有效

'http://username:password@myurl.com/myfile.csv


Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody

   '儲存檔案位置
    oStream.SaveToFile ("C:\file.csv")
    oStream.Close

End If

End sub

文章來源 : MSDN