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
Any help is appreciated
Thanks,
Koen
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