Downloading online files using VBA

Morning all,

I'm trying to find out how to download an online xls file and save it locally using vba?

I have the code working fine for a pdf document (see below), but cant get it to work when trying to download a .xls or .doc file

If anyone could point me in the right direction - that would be great.

Thanks
Kurt


Sub DownloadFiles()

Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim PDFFile As String
Dim WHTTP As Object



' Create connections to IE
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0

PDFFile = "WhitechurchWD.pdf"
MyFile = "http://www.ifa-systems-uk.co.uk/webs/whitechurch1982/documents/WMQ42013v2Small.pdf"


'Now we go and get the file and save it to the C:\Temp folder
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.responseBody
Set WHTTP = Nothing

If Dir("C:\Temp", vbDirectory) = Empty Then MkDir "C:\Temp"

FileNum = FreeFile
Open "C:\Temp\" & MyFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum


End Sub

Thanks for you suggestion patel45.

My actual error was a silly typo (apologies).

All sorted now. If anyone else needs this code - here you go:


Sub DownloadMyFiles()

Dim FileNum As Long
Dim FileData() As Byte
Dim PDFFile As String
Dim MyPDFFile As String
Dim XLFile As String
Dim MyXLFile As String
Dim WHTTP As Object

' Create connections to IE
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0

' ------------------------------------------------------------------------------------------------
PDFFile = "SamplePDF.pdf"
MyPDFFile = "http://www.ifa-systems-uk.co.uk/webs/whitechurch1982/documents/WMQ42013v2Small.pdf"

' ------------------------------------------------------------------------------------------------

'Now we go and get the file and save it to the C:\Temp folder
WHTTP.Open "GET", MyPDFFile, False
WHTTP.Send
FileData = WHTTP.responseBody
Set WHTTP = Nothing

If Dir("C:\Temp", vbDirectory) = Empty Then MkDir "C:\Temp"

FileNum = FreeFile
Open "C:\Temp\" & PDFFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
' ----------------------------------------------------------------------------------

'Now the Xl file

' Create connections to IE
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0

XLFile = "SampleExcel.xls"
MyXLFile = "http://www.exinfm.com/excel files/fcfe2st.xls"


WHTTP.Open "GET", MyXLFile, False
WHTTP.Send
FileData = WHTTP.responseBody
Set WHTTP = Nothing

If Dir("C:\Temp", vbDirectory) = Empty Then MkDir "C:\Temp"

FileNum = FreeFile
Open "C:\Temp\" & XLFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum

MsgBox ("Online file downloads complete - remove this msg after testing")

End Sub

Ahh - Thanks Jim885

I get you - Like this:


"

Please Login or Register  to view this content.

"