Fetch Text From Website

  • Thread starter Thread starter Dileep Chandran
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top