Error in Date Format

[vjvijay88]

Dear sir ,

I have vba code to copy data from multiple csv files from a folder to main worksheet in row , the problem is vba works fine but in date column the first some rows having different format and bottom rows having different format data, i need exact data format as dd/mm/yy ... help me to correct these errors

Sub copyDataFromMultipleWorkbooksIntoMaster()

Dim FolderPath As String, Filepath As String, Filename As String

FolderPath = "D:\RTTrading\Index\"

Filepath = FolderPath & "*.csv*"

Filename = Dir(Filepath)

Dim lastrow As Long, lastcolumn As Long

Do While Filename <> ""
Workbooks.Open (FolderPath & Filename)

lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy

Application.DisplayAlerts = False
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, lastcolumn))

Filename = Dir

Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Deepak]

Hi,

For copying data from multiple csv files..I would suggest to use a native command to do it quickly.
Explained here.
http://forum.chandoo.org/threads/quick-tips.29538/

While date issue is the most common thing which puzzled every excel user due to format change pc to pc.

In ur file some dates got converted to mm-dd-yyyy instead of dd-mm-yyyy due to different date format in csv vs pc.

What I suggest is to first merge all csv to a single csv by above method and then import it to excel rather copying & choose format DMY there.

First do it manually & check.
Code will be simple like as u having one.