vba - Overwrite Excel 2016 worksheet data with data from CSV file

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?

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?

Share Improve this question asked Jan 31 at 13:46 w2kpro 678 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 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

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

vba - Overwrite Excel 2016 worksheet data with data from CSV file - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)