My.ADVISOR.com Sign-In
ID
Password

Member Center / Sign-Up
   
SUBSCRIPTION STATUS
If you are a subscriber to this publication, sign-in to access locked articles. To subscribe or renew go to www.AdvisorStore.com.
Go to Article

DATA INTEGRATION

Get Data from the Internet into Microsoft Access

Here’s the code you need to download a table on a Web page into a table in your Access database.

By Doug Steele


I've often resented the fact that Excel makes it much easier to import data from the Web than Access does. For the purposes of this article, I've hosted a sample Web page here. As figure 1, at the bottom of this page, illustrates, it's nothing fancy, but it might be the sort of table you'd want to import into your application. If you tried to importing this page you might try selecting File > Get External Data > Import, you'd find one of the choices for import is HTML Document. But try putting in the URL above as the file -- you get an error message. It's only if you download the HTML file that you can use the Import HTML wizard (shown in figure 2)

If that's good enough for you, the following code will allow you to download an HTML page so that you can use the Import HTML Wizard:

Sub SaveWebpage(URL As String)
Dim objWeb As Object
Dim intFile As Integer
Dim strFile As String
Dim strHTML As String

  Set objWeb = CreateObject("Microsoft.XMLHTTP")
  objWeb.Open "GET", URL, False
  objWeb.send
  strHTML = objWeb.responseText
  strFile = CurrentProject.path & "\Saved.html"
  intFile = FreeFile()
  Open strFile For Output As #intFile
  Print #intFile, strHTML
  Close #intFile

End Sub

But why must it be that difficult? In this article I'll show you how to import tabular data from a page over the Internet. It's not my intention to build a complete wizard to allow you to import data from any web page. However, I'll show you enough that you'll be able to apply it to your specific situation.

Figure 1: A sample webpage to import -- The data of interest is presented in a table.


Figure 2: The Access Import HTML wizard -- Once the file is accessible, Access does a reasonably good job of importing the data, although you do have to provide a lot of information.

Printer-friendly
page layout

Get Data from the Internet into Microsoft Access

No reader comments ... yet.

    What do YOU think about this topic? Share your advice and thoughts using this form.

    Your Name

    REQUIRED : PUBLIC

    Your E-Mail

    REQUIRED : PRIVATE

    Job, Company

    OPTIONAL : PUBLIC

    City, State, Country

    OPTIONAL : PUBLIC

    Your Web Site

    OPTIONAL : PUBLIC

    Your Comment

    Please help everyone by keeping your comments on-topic, using clean language, and not defaming or making personal attacks.


    Your e-mail address is required, but it will not be displayed to the public or given to anyone. See our Privacy Policy. Comments become visible after they pass our spam filter, and spammers and abusers are permanently blocked. Please report spam or abuse.

    ARTICLE INFO

    Web Edition: 2009 Week 18, Doc #19551

    FREE ACCESS FREE ACCESS

    Keyword Tags: ActiveX, Data Integration, Development, Microsoft, Microsoft Access, Microsoft Excel, Microsoft Office, Microsoft Office Access, Microsoft Windows, Web Development

    ADVISORAMA
    Throughout history, it has been the inaction of those who could have acted; the indifference of those who should have known better; the silence of the voice of justice when it mattered most; that has made it possible for evil to triumph.
    -- Haile Selassie

    Use of this or any other site, content, product or service of Advisor Media constitutes acceptance of Terms of Use.
    Portions copyright ©1983-2010 Advisor Media, LLC. All Rights Reserved.
    Reuse or reproduction of any portion or quantity of Advisor Media's copyrighted content, in any form, for any purpose, requires written permission.
    ADVISOR®, the ADVISOR logo, and other names and logos that incorporate ADVISOR are registered trademarks, trademarks or service marks of Advisor Media, LLC in the United States and/or other countries.
    Other trademarks are used for identification, editorial or descriptive purposes and are the property of their owners.
    Hosted by Prominic.NET Website powered by
    LOTUS SOFTWARE
    oa steed28 posted 2009-4-30 mod 03/18/2010 03:12:29 AM ztdbms/ztdbms
    domino-144.advisor.com my.advisor.com 03/21/2010 03:02:42 AM