Excel VBA: Transpose QueryTable

[Hermanator]

Hello,

I'm building an Excel sheet to analyze data contained in a large number (> 200) of text files, each containing 5 values. For instance, I have files: file0001.txt, file0002.txt etc, each containing

VALUE1
VALUE2
VALUE3
VALUE4
VALUE5

I have build a VBA macro which uses the Excel function "Import External Data", to make a QueryTable. This works pretty good. However, the data is now put in columns. So the data of file0001 is put in column A, and the data of file0002 is put in column B etc. Example

file0001 file0002
VALUE1 VALUE1
....

Since the number of files to import is quit big, I run into the limit of 256 columns (I have Excel 2003). So therefore I would like to transpose the date. Ideally, data from each file is put in a row, instead of a column. So that each column would contain all the VALUE1's from all files:

file0001 VALUE1 VALUE2
file0002 VALUE1 VALUE2

I have not found an option to do this. As far as I know, there is no Transpose option when adding a QueryTable. I tried to be smart, and let Excel not "see" the Carriage-Return characters in the textfile, by setting the carriage-return character as a delimiter, however that also didn't work.

Does somebody have a brilliant idea how to solve my problem?

 

[ettienne]

Pull the data into Access and run your queries against the Access database.

 

[SkipVought]

Hi,

You need to store and accumulate your data like this...
[tt]
Val FileName
VALUE1 file0001
VALUE2 file0001
VALUE3 file0001
VALUE4 file0001
VALUE5 file0001
VALUE1 file0002
VALUE2 file0002
VALUE3 file0002
VALUE4 file0002
VALUE5 file0002
...
[/tt]
Then you can use Excel's reporting and analysis features.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Hermanator]

Unfortunately, I am not able to change the format of the data files. They come from a different program, and more people rely on them, so I can not change the output format.
Instead, I have build a little loop around the QueryTable statement, which basically makes a QueryTable for each cell in the proper location. Not a very elegant solution, but it works.

 

[SkipVought]

Well you don't have to CHANGE what's there.

But you could ADD what you so desperately need to make your task possible.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]