0

for example, following query will create a new column A and the data from A column will now be in Column B

With ActiveSheet.QueryTables.Add(Connection:="URL;" & u, _
Destination:=Cells(14, 8))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    .AdjustColumnWidth = False
End With

is it possible that the destination of web query remain fixed lets say A1

Jatin Gera
  • 814
  • 1
  • 11
  • 25

1 Answers1

0

Yes. Add .RefreshStyle = xlOverwriteCells inside your With clause. This will force the external data to A1 instead of adding a new column.

Omer Greenwald
  • 758
  • 7
  • 20
  • i have updated the code you can see above, however it is still not working excel always inserts a new column – Jatin Gera Feb 11 '16 at 12:18
  • I suggest you add only that one row to your previous code. also change destination property to range($A$1). it worked for me on excel 2010. no column was added, the new data overwrites whatever is on cell a1 – Omer Greenwald Feb 11 '16 at 13:43