Solved

How to extract data from a webpage and enter into an Access Database?

Posted on 2014-11-19
4
215 Views
Last Modified: 2014-11-20
I have a Microsoft Access 2010 Database with a table and a form to read data from a webpage.
When I enter a number into the form, the form must have code to open the corresponding webpage and read a range of data from fields in the web page into the table in the database.
I only want the pages as I enter the numbers (SAQA QUAL ID = fugures long). I don't want the whole website as the information may change any time.
Typical page addresses are
http://allqs.saqa.org.za/showQualification.php?id=62322
http://allqs.saqa.org.za/showQualification.php?id=59769 
Note the 5 digit number at the end. So I think that will be simple to just add the number to the first part of the url. But what then?
I want the attach the VBA code to the provided button on the form.
Database is attached
Where and how should I start?
0
Comment
Question by:Fritz Paul
  • 2
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40452638
You can use the function below to download the page and save as, say, "saqa.html"
This file you can attach via "External Data". Select More, HTML Document.
The wizard opens and you will have listed all the tables inside the document.
It is most likely the SAQA4 you will need. Finish the wizard and you the table attached.
However, it is in spreadsheet style so you are on your own how to proceed from here.

To read another page, you can download that page and overwrite the file linked to.
Option Compare Database
Option Explicit

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


Public Function DownloadFile( _
    ByVal strURL As String, _
    ByVal strLocalFilename As String) _
    As Long
  
' Download file or page with public access from the web.
' 2004-12-17. Cactus Data ApS, CPH.

' Usage, download a file:
' lngRet = DownloadFile("http://www.databaseadvisors.com/Graphics/conf2002/2002ConferencePicsbySmolin/images/dba02smolin27.jpg", "c:\happybassett.jpg")
'
' Usage, download a page:
' lngRet = DownloadFile("http://www.databaseadvisors.com/conf2002/conf200202.asp", "c:\dbaconference.htm")

' Returns 0 if success, error code if not.
' Error codes:
' -2146697210 "file not found".
' -2146697211 "domain not found".

' Limitation.
' Does not check if local file was created successfully.

    Dim lngRetVal As Long
      
    lngRetVal = URLDownloadToFile(0, strURL & vbNullChar, strLocalFilename & vbNullChar, 0, 0)
    
    DownloadFile = lngRetVal
  
End Function

Open in new window

saqa.PNG/gustav
0
 

Author Comment

by:Fritz Paul
ID: 40452843
I will try that thanks. Many thanks.
0
 

Author Closing Comment

by:Fritz Paul
ID: 40454471
Thanks Gustav,
I got it in Excel and I know how to read from Excel.
Fritz
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40454474
OK, great.

/gustav
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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