Date format changed after csv files imported to current excel workbook

[Jason4125]

Greetings all.

I am a VBA newbie, currently studying and working on a personal project importing multiple .csv documents into my current workbook with their own different sheet, but I'm stuck at a stage where after csv files been imported to my current workbook, some dates are converted to mm/dd/yyyy, while their original formates are dd/mm/yyyy in csv. Also, some of the dates changed to 'general' type. As I am using dd/mm/yyyy format and aim to run date range filter, it always gave me incorrect outcome. I have now located the issue and have no idea how to fix them.

After I run the followling macro, it imports my selected csv files and merge them with my current workbook, and the flexibility is my greatly prefered. It just the date format that confusing me. I have attached file1.csv which is one of my original file.

Currnet VBA I am using as follows.

Please Login or Register  to view this content.

After I run the macro, the dates becomes totally different as what shown on csv file. see attachment 'Screen Capture' (list on the left side are incorrect data, left aligned are general type and right aligned are mm/dd/yyyy, and list on the right side are original data, which are what I wantted).

Hope I have explained my confusion.

Please kindly advise how can I fix the problem. Any help would be greatly appreciated. Thank you

[Keebellah]

Hi, welcome to the forum,
I suggest you read the rules and see how to mark code as code in your text.
I haven/t looked at your file but date format differences occur when the system settings differ. csv files are text files so make sure that you configure the date fields as date when reading them in Excel, You can do this the moment you read the csv file.
If you start over and record a macro to import the csv file into an existing sheet, you will get the dialog in Excel and there you can set the format for the columns as you go, after you've done this look at the recorded code and you will see that the date columns have another type definition as the cells formatted as General or Integer or whatever.
If I would import your csv file on my system with Dutch settings then the date will look different too and maybe even incorrect, 11/09/2018 which I would interprest at September 11 2018 could become November 9 2018, this would happen with all dates where the day is less than 13

[Keebellah]

I recorded a macro doing that

Please Login or Register  to view this content.

As you can see the first column is set as a date column, that is what the 4 stands for
I get the same result you did so I honestly think it's a question on date settings.

[Vraag en antwoord]

Without error handling

Please Login or Register  to view this content.

[Keebellah]

@Vraag en antwoord: What about the date settings? There is no issue that requires error handling

[FDibbins]

Jason, welcome to the forum

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Jason4125]

Hi FDibbins,

Apologize for the oversight, was too rushed to get things sorted as I have been stuck here for a while, and it's my first time posting on forum. Thank you for point out. I should have been more careful and read through the rules.

[Jason4125]

Dear Vraag,

Thank you so much for your help. I have tried your codes, it's very helpful and working perfectly fine, but it led me to another problem, all of my original file names are exceeded 31 characters, which excel does not allow. Could you please advise how to add related code to change each file name after the import? e.g importfile1, importfile 2 and so go.

Also, could you please kindly write a brief note teaching me what does this code mean?

Please Login or Register  to view this content.

I am very keen to learn VBA. Much appreciated for your help.

Thank you

[Keebellah]

Hi Jason,
I like the Office Version you show in your profile 1830? New one for me
Excel allows any length of filenames but I and thinking you mean Worksheet Tabs.
That is true, the Sheet's tabs are limited to 31 characters.

What I did is the following using a code like shown here

Please Login or Register  to view this content.

The only thins is that you will need to add a routine to check if the tab name already exists, since if you have more than one file where the first 31 characters is the same then you will need to add a counter.
You could also do this with an On Error Trap

Please Login or Register  to view this content.

If you get stuck, just let us know

[Vraag en antwoord]

@Keebellah,

Please Login or Register  to view this content.

Is a form of error handling that I meant
And there are others that you have not mentioned, for example invalid characters in a sheet name.

[Keebellah]

Yes, I have some code for that too and also for the worksheet names to number them if the left 31 are the same