VB Code to Transpose & Import a Text File

I have space delimited text files that need to be imported into Excel. These files have several hundred columns, but no more than 100 rows. I need to import the files into Excel but cannot due to the column limitation. I am looking for some VB code that will transpose the files on import so the column limitation will not be an issue.

The way you phrase the question, this is impossible (I think). There is no command to "transpose on open".

I do have a few ideas for you though.

Idea#1: use Access. You can then export the data from Access to an Excel format, and decide whether you want it transposed (and keep all of the data together), or cut it up into pieces that will fit on an Excel spreadsheet (and retains its original North/South orientation).

Idea#2: If you are OK with Idea#1, but want something automated and want Excel to be your main engine, then use Excel to Automate Access. You can keep Access hidden and the user never needs to know that they had Access open. I realize that there are not very many living people who are comfortable with Excel, Access and using one to Automate the other. So, don't feel badly if Idea#2 seems a stretch farther than you want to go.

Idea#3: clear you mind of Ideas 1&2. This one is purely Excel. Well, almost. In the VB Editor, look up the "OpenAsTextStream Method". Depending on the version of Excel you're running, you might see a code example that looks like this:

Please Login or Register  to view this content.

This shows you how to read lines from a text file. You will read in one line at a time, parse out the commas, and write whatever is between the commas to rows. Then, move on to the next line in the text stream and the next column in your workbook.

Choose the one that sounds easiest to you. I am partial to #2. If you have Access on your machine.