Limiting FieldInfo while using OpenText method

[diakonos1984]

I have a piece of software that exports a bunch of tables into a single text file. I am reading it into Excel 2007 with the OpenText method.
The problem is that the Cable table includes a column called <Cable Size>, and per the American Wire Gauge standards some cable sizes are things like 2/0 (pronounced "two-aught"). Excel thinks it's a date...
Cable Table Bad.PNG
Cable Table Good.PNG

I used the following recorded macro to pull this column in correctly, defining it as Text, but I really don't want to have to define the other column types AND worse, since there are other table below the cable table, it forces the fifth column of those tables to text as well...

Please Login or Register  to view this content.

I have attached a sample text file. What do I need to do to limit the area defined as Text to JUST the cable table and JUST the <Cable Size> column?
Thanks!
Verallia 130901-2 - Cable Test.txt

[patel45]

attach please a file xls with the whole desired result

[diakonos1984]

I have attached the requested Excel file with the hand modified result:
I set E34:E100 to Text. Unfortunately, it messes up rows 41, 42, 96, 97 and 98, and I had to manually set them to 2/0 and 4/0. I would like to set E34:E100 to text on import, BEFORE those rows get messed up.
Additionally, this all has to be macro based, as I import dozens of files that are similar to the provided .txt, though the affected range may change (always in Column E, but beyond that it could start at any row and be any number of rows long).
Verallia 130901-2 - Cable Test.xlsx
Thanks!
Nathaniel

[diakonos1984]

Anyone have a chance to look at this? My alternative is to write a code to look for Feb-00 and replace it with 2/0, which I'm not too excited about doing...

[patel45]

I looked at the two files, but I did understand nothing