Combine many CSV files into one Excel document, ke

Question

I have several hundred CSV files that I want to combine into one master worksheet. I managed to do this using the following:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")


Set dirObj = mergeObj.Getfolder("C:\Users\abc\Documents\Test")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)


Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate


Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

In my CSV files, the information in column A is the date. It has been created using a user-form with the =NOW() formula.

However, when I use the above macro to consolidate, the date confuses its formatting. Some change to U.S. and some stay in EU format. I wish for the consolidated info to remain in the original format, as it appears in the original CSVs.

Is there a line I can add into my macro to make this possible?

Thank you

(N.B. I am not looking for an excel solution, but a solution for the code above, if that is possible)

Answer 1

Try changing this line:

Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial 

...to this:

Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats