How to bring in new data from web query while preserving the old

[tommypkoch]

I am trying to bring in data from the web (works) and as is, Excel overwrites the old data with the new. How can I set it up such the Excel brings in the new data and does not overwrite the old?

 

[John_w]

I think you need VBA to change the destination cell, like this:

Sub Macro1()

    Dim destCell As Range
    
    With ActiveSheet
        If .QueryTables.Count = 0 Then
            Set destCell = .Range("A1")
        Else
            Set destCell = .QueryTables(1).ResultRange.Offset(0, 1)     'Either next empty column to the right
            'Set destCell = .QueryTables(1).ResultRange.Offset(1, 0)     'Or next empty row below
            .QueryTables(1).Delete
        End If
        
        With .QueryTables.Add(Connection:="URL;http://www.mrexcel.com/forum/excel-questions", Destination:=destCell)
            .Name = "excel-questions"
            .RefreshStyle = xlInsertDeleteCells
            .AdjustColumnWidth = False
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .Refresh False
        End With
    End With
    
End Sub

 

[tommypkoch]

John - I wondered about VBA but was not sure - will give it a try and let you know if I am successful - am booked today so it will be tomorrow before I will get a chance. I apprecaite your help! Tom

I think you need VBA to change the destination cell, like this:

Sub Macro1()

    Dim destCell As Range
    
    With ActiveSheet
        If .QueryTables.Count = 0 Then
            Set destCell = .Range("A1")
        Else
            Set destCell = .QueryTables(1).ResultRange.Offset(0, 1)     'Either next empty column to the right
            'Set destCell = .QueryTables(1).ResultRange.Offset(1, 0)     'Or next empty row below
            .QueryTables(1).Delete
        End If
        
        With .QueryTables.Add(Connection:="URL;http://www.mrexcel.com/forum/excel-questions", Destination:=destCell)
            .Name = "excel-questions"
            .RefreshStyle = xlInsertDeleteCells
            .AdjustColumnWidth = False
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .Refresh False
        End With
    End With
    
End Sub

 

[tommypkoch]

Hmm,... the VBA code does not seem to do tha trick and there has been a twist in the request. Instead of preserving all the data, is there a way to have the program keep the last trading days' data (CBOT, CME)? I cannot find the data on the web like the futures trading data (current connections).