Solved

Read data from a internet html page into a MSaccess Table

Posted on 2009-03-30
5
253 Views
Last Modified: 2012-05-06
i have airport database in msaccess. what i want is the code should open the webpage (http://en.wikimapia.org/<airport_Name.html)) if it has got a coordinate and a height above sea level value read it and update the access table database. the code given below works fine with a html page saved in the hard disk, but when directed to a web page it says bad file name or number. why is that so?  also tell me how can i simplify the above code
Const ForReading = 1, ForWriting = 2
 

Dim fso, MyFile, textL, fsr, rep, rep1, rep2, ctl, str, HTTP
 

Dim vc As Object

Set vc = CurrentDb.OpenRecordset("SELECT DISTINCT(ID), AIRPORTNAME, FIELD16 FROM AIRPORT")
 

    A = vc.RecordCount

        'vc.MoveFirst

        'For I = 1 To A

    IDN = vc.ID

    airn = vc.AirportName

    f16 = vc.Field16

    bn = airn

        sURL = "http://en.wikipedia.org/wiki/Houari_Boumedienne_Airport.htm"

        

    Set fsr = CreateObject("Scripting.FileSystemObject")

    Set str = fsr.openTextfile("C:\Documents and Settings\user\Desktop\testHTTP.txt", ForReading)

    Set HTTP = fsr.openTextfile("C:\Documents and Settings\user\Desktop\testHTTP.txt", ForWriting, True)

    

    Dim ie As Object

    Dim path As String

        Set ie = CreateObject("InternetExplorer.Application")

            ie.navigate sURL

            'FollowHyperlink sURL, , False, True

                'ie.navigate f16

                While ie.busy

                    DoEvents

                        Wend

   'ie.Visible = True

        Open "http://en.wikipedia.org/wiki/Houari_Boumedienne_Airport.htm" For Input As #1

       

 '-------------test code--------------------------------------

With HTTP

        Do While Not EOF(1)   ' Loop until end of file.

Line Input #1, str    ' Read line into variable.

HTTP.writeline str

'Debug.Print str    ' Print to the Immediate window.
 

'--------test code for inside string-------------

'With strn

Set strn = fsr.openTextfile("C:\Documents and Settings\user\Desktop\testHTTP.txt", ForReading)

'    Do Until .atendofstream

'        str = .readline

            s = InStr(1, str, "params=", vbTextCompare)

'Debug.Print s

If (s > 0) Then

    COR = Mid(str, s + 7, 30)

        le = Len(COR)

 

            Set rep1 = fsr.openTextfile("C:\Documents and Settings\user\Desktop\testCOR.txt", ForWriting, True)

                rep1.writeline COR

                    Debug.Print COR

           

            Set rep2 = fsr.openTextfile("C:\Documents and Settings\user\Desktop\testHTTP.txt", ForReading)

                'With repp

                    'Do Until .atendofstream

                        'cor = .readline

                            c1 = InStr(1, COR, "N", vbTextCompare)

                                C2 = InStr(1, COR, "E", vbTextCompare)

'End If

'--------test code for inside string----------------
 

'---------------------test to check the coordinates---------------------

    If (c1 > 0) Then

        CO = Left(COR, c1)

            'CO = Replace(CO, "_", "", 1, -1, 1)

                Debug.Print CO

                    End If

'--------------trace the under score symbol (_) in the 1st coordinate--------------------

               US = InStr(1, CO, "_", vbTextCompare)

               US1 = Left(CO, US - 1)

               Debug.Print US1

               US = Mid(CO, (US + 1), (Len(CO)))

               CO1 = US

               

               US = InStr(1, US, "_", vbTextCompare)

               US2 = Left(CO1, US - 1)

               Debug.Print US2

               US = Mid(CO1, (US + 1), (Len(CO)))

               CO2 = US

               

               US = InStr(1, US, "_", vbTextCompare)

               US3 = Left(CO2, US - 1)

               Debug.Print US3

               

'--------------trace the under score symbol (_) in the 1st coordinate--------------------

   If (C2 > 0) Then

        CA = Mid(COR, (c1 + 2), (Len(COR)))

            COR = CA

                End If

                

                CA1 = InStr(1, COR, "E", vbTextCompare)

        If CA1 > 0 Then

            CA = Left(CA, CA1)

                Debug.Print CA

        End If

        

        

'--------------trace the under score symbol (_) in the 2ND coordinate--------------------

               VS = InStr(1, CA, "_", vbTextCompare)

               VS1 = Left(CA, VS - 1)

               Debug.Print VS1

               VS = Mid(CA, (VS + 1), (Len(CA)))

               CA1 = VS

               

               VS = InStr(1, VS, "_", vbTextCompare)

               VS2 = Left(CA1, VS - 1)

               Debug.Print VS2

               VS = Mid(CA1, (VS + 1), (Len(CA)))

               CA2 = VS

               

               VS = InStr(1, VS, "_", vbTextCompare)

               VS3 = Left(CA2, VS - 1)

               Debug.Print VS3

Open in new window

0
Comment
Question by:bashkaru
  • 2
  • 2
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24017256
not sure what you are trying to say with directed to web page. What I see in your code is to read the webpage contents and save it locally
and you say it works fine on local files
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017300
0
 

Author Comment

by:bashkaru
ID: 24025915
Hi RockiRoads, what i mean is i am able to read a html page saved in the hard disk, but when the path is directed to a page on the internet (www) then it says bad file name or number. why is that so?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24026279
thats because you cant open up a webpage like that

If u wanted to get the text from the html, either use InetCtls, Gary has given you an example or using your existing code with the IE object, get the text like this

    Dim docHTML As HTMLDocument
    Dim sHtml as String


    Set docHTML = appIE.Document
    sHtml = docHTML.Body.innerHTML


You add this after your navigate

            ie.Navigate sURL
            'FollowHyperlink sURL, , False, True
                'ie.navigate f16
            While ie.Busy
                DoEvents
            Wend

    Set docHTML = appIE.Document
    sHtml = docHTML.Body.innerHTML


And to write it to your file, dont do this

        Open "http://en.wikipedia.org/wiki/Houari_Boumedienne_Airport.htm" For Input As #1
        Do While Not EOF(1)   ' Loop until end of file.
Line Input #1, str    ' Read line into variable.


but just do this

HTTP.WriteLine sHtml


Now the difference between using IE and InetCtls to load the page is that the latter does not open up a browser to get the text so would be quicker


0
 

Author Closing Comment

by:bashkaru
ID: 31564253
thanks very much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

932 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