IT 專業人員的技術資源 >
論壇首頁
>
The Official Scripting Guys Forum!
>
Reading text from webpage/source code into access database, preferably with VBA
Reading text from webpage/source code into access database, preferably with VBA
- Not sure if this is the right place or not, so if it is in the wrong place let me know.
At work, we have a webpage that is hosted offisite. Its a simple site that allows residents to submit service requests for things like down trees, road kill, stuff like that. After submission, there is a tracking page that is always in the same format. The problem here is that data is stored offsite and off of our network, so we have no real way to access it other than through the limited webpage.
Each request is givin a request number, and that number is placed in the url, so the page is http://biglongurlforrequest=requestNumber. I have a hyperlink field on our in house work order system, they just have to put the request number in the field, then double click to access the page. There is no security or loggin in. The problem is I have no idea how to write a webpage to a file or array, and then pull that information from the page. My biggest hurdle is I dont know how to get the info from the file into a field in access.
Example from the source code:
<td align="left" valign="top"><font face="Arial" size="2" color="#000000"><tr><td align="right" valign="top"><font face="Arial" size="2" color="#000000"><b>Comments:</b></font></td>
<td align="left" valign="top"><font face="Arial" size="2" color="#000000">Description of problem.</font></td></tr></font></td></tr>
How would I get the bolded part into a field in a table? If there is a better way to go about this please let me know of other options.
Or can you tell me where to look/research for this stuff. My limit of VBA is mostly looping, if/then, and form operations.
Thank you
解答
- This code in VBScript (very nearly VBS) will collect the webpage's content into a string variable ...
Function GetHTML(sURL)
' Create an xmlhttp object:
With CreateObject("Microsoft.XMLHTTP")
.open "GET",sURL, false
.send
GetHTML = .responseHTML
end with
The single argument is the URL you are trying to retrieve, i.i. "http://biglongurlforrequest=requestNumber ".
Parsing the response requires some knowledge of the exact structure of the returned HTML. For example, does the table that contains the information you seek have a name or is it the only table on the page? If so, that table can be accessed fairly easily and then if the cell number of the table is known it is fairly easy to collect it content as ...
Function FindCell(sHTMLText, ntbl, idx) ' as string
Dim otable
with CreateObject("htmlfile")
.write sHTMLText
.close
set otable = .body.all.tags("table")(ntbl)
FindCell = otable.cells(idx).innerText
end with
End Function ' FindCell
Then the calling routine might look something like ...
Sub GetDescription(sURL)
sPageText = GetHTML(sURL)
sDescription = FindCell(sHTMLText, 0, 3) ' in the first table and the fourth cell in that table
' Use or display the description as desired ...
'
end sub
HTH,
Tom Lavedas- 已標示為解答helllmonkey Wednesday, 25 November, 2009 14:06
所有回覆
- This code in VBScript (very nearly VBS) will collect the webpage's content into a string variable ...
Function GetHTML(sURL)
' Create an xmlhttp object:
With CreateObject("Microsoft.XMLHTTP")
.open "GET",sURL, false
.send
GetHTML = .responseHTML
end with
The single argument is the URL you are trying to retrieve, i.i. "http://biglongurlforrequest=requestNumber ".
Parsing the response requires some knowledge of the exact structure of the returned HTML. For example, does the table that contains the information you seek have a name or is it the only table on the page? If so, that table can be accessed fairly easily and then if the cell number of the table is known it is fairly easy to collect it content as ...
Function FindCell(sHTMLText, ntbl, idx) ' as string
Dim otable
with CreateObject("htmlfile")
.write sHTMLText
.close
set otable = .body.all.tags("table")(ntbl)
FindCell = otable.cells(idx).innerText
end with
End Function ' FindCell
Then the calling routine might look something like ...
Sub GetDescription(sURL)
sPageText = GetHTML(sURL)
sDescription = FindCell(sHTMLText, 0, 3) ' in the first table and the fourth cell in that table
' Use or display the description as desired ...
'
end sub
HTH,
Tom Lavedas- 已標示為解答helllmonkey Wednesday, 25 November, 2009 14:06
- Cool, thank you. Ill give this a try.
