help with dates format and vba code

[thoandros]

Hello everyone. I need some help!
An online platform I use exports .csv files where a column is a date. For some random reason, but I guess because I am located in London and the files are generated in US (so with mm-dd format) some of the dates are recognized as such, whilst others are considered as text. You can see what I mean at row 242 of the file I am attaching.


As I need all dates to be in the number format (44227 for example), I created the following code which splits the column A into day month and year, recombine and get the number in column G.

Please Login or Register  to view this content.

It works just fine.

The problem I have is that it only works if I add the module directly in the csv file and run the macro there, whilst what I want is to start the macro from another file, pick the csv file and let it run the macro. If I do so, the format of the dates remain incorrect, ending up with #value error in the column G.

This is the code I created:

Please Login or Register  to view this content.

I don't know if there is something I can do in the vba itself or it's a setting in the file with the macro perhaps?

Can anyone help me?

Thank you!

[Norie]

You could do the conversion manually by following these steps:

1 Select the column with the dates.

2 Goto Data>Text to columns...

3 Choose Delimited, click Next, click Next.

4 On the 3rd step choose MDY from the Date dropdown in the Column data format section.

P.S. You could do something similar to this if you imported the data from CSV file into Excel rather than opening the file directly.

[mjr veverka]

... something I can do in the vba itself ...

You took on this matter from the most tedious and time-consuming side - use Nori's advice.
In Excel, you have ready-made text import mechanisms, which can additionally be turned into a macro, e.g.:

Please Login or Register  to view this content.