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
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
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:[vba]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[/vba]Matt
There's also an API function called URLDownloadToFile
http://msdn.microsoft.com/library/de...loadtofile.asp
Marcster.
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
There is also a free ActiveX component from xStandard (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.Originally Posted by photon_ed
.02 Stan
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
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
Ed
Can you post the code you used?
you will have to add authentication to the Open() method
where user and pwd are the real username, password. If I'm not mistaken that code requires a very specific reference to a server filespec, so I support Norie's request that you post your efforts so far.oXMLHTTP.Open "GET", vWebFile, False,"user", "pwd"
Stan
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
[vba]
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
[/vba]
hum... the program will only work AFTER I manually downloaded the files and NOT before, can someone please advices. Thank you.
yours,
Ed
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
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
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.
Stan
Set reference to Microsoft HTML Object Library、Microsoft Internet Controls
[VBA]
'---------------------------------------------------------------------------------------
' 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
[/VBA]
Original Topic in Chinese:
http://www.vba.com.tw/plog/post/1/283