download a file from a webpage with VBA

[RB Smissaert]

How do I download a file from a webpage with VBA?
I know how to open a hyperlink with the FollowHyperlink function, but I
can't find any information about how to download a file to a specified
folder.
The idea is to automatically update an Excel add-in from a webpage.
Thanks for any advice.

RBS

 

[Robin Hammond]

RB,

You'll need to know the ip address of the source machine and change the
strFullURL line.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Sub GetInternetFile(strFileName As String)

Dim lReturn As Long
Dim strFullURL As String
Dim strLocation as string

strFullURL = "http:\\nnn.nnn.nnn.nnn\subfolder\" & strFileName
strLocation = environ("Temp") & "\" & strFileName

lReturn = URLDownloadToFile(0, strFullURL, strLocation, 0, 0)

If lReturn <> 0 Then call msgbox("failed")

End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[mudraker]

RB

I found this code sometime ago and I use it almost daily to download a
text file





''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copyright ©1996-2003 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare 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

Private Const ERROR_SUCCESS As Long = 0
Private Const BINDF_GETNEWESTVERSION As Long = &H10
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000



Sub AutoDownloadIntranetFile()
Dim sSourceUrl As String
Dim sLocalFile As String
Dim hfile As Long

sSourceUrl = "http://www.??????"
sLocalFile = "c:\temp"
If DownloadFile(sSourceUrl, sLocalFile) = False Then
Call cmdE
MsgBox "Error In Downloading File" _
& Chr(10) _
& "Cannot Continue", vbCritical
End
End If
End Sub


Public Function DownloadFile(sSourceUrl As String, _
sLocalFile As String) As Boolean

'Download the file. BINDF_GETNEWESTVERSION forces
'the API to download from the specified source.
'Passing 0& as dwReserved causes the locally-cached
'copy to be downloaded, if available. If the API
'returns ERROR_SUCCESS (0), DownloadFile returns True.
DownloadFile = URLDownloadToFile(0&, _
sSourceUrl, sLocalFile, _
BINDF_GETNEWESTVERSION, _
0&) = ERROR_SUCCESS
End Function

 

[RB Smissaert]

Robin, Mudraker,

Thanks for the code.
I haven't got it working yet, but that is because I have some trouble
figuring out the right
URL of the download link.
MudRaker, what is the line Call cmdE doing?

RBS

 

[RB Smissaert]

All fixed now. It works very smooth indeed. Thanks for the code.

RBS

 

[mudraker]

RB

a moudule that used to do more but now only has one command in it

sub cmdE
AppActivate "microsoft excel"
end eu