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

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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!

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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top