Web Queries

[Guest]

Within Microsoft Excel you can leverage the Web Query dialog to retrieve
information from a web pagae and place it in a spreadsheet. I have two
questionsL

1. Can this be done via a function or macro call?

2. If not can the URL associated with the web page by dynamic, allowing you
to reference a cell which would contain the string you want leveraged?
Enabling you to change parameters based upon other cell contents.

 

[Guest]

Hi Rob,

Is something like this what you're after ...

Sub Create_Web_QueryTables()
Dim strCnn As String
strCnn =
"URL;http://investing.reuters.co.uk/Stocks/Quote.aspx?symbol=RTR.L"
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
.Name = "WebQueryTest"
.FieldNames = True
.RefreshStyle = xlInsertEntireRows
.HasAutoFormat = False
.WebFormatting = xlWebFormattingNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "25"
.RefreshOnFileOpen = True
.Refresh
End With
End Sub

In order to make it 'dynamic', change the line strCnn = "URL;http://..." to

strnCnn = "URL;" & Range("A1").Text

and ensure that the full URL is correctly entered into worksheet cell A1
before calling the macro. (You should also change the destination cell
when/if creating a new querytable).

HTH, Sean.