VBA - Update existing excel file with query

Question

I have an excel file and used this code to import data.

https://www.youtube.com/watch?v=sQIImQbEO_Q

Unlike this example my Datafeeds sheet has data already that is formatted in a particular way. If I rerun the Macro, the existing data shifts to the L1 (That is where the second csv ends) and new data is written from A1.
1) I would like to modify the code to replace the existing one overwrite it. 2) Also the existing one has been formatted in a particular way. I wish to retain the formatting.
How can I best modify my code to do this?

This is the code:

Sub AutomateImport()
For rep = 4 To 16

Dim file_name As String
Dim row_number As String
Dim output_sheet As String

file_name = Sheets("Admin").Range("B" & rep).Value
output_sheet = Sheets("Admin").Range("C" & rep).Value
row_number = Sheets("Admin").Range("D" & rep).Value
Sheets(output_sheet).UsedRange.ClearContents

    With Sheets(output_sheet).QueryTables.Add(Connection:="TEXT;" + file_name, Destination:=Sheets(output_sheet).Range("$A$" + row_number))
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Next rep

MsgBox "Done"
End Sub

Something needs to clear that range so it can insert into the existing structure. I have not tried to tackle this one, but [let us know if this solution helps](http://stackoverflow.com/questions/10220906/how-to-select-clear-table-contents-without-destroying-the-table). — Jimmy Smith, Oct 17 '16 at 13:16
@Jimmy Smith Thanks for the response. Used this to clear existing contents. Sheets(output_sheet).UsedRange.ClearContents Please check edits! But how do I retain the existing format? — amazingCodingExperience, Oct 17 '16 at 13:52

VBA - Update existing excel file with query

0 Answers0