PDA

View Full Version : Excel 2007 Populate table with web query



DevRichard
11-04-2009, 09:07 AM
Is it possible to populate a table with a web query in Excel 2007?

This code loads a web table into excel

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;link removed due to forum rules - sorry", Destination:=Range _
("$A$1"))
.Name = "tabletest"
.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 = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Now I select the range A1:C4

Home
Styles Format as Table
Choose any style
Where is the data for your table
shows =$A$1:$C$4
click ok

Error message from Excel:
Your selection overlaps one or more external data ranges.
Do you want to convert the selection to a table and remove all external connections?

If I choose Yes I get the desired table, but no longer have the connection.

I want to have a Table which can display the results of a web query which can be refreshed.

TIA