web query - pull web page title

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

li3carney

New Member
Joined
Jul 16, 2002
Messages
16
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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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?
 

li3carney

New Member
Joined
Jul 16, 2002
Messages
16

ADVERTISEMENT

What I want to do is pull the actual HTML page title, as in:

<html>
<title>
THIS PART, RIGHT HERE.
</title>
 

li3carney

New Member
Joined
Jul 16, 2002
Messages
16

ADVERTISEMENT

Sorry, I forgot to disable HTML. I want the text between the TITLE tags.

HTML:
[TITLE]
I want this text right here.
[/TITLE]
[BODY]
....
 

li3carney

New Member
Joined
Jul 16, 2002
Messages
16
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.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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?
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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>
 

Forum statistics

Threads
1,148,216
Messages
5,745,440
Members
423,952
Latest member
EduardoM

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
Top