• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Open and save page as XML document

I am trying to open a specific web page that loads an XML file which can take up to an hour or more.   I would like to create a on click function to openthe page, wait for Tito completely load, then save it to my hard drive.

This codeibeleive will open a page.  I've taken it from another question that opens a page and downloads a specific link.


Private Sub DrawingIn_Click()
   Set browser = CreateObject("InternetExplorer.Application")
   browser.Navigate ("www.mywebpage.com")
   browser.StatusBar = False
   browser.Toolbar = False
   browser.Visible = True
   browser.Resizable = False
   browser.AddressBar = False
End Sub
0
atljarman
Asked:
atljarman
2 Solutions
 
danishaniCommented:
What do you mean with loading XML and then saving XML from a specific Webpage?

I am not sure what are trying to accomplish. A little bit more info would be very helpful.
0
 
trungnt8Commented:
0
 
vb_elmarCommented:
If the job is only loading a specific XML web page it is sufficient to use
the "URLDownloadToFile" Function (it takes less computer memory and there's no need using a complete web browser).

The following sample loads the stock webpage "http://exchangerate360.com" and dumps it to a local file "myDump.htm".
Private Declare Function DeleteUrlCacheEntry Lib "wininet" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
Const myURL = "http://exchangerate360.com"

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 Sub Form_Activate()
imname = "myDump.htm"
hardDisk = App.Path & "\" & imname

    DoEvents: myUrl2 = IIf(InStr(1, myURL, "http://"), myURL, "http://" & myURL)
    MsgBox DownloadFile(myUrl2, hardDisk), , "Web site successfully loaded ?"
    MsgBox "Download path :" & vbCrLf & App.Path & "\" & imname
End Sub

Public Function DownloadFile(ByVal URL As String, ByVal LocalFilename As String) As Boolean
    DeleteUrlCacheEntry URL
    DownloadFile = URLDownloadToFile(0, URL, LocalFilename, &H10, 0)
    DownloadFile = Not DownloadFile 'If DownloadFile = 0 Then Success
End Function

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
atljarmanAuthor Commented:
I will try these solutions today when I'm at the office.  I appreciate the quick reply.
0
 
atljarmanAuthor Commented:
dannishani, I have a server page that loads xml in the body of the page from the server.  The lenght of time that it takes depends on how much data is available, which is partially set by variables in the url.

trungnt8, I looked at the solutions posted on that page, but they are focused on Excel and I am applying this to an Access application.  The reference they indicate is needed is not available in Access list of References.

vb_elmar, I've cut and pasted your code into a new form and referenced the url I'm trying to download.  I open the page and the msgbox is "False" and the file is not downloaded.  It happens quite fast, and I'm not sure the page has had time to fully load the data.  Is there a way to tell the code to wait for the page to fully load before downloading?

I've tried this solution:
http://vbnet.mvps.org/index.html?code/internet/dofiledownloadcustom.htm

But it says Internet Explorer can not download the file as the file can not be written to cache.
0
 
atljarmanAuthor Commented:
vb_elmar,

You code seems to work for a static xml document.  I need to add a delay while the page is loading for a dynamically generated xml document that appears within a webpage (e.g., rss feed).

Option Compare Database
Private Declare Function DeleteUrlCacheEntry Lib "wininet" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
'Const myURL = "http://exchangerate360.com"
Const myURL = "http://www.ihs.gov/hpdp/hpdp_rss.cfm"

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 Sub Form_Activate()
imname = "myDump.xml"
hardDisk = "C:\downloads\" & imname

    DoEvents: myUrl2 = IIf(InStr(1, myURL, "http://"), myURL, "http://" & myURL)
    MsgBox DownloadFile(myUrl2, hardDisk), , "Web site successfully loaded ?"
    MsgBox "Download path :" & vbCrLf & "C:\downloads\" & imname
End Sub

Public Function DownloadFile(ByVal URL As String, ByVal LocalFilename As String) As Boolean
    DeleteUrlCacheEntry URL
    DownloadFile = URLDownloadToFile(0, URL, LocalFilename, &H10, 0)
    DownloadFile = Not DownloadFile 'If DownloadFile = 0 Then Success
End Function

Open in new window


I am able to open the page with the code used at the top of the question just fine, but still not able to save it.  It appears that I need to incorporate some way to allow the page to finish loading.  I am not sure if something like this would work or how to incorporate it into vb_elmar's recommendation.

   dteStartTime = Now
   Do While ieBrowser.readyState <> 4
      If DateDiff("s", dteStartTime, Now) > 1000 Then Exit Sub
   Loop

Open in new window

0
 
vb_elmarCommented:
The following VBS code sample shows how to load the web page with iE. After navigating to the page (and waiting 10 seconds) a Dialog pops up asking the user whether he wants to save the web site.

Private Sub Form_Load()

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True: DoEvents

ie.navigate "http://www.ihs.gov/hpdp/hpdp_rss.cfm"

dteStartTime = Now

    Do While ie.ReadyState <> 4
        DoEvents
    Loop
    
Beep
    Do
    DoEvents
    Loop While DateDiff("s", dteStartTime, Now) < 10 'wait 10sec
Beep

ie.ExecWB 4, 1 'Saving the file
'ie.ExecWB 4, 2
    
'EXECWB CONSTANTS :
'Const OLECMDID_PRINT = 6
'Const OLECMDEXECOPT_DONTPROMPTUSER = 2
'Const OLECMDID_SAVEAS = 4
'Const OLECMDEXECOPT_PROMPTUSER = 1

End Sub

Open in new window

0
 
atljarmanAuthor Commented:
This works:

Option Compare Database

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
     
     'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request
     
     'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4
        DoEvents
    Loop
     
    oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
     
     'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
     
     'Clear memory
    Set oXMLHTTP = Nothing
End Function
 
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    SaveWebFile "http://www.ihs.gov/hpdp/hpdp_rss.cfm", "C:\downloads\hpdp.xml"


Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

Open in new window

0
 
vb_elmarCommented:
>>vb_elmar, I've cut and pasted your code into a new form and referenced the url I'm trying
>>to download.  I open the page and the msgbox is "False" and the file is not downloaded.

I pasted the code in Access and it works. -See attached file.
Downloadfile.accdb
0
 
atljarmanAuthor Commented:
Vb_elmar, I will give this a shot over the weekend.   My guess is your solution will work and I will award points as a result.  The code that I pasted works for a page that dynamically loads and depending on the URL variables and users on the site can take several hours to load.

I won't be able to test your code on the computer that  i use to connect to this websiteas I have access 2003 installed.  I will test it out and let you and others know in case others come to this page for a similar solution.
0
 
atljarmanAuthor Commented:
vb_elmar's solution works great.  It did not work for the page that I needed to download.  The page I needed to download is an XML file (RSS) that is dynamically loaded by the server.  I needed functionality to allow the page to fully load before it installs.

vb_elmar's solution work for both Access 2003 and 2010.  The one that I've accepted as the best answer works on Access 2003.  It also needs the Microsoft Internet and Microsoft html (I believe) library references

I want to thank vb_elmar for his hard work on this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now