The QueryTables.Add method asks for a destination range on a worksheet. Is it possible to do this and have the destination of the import be an array?
Since i must do a fair amount of manipulation of the data, i can speed up my code if it is in an array. Note that the imported data includes a hyperlink, which is why WebFormatting = xlWebFormattingAll.
Here is the segment of code i am using now.
sWebAddr is a string with the website address and sTargetCell is a string with the destination as a range in A1 format.
With wsT.QueryTables.Add(Connection:="URL;" & sWebAddr, _
Destination:=wsT.Range(sTargetCell))
.Name = sImportRangeName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = iWebTableNumber
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Bookmarks