Fetch Text From Website

[Dileep Chandran]

Hi All,

How can I get the news description from the below mentioned link to a
particular cell (say sheet1, Range("A1") using VBA. Is this possible??

http://www.commercialpropertynews.com/cpn/article_display.jsp?vnu_content_id=1003559988

Dileep

 

[Incidental]

Hi Dileep

I'm not used to this sort of thing but below is the code recorded by
excel while i got the data from the site you gave using a web query
it's very scrappy but it works and should give you an idea of what to
do.

you might want to search help for importing data into excel as well


Sub RunMyCode()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.commercialpropertynews.com/cpn/
article_display.jsp?vnu_content_id=1003559988" _
, Destination:=Range("A1"))
.Name = "article_display.jsp?vnu_content_id=1003559988"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

S

 

[Dileep Chandran]

Thank you for the suggetion. But how can alter this code to get data
from another page without manually altering the URL in the code?

I need to get data from multiple web pages.

-Dileep

 

[Tim Williams]

Take the code sampleprovided, add a parameter "URL" and then use that to
pass in the URL for each page.

Sub RunMyCode(sURL as String)
With ActiveSheet.QueryTables.Add(Connection:= "URL;" & sURL,
Destination:=Range("A1"))
'etc....



You don't say where your list is kept, so it's difficult to suggest details
of how you might loop through the URL's.

Tim

 

[Dileep Chandran]

Tim,

My URL's are kept in Sheet1 Range("A2:A20"). Any suggetions??

-Dileep

 

[Tim Williams]

Please quote when replying - it makes everyone's life easier.


Try this:

'********************
Dim c as range

for each c in Sheet1.Range("A2:A20")
RunMyCode(c.value)
next c