Delete contents prior to import

[tyroneclark]

Hi,

I have created an import for a CSV fil, which is working fine - apart from the issue I am facing where the contents being imported are being added to the list already present, I need this to amend the entire range, so to delete the information held with in the sheet (starting from row 10) then importing the new information.

Please let me know if you need further clarification on this...

Private Sub CommandButton1_Click()
 Dim csvFileName As Variant
    Dim destCell As Range
    
    Set destCell = Worksheets("RoutePlanReport").Cells(Rows.Count, "A").End(xlUp).Offset(10)
    
    csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
    If csvFileName = False Then Exit Sub
    
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=destCell)
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    destCell.Parent.QueryTables(1).Delete
End Sub

 

[Kamolga]

Maybe...

Private Sub CommandButton1_Click()
 Dim csvFileName As Variant
    Dim destCell As Range
    
    Set destCell = Worksheets("RoutePlanReport").Cells(Rows.Count, "A").End(xlUp).Offset(10)
    
    csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
    If csvFileName = False Then Exit Sub
   
 [COLOR=#0000ff]Rows("10:999999").ClearContents[/COLOR]

    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=destCell)
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    destCell.Parent.QueryTables(1).Delete
End Sub

 

[tyroneclark]

Works like a charm - with a little adjustment.

Thanks for the help!

 

[Kamolga]

You are welcome