Hi there. I'm using the following code to read in any number of CSV files into a single worksheet and concatenating them together
File_Path = "H:\pathname"
strName = Dir(File_Path & "\" & "XXXXX*.CSV")
Do While strName <> vbNullString
If active_workbook.Name <> strName And strName <> "" Then
Workbooks.OpenText Filename:=File_Path & "\" & strName, startrow:=1, _
DataType:=Excel.XlTextParsingType.xlDelimited, _
TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
Comma:=True, local:=True
Set dataset_workbook = ActiveWorkbook
Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy
active_sheet.Activate
Cells(ActiveCell.SpecialCells(xlLastCell).Row + 1, 1).Select
ActiveSheet.Paste
dataset_workbook.Close
End If
strName = Dir
Loop
It works fine the first time I run it after opening the workbook (I've got 12 rows of data across 3 CSVs) 12 rows are pasted and 1 blank row at the beginning.
The second time I run it it pastes into the worksheet starting from row 14, and then 27 the third time etc.
Does anyone know a way I can make sure it always pastes starting from row 1?
Thanks in advance.
Bookmarks