Text to Columns Wizard - Way to save off positions?

[Horadin]

Hello! I have a somewhat unique problem, I think, as I've been unable to find anything online that addresses my particular situation.

I'm creating a very large VBA project that will need to be able to read in files, no matter the type (xls, csv, txt, etc) or location (HDD, Network, Mainframe, etc). I've already configured the project to read xls and csv files successfully. My issue is reading in text files.

When a text file is read in, I need to be able to split the file into multiple columns. I know that there's a TextToColumns method and wizard that's available. The problem is that they may import the file multiple times, and I don't want the user to have to specify the delimiter or column separations every time. I would like to save off that information and store it so that later when they re-import their file, the Excel app can do it automatically without requesting this information again.

Is there any good way to do this? I'm not opposed to anything - whether that's using the wizard and grabbing the information, building my own input form somehow, or any other ideas that can be suggested. I'm just at a loss for how to approach this one. The only method I've been able to come up with in my head is to have the wizard process their file, and then examine the results of the wizard with how the file originally looked, and somehow determine what their selections were, based on those differences. This just seems really difficult and messy... Seems like there should be a better way.

Help!

[mdmackillop]

Welcome to VBAX,
Using the macro recorder will save the import settings.

[Horadin]

Maybe I need to expand on my VBA project to help assist in the solution.

As I said, this is a pretty complex project. When it's opened, it asks for a file to examine. When that file is provided, it is read and a new workbook is generated specifically for that file. The new workbook will need the ability to read that file at any time to be able to update its internal copy of the data stored there.

That's where the problem comes in. Since the file provided by the user could be of any shape and dimension, I can't just use the macro recorder to get the code for one specific file. I need to be able to dynamically determine the columns on ANY file. Am I out of luck?

I was given another option yesterday of just requesting the file, and then once the new worksheet is generated, ask for a file layout. It's not as elegant as the Text to Columns wizard, but it should work. It would still be better if I could somehow save off the positions. Any other thoughts?

[shrivallabha]

Are they always delimited with some delimiter? (or Fixed width cases would also creep in)

[Horadin]

The files that would be examined, could be completely unique, as they could come from any of a variety of sources. Most will likely be space delimited (each field beginning in a specific column), but they could be delimited by nearly anything.

Thank you for your help!

[hardlife]

may be this is not best way and there is thousand of difficulties,

01/ display user in msgbox some content of text file
02/ user must consdider if there is delimiter

-- if there is delimiter = Yes, then inputbox for used delimiter ..

-- if there is no delimiter and not fixed column width it is probably a trouble ..

another issue is, what format use for every column? (text, date, values, ..)

Good Luck

[Horadin]

I had actually already tried something similar to this, but I think the way you worded it helped me think about it a little differently. If a text file is selected, I can display the file and ask if the file is delimited. If not, then I can drop back to the new workbook and ask for a file layout later. If so, then I can apply the delimiter and read in all the columns and headings from there.

Still not quite what I was hoping for, but I think we're getting closer to the ideal solution!

Thanks!

[Horadin]

BUMPing up my post again. I'm still working on this solution, and am still looking for a better way of pulling in random text files, repeatedly. Any other suggestions?

Thanks!

[GTO]

Hi there,

Certainly no promises from a laymen coder, but it sounds interesting (or maddening). In all fairness to anyone who was to make attempts at a solution though, could you zip some samples up, a few of each, particularly the txt files, and show the wanted outcome? A bit of work I realize, but it sounds a tall task.

Mark

[Horadin]

Not quite sure why I didn't think of providing text file samples sooner....

I've attached 3 different text files. All of them contain the same data, but are in 3 different formats with a txt extension. The first one is really a CSV, so if you open that in Excel in a CSV format, you will see what the data should look like for all 3 files.

This is just a sample. We have hundreds (thousands?) of other files in other formats that could be fed into the program. The program then creates the new spreadsheet that is unique to that one file, and has the ability to re-read that file at any time (in case of updates to it).

Please let me know if there's anything else I can provide to shed more light on the subject. It just really seems that this shouldn't be as difficult as I'm making it (or as Microsoft is making it!).

Thank you everyone for your help!