Solved

Open and save page as XML document

Posted on 2012-03-29
11
484 Views
Last Modified: 2012-04-05
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
Comment
Question by:atljarman
11 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 37785430
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
 
LVL 5

Expert Comment

by:trungnt8
ID: 37785463
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 37785718
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:atljarman
ID: 37786309
I will try these solutions today when I'm at the office.  I appreciate the quick reply.
0
 

Author Comment

by:atljarman
ID: 37787522
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
 

Author Comment

by:atljarman
ID: 37788546
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
 
LVL 17

Expert Comment

by:vb_elmar
ID: 37789118
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
 

Accepted Solution

by:
atljarman earned 0 total points
ID: 37789215
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
 
LVL 17

Assisted Solution

by:vb_elmar
vb_elmar earned 500 total points
ID: 37789444
>>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
 

Author Comment

by:atljarman
ID: 37790874
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
 

Author Closing Comment

by:atljarman
ID: 37810105
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question