Read data from a internet html page into a MSaccess Table

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

bashkaruAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
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
 
rockiroadsCommented:
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
 
tbsgadiCommented:
0
 
bashkaruAuthor Commented:
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
 
bashkaruAuthor Commented:
thanks very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.