VBA text to columns formatting issue

[Koen1999]

Hi there,

I am relatively new to VBA so please bear with me. At the moment I would like to write a small program which will perform "Text to column" for me so I do not have to keep doing it manually. The problem I am running into has to do with the dates.
When I manually do the text to columns formula in Excel I get all fields formatted as General, even the dates, and all works fine. However, when I use VBA to do text to columns the dates are being formatted as 'dates'. The problem with this is that the software I use for importing this data reads those dates as numbers, like 44444, thus giving me an error. I would like to know if there is a way for me to use VBA and keep the dates formatted as "General" so my software can read the data.

Code
For me Arrays in the Text to column formula do not seem to do the trick.
I also tried
Range("H1:H1000").NumberFormat = "General"
Range("A1:A1000").TextToColumns , xlDelimited, xlTextQualifierNone, True, , , True, , , , Array(Array(5, 1), Array(6, 1)), ".", "."

Sample data

SE00000000000000,company,SEK,10000000,05-20-2021,05-20-2021,STRING,TIMEstamp,4366352,STRING

Any help is appreciated

Thanks,
Koen

 

[Joe4]

Welcome to the Board!

A few things:

Using Text to Columns, note that there is a "Date" option. If you choose it when recording your Text to Columns import, that argiment will look something like this:

Array(Array(5, 1), Array(6, 3))

where the "3" says treat it as a Date instead of General.

Also note that it is important to understand how Excel treats date. Dates are actually stored as numbers in Excel, specifically the number of days since 1/0/1900.
The date 5/20/2021 is really stored in Excel as 44336.
Dates are really just numbers in Excel, with some sort of date format applied to them.

So if you are seeing a number like 44336 where you expect to see a date, the value is actully correct. You just need to apply a date format to that column after the Text to Columns.

 

[Koen1999]

Welcome to the Board!

A few things:

Using Text to Columns, note that there is a "Date" option. If you choose it when recording your Text to Columns import, that argiment will look something like this:

Array(Array(5, 1), Array(6, 3))

where the "3" says treat it as a Date instead of General.

Also note that it is important to understand how Excel treats date. Dates are actually stored as numbers in Excel, specifically the number of days since 1/0/1900.
The date 5/20/2021 is really stored in Excel as 44336.
Dates are really just numbers in Excel, with some sort of date format applied to them.

So if you are seeing a number like 44336 where you expect to see a date, the value is actully correct. You just need to apply a date format to that column after the Text to Columns.

Hi Joe,

Thank you for your insights!

Now I understand that dates are actually formatted numbers.
Q1: I wonder if a cell in date format can also be stored as a string value?

Q2: Another question, when I export a CSV file, am I exporting the contents of the cell, 44336 or am I exporting the function result, 05/20/2021?
Q2a: Is there a way to manipulate this?

Sorry to bombard you with questions, please take the time to read and answer as you see fit.

Thank you,
Koen

 

[Joe4]

Q1: I wonder if a cell in date format can also be stored as a string value?

Q2: Another question, when I export a CSV file, am I exporting the contents of the cell, 44336 or am I exporting the function result, 05/20/2021?
Q2a: Is there a way to manipulate this?

Q1: Not sure exactly what you mean. Only numbers can be "formatted". Text values cannot be.
You could store dates as Text instead of Numbers/Dates, but then they would be treated as Text and not Numbers/Dates, so you wouldn't be able to do normal mathematical functions on them directly, nor would sorting work properly, i.e. "01/01/2021" would come before "12/31/2020".

Q2: When you export an Excel file as a CSV, the values are exported with whatever formats you are currently showing in Excel (so, kind of a "what you see is what you get" type of thing).

WARNING!
Many people try to use Excel to view the contents of CSV files. If you wish to see what the data in a CSV file TRULY looks like, do NOT use Excel to view it!
Excel actually performs its own automatic conversions on the data when you open a CSV file in Exce (i.e. drop leading zeroes on things like Zip Codes, etc). So it does NOT necessarily show you the data in the CSV, as it truly appears.

If you wish to see what the data in a CSV file truly looks like, open and view the CSV file in a Text Editor like NotePad or WordPad.

One of my biggest pet MS pet peeves is that they set Excel as the default program to open CSV files. So one of the first things I do when I get a new computer is change this, so I set a Text Editor to be the default program to open CSV files.

 

[Koen1999]

Q1: Not sure exactly what you mean. Only numbers can be "formatted". Text values cannot be.
You could store dates as Text instead of Numbers/Dates, but then they would be treated as Text and not Numbers/Dates, so you wouldn't be able to do normal mathematical functions on them directly, nor would sorting work properly, i.e. "01/01/2021" would come before "12/31/2020".

Q2: When you export an Excel file as a CSV, the values are exported with whatever formats you are currently showing in Excel (so, kind of a "what you see is what you get" type of thing).

WARNING!
Many people try to use Excel to view the contents of CSV files. If you wish to see what the data in a CSV file TRULY looks like, do NOT use Excel to view it!
Excel actually performs its own automatic conversions on the data when you open a CSV file in Exce (i.e. drop leading zeroes on things like Zip Codes, etc). So it does NOT necessarily show you the data in the CSV, as it truly appears.

If you wish to see what the data in a CSV file truly looks like, open and view the CSV file in a Text Editor like NotePad or WordPad.

One of my biggest pet MS pet peeves is that they set Excel as the default program to open CSV files. So one of the first things I do when I get a new computer is change this, so I set a Text Editor to be the default program to open CSV files.

Hi Joe4,

Thank you so much. My issue is resolved because of your tip. I was mainly working from Excel and VBA, but now you have opened my eyes and I started using Text editor to look into CSV data. My day is made

Koen

 

[Joe4]

Excellent!
Glad to hear it all worked out for you!