When pasting text w/ VBA it puts it all in 1 column

[WxShady13]

I am automatically extracting data out of a Text file and pasting it into Excel. When I do it puts all the columns from the Text file into Column A and you cannot easily separate them. I need to be able to keep the source formatting from the text file (i.e. Columns) when I paste. Any help is greatly appreciated.

Workbooks.OpenText Filename:=vFileName, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=True, Space:=False, _
    Other:=False, TrailingMinusNumbers:=True, _
    Local:=True

 

[RoryA]

What's the column delimiter in the text file?

 

[WxShady13]

Unfortunately there isn't really one...Here is some sample data but shows you how it is laid out.


12345 9224574 Doe, Jane W 08/27/2018 05:28:39 TSG011-EX

 

[RoryA]

If there isn’t one, how do you expect Excel to know what goes where? Is it fixed width?

 

[WxShady13]

In the .TXT file there are separated columns. I want Excel to keep that formatting when it pastes.

 

[RoryA]

But how would it know that the first two spaces are column delimiters but the third and fourth are not? And what if some names don't have initials for example?