VBA and Excel texttocolumns

[crslack]

My VBA macro will bring in a flat text file and a separate description of the text file that will describe how to take the fixed width data (no delimiters) and change it to columns.

Excel's texttocolumns functions seems to be the way to go but I need to know how to dynamically set the number of columns. The example below (recorded) shows the basic technique but it assumes three columns. I could have anywhere from 1 to 75 columns.

To be sure I could assume two columns and iteratively "parse" the table but this doesn't seem elegant enough.

I could also bring it in one line at a time but this also seems like a kludge? (In fact it is very slow).

Any ideas?

 

[crslack]

Here is the recorded macro that I forgot to post.

Sub Macro1()
'
' Macro1 Macro

'

'
Selection.TextToColumns Destination:=Range("I2&quot, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(9, 1)), TrailingMinusNumbers:= _
True
End Sub

 

[ETID]

Try creeating a live link to your text file instead...

To create a live link to the text file from within excel,

From excel click Data>Get External Data>New Database Query>....text files* from the list...follow the wizard from there (to select your text file)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access...look for a check box as you step through the wizard)...
after your comfy with this...I would recommend the MsQuery option, there you can set criteria about the data that is returned from your text file.


when data is returned,...you can right click in the data on the sheet and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date text file data comes in (if you set the refresh on open option).

...you can also refresh manually at any time, (or launch a refresh via code.)

 

[ETID]

Only other suggestion I have is ....

On a text file that has the maximum columns expected...

Record the text to columns process as a macro, and instead of using fixed width, select delimited and define as space delimited (check that definition option only) and select "treat consecutive delimters as one".

...then try it on a text various text files.

 

[crslack]

Thank you both. Two good ideas.