Hi all Excellers, long time no post (for me anyway).
I have some CSV data (a sample is shown below), that I need to parse into Excel. All except for the 2 date fields, must be formatted as text in order to preserve leading zeros where present.
Now I don't want to have to go through the "Text import wizard" or "Data->Text to columns" each time to select the data types.
So, I set up a simple TextToColumns script that parses the selected data in column A into separate columns as specified above.
So far so good.
BUT (you all knew that this was coming didn't you), often when I paste the data into a sheet, Excel parses it on the commas and doesn't do the formatting.
Sometimes it pastes into column A and allows me to run my TextToColumns script, sometimes is parses and then offers me the chance to use the "Text import wizard", which means I have to go thgrough and specify each columns data type.
I tried recording the paste into sheet (where it auto-parses) and then the use of the Text import wizard, but the resulting script contained just:
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
The Text import part didn't get recorded.
So, does anyone have any idea why I can sometimes get the text pasted intoi a single column and not otherwise?
Following on from that, is it possible for me to always have it pasted in a single column?
And I guess, is it possible to automate the text import wizard operation?
Any help is much appreciated.
I can attach sample sheets if necessary.