open text file and transpose using macro

Microsoft Office 2003 basic edition
December 23, 2009 at 01:22:00
Specs: Windows XP

I'm no expert in macro but appreciate any help.
I'd wished to open or import a text file. It has range of up to 10,000 and row to up to 200. I'm using excel 2003 at office.
I've tried import the data using text import wizard (try using excel 2007). However not all column can be displayed in step 3.

Is there anyway I could just open the text file and all data will be transposed (using macro)?


See More: open text file and transpose using macro

Report •

December 23, 2009 at 10:07:55
re: open the text file and all data will be transposed

What do you mean by "transposed"?

Definitions of transposed on the Web:

* converse: turned about in order or relation; "transposed letters"

* transpose - permute: change the order or arrangement of

Report •

December 23, 2009 at 10:56:29
Thanks for the concern.
The text file is like a delimited data in sequence (like *.csv, but the separation of data is tab format, not comma) and the arrangement in column sequence which if opened it will exceed more than 1000 column (excel 2003 only 256 column, thus the rest of the data will be truncated).
Tried with the Import Wizard also not a best option because in Step 3 the field cannot show the whole column.

Report •

December 23, 2009 at 11:55:31
Assuming you were able to paste your data into a WorkSheet, what do you want it to look like once that's done?

In other words, since the data is "up to 10,000" columns wide and up to 200 rows, what are you expecting to see once the data is in the worksheet? We know it can't be 10,0000 columns by 200 rows, so what are you willing to accept?

The code below will allow you to access the clipboard from VBA and store the copied data in a variable. Once there, you can use various text functions to manipulate the data.

I opened a text file manually, selected all of the text, copied it and then ran the following code.

As written, the code will put each individual character, including the tabs, into its own individual cell, A1 through IV1 then A2 through IV2, etc.

Sub WideData()
'Get data from clipboard
Dim DataObj As New MSForms.DataObject
    Dim cbText As String
'Store text in cbText variable
     cbText = DataObj.GetText
'Put each character into a cell
 For numChr = 1 To Len(cbText)
  nxtCell = nxtCell + 1
  Cells(nxtCell) = Mid(cbText, numChr, 1)
End Sub

P.S. In order to use the DataObject in your code you must set a reference to Microsoft Forms 2.0 Object Library in the VBA editor.

Tools...References...Check Microsoft Forms 2.0 Object Library

Report •

Related Solutions

December 24, 2009 at 06:45:29
Appreciate the help. Tried your code but the result is not quite same. I've upload some sample file - the original and the transposed. The data have smaller column value. Pls do see if not a burden. Thanks.

Report •

Ask Question