web query - pull web page title

L

li3carney

New Member
Joined
Jul 16, 2002
Messages
16
I have a list of URLs - one per cell. How can I use a web query (or anything) to pull in each URLs page title? I'm using Excel 97, but I could also do this on 2000 if need be. Thanks.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Sub myGetWebData()
'Get web data and add it to sheet2 named: "Data"!

Sheets("Data").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.MrExcel.Com", _
Destination:=Sheets(2).[a65536].End(xlUp)(2))
.Name = "Data"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

End Sub
 
Upvote 0
Afraid it's been a while since I took a VB class (and that was VB6). Let's say I have URLs in cells A1-A10. I'd like to be able to grab the page titles from those URLs and put them next to the URLs into cells B1-B10. How do I go about doing this?

-Matt
 
Upvote 0
My code will pull all the data from each web page that the link points to! So you will get a full sheet worth of data for each link, if you add a loop to automatically search each link?

Are you looking to convert a link from it's numbers to it's string name?
Or, are you wanting to pull a line of text off of each page or something?
 
Upvote 0
What I want to do is pull the actual HTML page title, as in:

<html>
<title>
THIS PART, RIGHT HERE.
</title>
 
Upvote 0
Sorry, I forgot to disable HTML. I want the text between the TITLE tags.

HTML: 
[TITLE]
I want this text right here.
[/TITLE]
[BODY]
....
 
Upvote 0
For example, if I had "http://www.thecatsite.com" in cell A1, I would like to be able to pull the page title, which is "TheCatSite.com - Home," into cell B1, and so on.
 
Upvote 0
Each web page may have different strings in different places that indicate its name. To do a text search you will need the exact location of the text you want to locate and report.

The code below will report all the generic info contained in the URL.

To work it go to the cell under your link then run the code:

Sub HlinksA()
'List all of the selected hyperlinks properties!
'Note: Select a cell next to the H-Link & use the cursor to slide into its cell.
'Then run this code to show that links properties!

Dim hyp As Hyperlink

Set hyp = Selection.Hyperlinks(1)
MsgBox "Address ==> " & hyp.Address & vbCr & _
"Name ==> " & hyp.Name & vbCr & _
"Parent ==> " & hyp.Parent & vbCr & _
"Creator ==> " & hyp.Creator & vbCr & _
"Text ==> " & hyp.TextToDisplay & vbCr & _
"Application ==> " & hyp.Application.Value

End Sub

You may be able to modify this code to print the info. rather than display the message and do it automatically for each link?
 
Upvote 0
This code seems to work ok:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> GetWebTitles()
   <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range
   <SPAN style="color:#00007F">Dim</SPAN> Cll <SPAN style="color:#00007F">As</SPAN> Range
   <SPAN style="color:#00007F">Dim</SPAN> IE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
  
   <SPAN style="color:#00007F">Set</SPAN> Rng = Range("A1:A4")   <SPAN style="color:#007F00">'Modify to fit your needs</SPAN>
  
   Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
  
   <SPAN style="color:#00007F">Set</SPAN> IE = CreateObject("InternetExplorer.Application")
  
   <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cll <SPAN style="color:#00007F">In</SPAN> Rng.Cells
      IE.Navigate "about:blank"
      IE.Navigate Cll.Value
      
      <SPAN style="color:#00007F">While</SPAN> IE.ReadyState <> 4
         DoEvents
      <SPAN style="color:#00007F">Wend</SPAN>
      
      Cll.Offset(, 1).Value = IE.document.Title
   <SPAN style="color:#00007F">Next</SPAN> Cll
  
   <SPAN style="color:#007F00">'Quit IE</SPAN>
   IE.Quit
   <SPAN style="color:#00007F">Set</SPAN> IE = <SPAN style="color:#00007F">Nothing</SPAN>


   Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Web Query doesn't load for the website
Replies
1
Views
131
John_w
J
D
  • Question
Importing data from web source
Replies
0
Views
638
Devvy
D
SandsB
  • Question
Deleting Notes when I save my file as a web page.
Replies
4
Views
157
SandsB
SandsB
Anigito
  • Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
401
Anigito
Anigito
sitewolf
  • Question
Dealing with 'schwahs' in web query
Replies
0
Views
134
sitewolf
sitewolf

Forum statistics

Threads
1,214,626
Messages
6,120,602
Members
448,974
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top