vba - Combine many CSV files into one Excel document, keep original date format intact -

vba - Combine many CSV files into one Excel document, keep original date format intact -

i have several hundred csv files want combine 1 master worksheet. managed using following:

sub simplexlsmerger() dim booklist workbook dim mergeobj object, dirobj object, filesobj object, everyobj object application.screenupdating = false set mergeobj = createobject("scripting.filesystemobject") set dirobj = mergeobj.getfolder("c:\users\abc\documents\test") set filesobj = dirobj.files 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 csv files, info in column date. has been created using user-form =now() formula.

however, when utilize above macro consolidate, date confuses formatting. alter u.s. , remain in european union format. wish consolidated info remain in original format, appears in original csvs.

is there line can add together macro create possible?

thank you

(n.b. not looking excel solution, solution code above, if possible)

try changing line:

range("a65536").end(xlup).offset(1, 0).pastespecial

...to this:

range("a65536").end(xlup).offset(1, 0).pastespecial paste:=xlpastevaluesandnumberformats

excel vba excel-vba csv