PDA

View Full Version : vba to download files from internet



photon_ed
05-19-2006, 10:32 AM
Hello,

I am wondering if vba under excel can be used to retrieve/download files from the internet using Internet explorer. I will be grateful if someone can set me off to the right direction. Thank you.

yours,
Ed

mvidas
05-19-2006, 10:46 AM
Hi Ed,

Do you need to do it using IE? It is possible, though a lot more complicated and not completely automatic. You can, however, use a variety of methods to do the same. Heres a function you can use, pass it the file URL and the local location to save it to:Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
oXMLHTTP.Open "GET", vWebFile, False
oXMLHTTP.send
oResp = oXMLHTTP.ResponseBody
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
Set oXMLHTTP = Nothing
End FunctionMatt

Marcster
05-19-2006, 11:07 AM
There's also an API function called URLDownloadToFile
http://msdn.microsoft.com/library/default.asp?url=/workshop/networking/moniker/reference/functions/urldownloadtofile.asp

Marcster.

photon_ed
05-19-2006, 08:47 PM
Thanks for you prompt replies.
I will have a go at it however it would be very help if I can have some simiple instructions as to what to do with the codes. concept such as loops is just about what I comprehend for the time being; but having said that, I am eager to learn :)
Thank you very much.

yours,
Ed :)

stanl
05-20-2006, 05:17 AM
Thanks for you prompt replies.
I will have a go at it however it would be very help if I can have some simiple instructions as to what to do with the codes.

There is also a free ActiveX component from xStandard (www.xstandard.com (http://www.xstandard.com)), that offers cross-browser support. In terms of assiting you with coding, perhaps you might want to explain more about what you are trying to accomplish - (1) getting a pages HTML, (2) getting specific graphic files on a page (3) getting a list of 'links' as each of these will affect coding.
.02 Stan

photon_ed
05-20-2006, 12:13 PM
The codes has to be able to download files from a secure website if login user name and passwords. I will be grateful if someone can give me some instructions to get mvidas’s “SaveWebFile” function to run and also some general advices will be will appreciated. Thank you very much.

yours,
Ed

photon_ed
05-20-2006, 12:29 PM
Hello,

I manage to run mvida's "SaveWebFile" function however, the program encounter a "run-time error '53' File not found" with "kill vLocalFile" highlighted Debug. I suspect that this is course by the secure login process, Please advice.
Thank you.

yours,
Ed

Norie
05-20-2006, 01:35 PM
Ed

Can you post the code you used?

stanl
05-20-2006, 01:46 PM
you will have to add authentication to the Open() method


oXMLHTTP.Open "GET", vWebFile, False,"user", "pwd"


where user and pwd are the real username, password. If I'm not mistaken:dunno that code requires a very specific reference to a server filespec, so I support Norie's request that you post your efforts so far.
Stan

photon_ed
05-20-2006, 01:51 PM
Hello,
Thank you very much for all your prompt replies.
I Had another go after a little break and realized that mvida's "SaveWebFile" codes works perfectly! However, comments on my "Private Sub" codes are more then welcome :)
Having said that, I have to login using IE before hand. I will have a go with Stanl suggestion and get back to you all.
Thank you!!
yours,
Ed



Private Sub CommandButton1_click()
Dim run As String
run = SaveWebFile("https://WhaEvEr.com/THE.pdf, "I:\THE.pdf")

End Sub

Function SaveWebFile(ByVal vWebfile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
oXMLHTTP.Open "GET", vWebfile, False
oXMLHTTP.send
oResp = oXMLHTTP.ResponseBody
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
Set oXMLHTTP = Nothing
End Function

photon_ed
05-21-2006, 04:57 AM
hum... the program will only work AFTER I manually downloaded the files and NOT before, can someone please advices. Thank you.

yours,
Ed

stanl
05-21-2006, 05:53 AM
just a S.W.A.G* - probably a multi-step process (1) authenticate to server (2) download file(s) - so perhaps try




oXMLHTTP.Open "GET", "https://WhaEvEr.com", False,"user", "pwd"


and check for error codes or a response text with "404 Forbidden".





*(S)ophisticated (W)ild-(A)ssed (G)uess

photon_ed
05-21-2006, 06:07 AM
Thanks for the prompt reply stanl.
I have already tried your above code and it didnt like it. I was told that there would be about 4 different protocol to go through to download files from a secure website. So any suggestions will be appreciated :)
Thank you!

yours,
Ed

stanl
05-21-2006, 06:24 AM
O.K - to summarize

If you use IE and navigate to site you can enter a user/pwd? -
After that, the HTTP protocol will download files? -

If the answer is Yes to both of these, perhaps you can open a hidden instance of the InternetExplorer.Application Object, enter the user/pwd and click Submit [or whatever]; then, assuming authentication, use HTTP to download files.

If you would like to see code for the IE Object, just pass the real url for the site your are trying to access; then we can supply VBA code to use the IE Object to enter user/pwd.
:dunno Stan

Emily
05-21-2006, 11:19 AM
Set reference to Microsoft HTML Object Library、Microsoft Internet Controls


'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime :
' Author : chijanzen
' Purpose :
'---------------------------------------------------------------------------------------
'Option Explicit
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
Sub Initialize()
'link stored in A3
Call DownloadFile(Range("A3"))
End Sub
Sub DownloadFile(sURL As String)
Dim IE As New InternetExplorer
Dim oDoc As New MSHTML.HTMLDocument
Dim i As Integer
IE.navigate sURL
Do While IE.ReadyState <> READYSTATE_COMPLETE
Application.StatusBar = "WEB sit connecting......"
DoEvents
Loop
Set oDoc = IE.Document
For i = 0 To oDoc.Links.Length
On Error Resume Next
' Remove If for ALL downloads
If UCase(Right(oDoc.Links(i).href, 3)) = "ZIP" Then
'href:
Call savefile(oDoc.Links(i).href)
End If
Next i
Set oDoc = Nothing
Set IE = Nothing
Application.StatusBar = False
End Sub
Sub savefile(sURL As String)
f = GetFileName(sURL)
DoEvents
Application.StatusBar = f & "Downloading..."
returnValue = URLDownloadToFile(0, sURL, ThisWorkbook.Path & "\" & f, 0, 0)
End Sub
Function GetFileName(sText As String) As String
Dim pos As Integer
Do
GetFileName = Mid(sText, pos + 1)
pos = InStr(pos + 1, sText, "/")
Loop While pos > 0
End Function



Original Topic in Chinese:
http://www.vba.com.tw/plog/post/1/283