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
atljarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.