clean range and import multiple csv, semicolon delimited, from a specific cell

[maurig]

Hi guys,
I'm trying to import multiple csv file into an existing worksheet (sheet1) within an existing workbook (WBTest). All csv files have same structure and have semicolon as delimiter. Csvs don't have header.
The content of the csvs should be imported starting from B6 cell. Before importing I need to clear the range below B6 and for all the columns.
Once all data are imported I must copy formatting and formulas from an existing row (row2).

I'm able to clean the range but I don't get how to merge them in one and how to set semicolon as delimiter. Below the code for cleaning. For now I found a solution using powershell but I would like execute all the steps using a vba script.
Do you have any suggestion on how to import multiple csvs?
Many thanks in advance guys!

Sub DeleteRowsBelow()
    Worksheets("backup").Rows(6 & ":" & Worksheets("backup").Rows.Count).Delete
End Sub

 

[maurig]

I think I've found a solution combining several ideas:
1) delete rows for the specified range
2) import csv
3) apply formats based on the formatting of another row

Let me know if you have any comment or suggestions to improve it

Thank you very much, Maurizio

Sub CollectCSV()

'1 Delete rows   
 Worksheets("yoursheet").Rows(6 & ":" & Worksheets("backup").Rows.Count).Delete


'2 Import CSV
Dim wb As Workbook
Dim wbCSV As Workbook
Dim myPath As String
Dim myFile As Variant
Dim fileType As String
Dim i As Integer, D As Integer



myPath = "yourpath\"

  fileType = "*.csv*"

  myFile = Dir(myPath & fileType)

    Worksheets("yoursheet").Activate
    ActiveSheet.Range("B6").Select


  Do While myFile <> ""
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myPath & myFile, Destination:=ActiveCell)
.Name = myFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

    i = i + 1
myFile = Dir

Selection.End(xlDown).Select
If ActiveCell = Range("A1048576") Then GoTo Done:
ActiveCell.Offset(1, 0).Range("A1").Select

  Loop

Done:
Range("A1").Select

'3 Apply format
Dim rngCopy As Range, rngPaste As Range

With ActiveSheet
    Set rngCopy = .Range(.Range("A1:AA1"), .Cells(1, Columns.Count).End(xlToLeft))

    Set rngPaste = .Range(.Range("A6"), _
                       .Cells(Rows.Count, 2).End(xlUp)).Resize(, rngCopy.Columns.Count)

End With

rngCopy.Copy
rngPaste.PasteSpecial Paste:=xlPasteFormats


Application.CutCopyMode = False

End Sub