Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Open and save page as XML document

Posted on 2012-03-29
11
Medium Priority
?
510 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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