web search

[RIck]

I would like to create a simple macro that will go to
several/many web sites and search for some given text. If
found, I would like to save the text of that web page, or
just the URL to that page.

Here is an example: Given a URL, like
http://www.scooby.com/monthtext.cfm?id=2154, I would like
to input 2 numbers, say 2154 and 2231, and also say, 3
strings of text, like "timely" "fat" "house". So the macro
will go to each URL,
http://www.scooby.com/monthtext.cfm?id=2154,
http://www.scooby.com/monthtext.cfm?id=2155,
http://www.scooby.com/monthtext.cfm?id=2156, etc. up to
http://www.scooby.com/monthtext.cfm?id=2231. And at each
site search for the 3 text strings given. IF any text
found, it would save the web page or the URL. If it finds
the text it continues on until it goes thru all the URLs.
Thanks for any ideas or help, Rick

 

[Don Guillett]

That's not too hard with starting number and ending number if contiguous. If
not, then a list.But, can you give an example url of where the desired text
IS found.

 

[Guest]

Yes the numbers are contiguous.
But I really can't give out the true URL... for privacy
reasons... sorry.
Could you give me some ideas, using the example URL
www.scooby.com/monthtext.cfm?

Rick

 

[Don Guillett]

This should get you going

Sub GetInfo()
Application.ScreenUpdating = False
Set datasheet = Sheets("nameasheettogetdata")
For i = 2154 To 2256
myurl="http://www.scooby.com/monthtext.cfm?id="&i
With datasheet.QueryTables.Add(Connection:="URL;" & myurl & "",
Destination:=datasheet.Range("A5"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.WebFormatting = xlWebFormattingNone
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Put your find and copy code here

Next
Application.ScreenUpdating = True
End Sub

 

[rick]

thanks,
let me work with this for a bit.
Rick

-----Original Message-----
This should get you going

Sub GetInfo()
Application.ScreenUpdating = False
Set datasheet = Sheets("nameasheettogetdata")
For i = 2154 To 2256
myurl="http://www.scooby.com/monthtext.cfm?id="&i
With datasheet.QueryTables.Add(Connection:="URL;" & myurl & "",
Destination:=datasheet.Range("A5"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.WebFormatting = xlWebFormattingNone
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Put your find and copy code here

Next
Application.ScreenUpdating = True
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)



.

 

[rick]

-----Original Message-----
This should get you going

Sub GetInfo()
Application.ScreenUpdating = False
Set datasheet = Sheets("nameasheettogetdata")
For i = 2154 To 2256
myurl="http://www.scooby.com/monthtext.cfm?id="&i
With datasheet.QueryTables.Add(Connection:="URL;" & myurl & "",
Destination:=datasheet.Range("A5"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.WebFormatting = xlWebFormattingNone
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Put your find and copy code here

1. So the above is to put the text of the web page into
Cell A5?

2. If I try to run this, it gives me a syntax error and
hilites this:
With datasheet.QueryTables.Add(Connection:="URL;" & myurl & "",

-- Rick

 

[Don Guillett]

It could be a problem if your url actually has the .cfm as the
extension. I have had problems with that in xl97 but not xl2002.???

I assume that you did not have any red line errors in the vba code sheet
such as a line wrap
I assume that you named a sheet say DATA and used that in the set
datasheet=sheets("DATA")
I assume you used your actual url with the quotes in the myurl=
I don't if I can be of much more help without the actual which you say you
cannot provide.

 

[rick]

Now it is doing better, but gives an error:
Object doesn't support this method or syntax ... and
hilites in yellow this line:
..WebFormatting = xlWebFormattingNone

I am using xl97.
Rick

 

[Don Guillett]

I don't think that was supported until later, so just comment out the line
by putting an ' before it. Or, delete.

 

[rick]

It is working now! thanks so much, Don.
Two questions:
1. Is it possible to send the data/text to sell A5 in a
"values only" way... like when you do a pasteSpecial
"values only" ... or "text". This is not absolutely
necessary.

2. Do you have a suggestion for a simple IF THEN that
searches a range, say A5:A10, for some text and then if the
text is found switches to a different sheet, say,
"foundsheet" and pastes it there.

You have been so helpful, thanks.
Rick

---------------------------------------------

 

[Don Guillett]

So that I don't spend time doing what you don't want, can you send a small
sample with EXACTLY what you want.
Send to the address below.