Solved

Open and save page as XML document

Posted on 2012-03-29
11
459 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
Comment Utility
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
Comment Utility
0
 
LVL 17

Expert Comment

by:vb_elmar
Comment Utility
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
 

Author Comment

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

Author Comment

by:atljarman
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:atljarman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now