I have the following code which places results from a web query into a QueryTable, starting at cell B2:
The problem is that this is supposed to be a multi-row and multi-column table, but each row of this result is one long string with commas taking up one cell. I want to modify these results and split the string by a comma delimiter and then paste those results into a range of cells starting at B2. Basically I want the Destination to be a two-dimensional array instead of a range of cells. How do I do this?
Code:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & url, _
Destination:=Range("$B$2"))
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.BackgroundQuery = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
The problem is that this is supposed to be a multi-row and multi-column table, but each row of this result is one long string with commas taking up one cell. I want to modify these results and split the string by a comma delimiter and then paste those results into a range of cells starting at B2. Basically I want the Destination to be a two-dimensional array instead of a range of cells. How do I do this?