PDA

View Full Version : vba webqueries (?)



gdilorenzo
01-28-2008, 03:40 AM
hi guys!
Unfortunately i have not more knowledge about integration between access and vba.
This is my problem:

i' ve an excel file with some web hyperlinks (one per row). I need a vba script that checks if in the web pages related to hyperlinks (i've heard it's possible with webqueries but i don't know what webqueries are) is founded the word "hello" and print the number of times in another column
Is it possible to obtain that? Have you a script doing that?
Thx a lot in advance!!!

Best regards
Giovanni

Oorang
02-18-2008, 04:03 PM
Would this be what you are referring to? http://www.jkp-ads.com/articles/WebQuery.asp

Or were you thinking something like this?


Option Explicit
'Disclaimer, this is just kludged together from all api examples, you will
'want to review, understand and improve it.

Const INTERNET_OPEN_TYPE_DIRECT = 1
Const INTERNET_FLAG_RELOAD = &H80000000


Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As _
Long) As Integer

Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, _
ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As _
Long) As Integer

Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" _
(ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As _
String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext _
As Long) As Long

Private Sub Test()
Dim strVal As String
strVal = GetSite("http://en.wikipedia.org/wiki/Hello")
MsgBox UBound(Split(strVal, "hello", compare:=vbTextCompare)) & " instances found"
End Sub


Private Function GetSite(sURL As String) As String
Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long
'Create a buffer for the file we're going to download
sBuffer = Space(100000)
'Create an internet connection
hOpen = InternetOpen(vbNullString, INTERNET_OPEN_TYPE_DIRECT, vbNullString, _
vbNullString, 0)
'Open the url
hFile = InternetOpenUrl(hOpen, sURL, vbNullString, ByVal 0&, _
INTERNET_FLAG_RELOAD, ByVal 0&)
'Read the first 1000 bytes of the file
InternetReadFile hFile, sBuffer, 100000, Ret
'clean up
InternetCloseHandle hFile
InternetCloseHandle hOpen
'Show our file
GetSite = sBuffer
End Function