Overwrite Excel 2016 worksheet data with data from CSV file

Question

I have an Excel 2016 (xlsm) worksheet called Data, which has conditional formatting that checks stock price data for validity. The headers are Ticker, Date, Open, High, Low, Close and Volume. The header starts at A3. The data is downloaded in the form of a CSV file which has to be copied and pasted over the existing data. I have tried to import the new data using Excel's "Get External Data" and also VBA code, Both methods insert the new data at A3, but the existing data is not overwritten and gets moved to the right. The VBA code I use is as follows:

Sub ImportCSVFile()
    Dim wrkSheet As Worksheet, mrfFile As String
    Set wrkSheet = ActiveWorkbook.Sheets("Data")
    mrfFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
    With wrkSheet.QueryTables.Add(Connection:="Text;";" & mrfFile, Destination:=wrkSheet.Range("A3"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimited = True
    .Refresh
    End With
End Sub

Is there any way to modify the code so that data is overwritten and not right-shifted?

Answer 1

Try this; (you need to add the RefreshStyle property)

Sub ImportCSVFile()
    Dim wrkSheet As Worksheet, mrfFile As String
    Set wrkSheet = ActiveWorkbook.Sheets("Data")
    
    mrfFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
    
    With wrkSheet.QueryTables.Add(Connection:="Text;" & mrfFile, Destination:=wrkSheet.Range("A3"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .RefreshStyle = xlOverwriteCells
        .Refresh
    End With
End Sub