Dear Excel Experts,
Currently, I'm using a vba script to copy content in every sheet1 in csv file into the new worksheet tab next to Sheet1 in master workbook. It supposed to compile all csv file content into one master workbook.
However, I notice that those dates format will automatically change to "m/d/yyyy" format when it detects the figure is less than or same as 12.
eg.
3/12/2015 --> 12/3/2015 (wrong)
5/1/2016 --> 1/5/2016 (wrong)
21/12/2015 --> 21/12/2015 (maintain correct)
I had googled these and seems like all the suggested solutions don't seem to work for me. I tried to record macro to actually cut the column F (where the dates data are placed) while in csv file, change format of column F to be Text, then paste back as Values then copy to master workbook. When I do this manually it works, but when I paste the recorded macro into my vba, it doesn't give me the result as I wanted. I also tried to make column G to convert to TEXT(F2,"d/m/yyyy h:mm:ss AM/PM") and copy down to all the rows, but still it will change the format when comes to master workbook. I have to do this on vba as I need to compile thousands of data rows in hundreds of csv files.
Is there any other way to do this in vba that will work, please? I kinda saw the phrase "Set off date recognition" but couldn't find it in Excel to set it off. I also came across some people mentioned abt doing the text to column that will solve this but I don't see the vba script to do this.
Appreciate your help.
Thank you in advance.
DZ
Currently, I'm using a vba script to copy content in every sheet1 in csv file into the new worksheet tab next to Sheet1 in master workbook. It supposed to compile all csv file content into one master workbook.
However, I notice that those dates format will automatically change to "m/d/yyyy" format when it detects the figure is less than or same as 12.
eg.
3/12/2015 --> 12/3/2015 (wrong)
5/1/2016 --> 1/5/2016 (wrong)
21/12/2015 --> 21/12/2015 (maintain correct)
I had googled these and seems like all the suggested solutions don't seem to work for me. I tried to record macro to actually cut the column F (where the dates data are placed) while in csv file, change format of column F to be Text, then paste back as Values then copy to master workbook. When I do this manually it works, but when I paste the recorded macro into my vba, it doesn't give me the result as I wanted. I also tried to make column G to convert to TEXT(F2,"d/m/yyyy h:mm:ss AM/PM") and copy down to all the rows, but still it will change the format when comes to master workbook. I have to do this on vba as I need to compile thousands of data rows in hundreds of csv files.
Is there any other way to do this in vba that will work, please? I kinda saw the phrase "Set off date recognition" but couldn't find it in Excel to set it off. I also came across some people mentioned abt doing the text to column that will solve this but I don't see the vba script to do this.
Appreciate your help.
Thank you in advance.
DZ
