Text to columns calculate fixed width

[alfordtp]

I have a .txt file that shows various information in sections about production orders. I can easily identify the sections of information, but the information will usually be just a little different, but in the same relative format.

When I highlight a section and click text to columns, it automatically calculates a "fixed width" array that works perfectly. This is great, except that doesnt work for a macro because that array might be slightly different.

I can use vba to highlight the sections, but need to figure out how to tell excel to calculate the proper fixed width array and apply it before moving on to the next section. I'd even be ok with using vba to highlight the section, open the text to columns window and having the user click ok, but it seems that I have to identify all of the variables for the text to columns function to work.

Any thoughts on how to ask excel to calculate the fixed width?

TIA!!!

 

[Joe4]

I don't know that you can have VBA code that dynamically figures out the correct ending positions each time.
Usually, the VBA is very literal, and you need to tell it exactly where to end (and if you use the Macro Recorder to do it, it is going to "lock in" whatever was determined when recording).

So, if you have files that have the same format every time, it will work great.
But if your file formats change, unless you want to write some pretty, long involved code, asking people to designate the stopping spot for each field, I don't know if that is going to work for you. At least probably not with "Text to Columns".

 

[alfordtp]

Nevermind on this request! Thanks Joe4 for your input. Turns out that even though my data does change, the starting point for each column is actually the same, so I can use that same code for each section.

 

[Joe4]

Nevermind on this request! Thanks Joe4 for your input. Turns out that even though my data does change, the starting point for each column is actually the same, so I can use that same code for each section.

Excellent!
That is the way it usually works with most file formats (thankfully!).