Solved

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

Posted on 2014-11-19
4
225 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
[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
  • 2
  • 2
4 Comments
 
LVL 50

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 50

Expert Comment

by:Gustav Brock
ID: 40454474
OK, great.

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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 …

726 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