bashkaru
asked on
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
Hi bashkaru,
Have a look at the following:
https://www.experts-exchange.com/questions/20988082/How-To-Save-Html-Page-Full-Page-Not-the-URL-Into-Access-DataBase.html
Gary
Have a look at the following:
https://www.experts-exchange.com/questions/20988082/How-To-Save-Html-Page-Full-Page-Not-the-URL-Into-Access-DataBase.html
Gary
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks very much.
and you say it works fine on local files