How to transpose an excel worksheet having more than 10000 rows into a text file, may by tab delimit
 

*** Sent via Developersdex http://www.developersdex.com ***

How to transpose an excel worksheet having more than 10000 rows into a text file, may by tab delimit
 

so you want the text file to have 10000 columns? How many columns of data
does the worksheet have

--
Regards,
Tom Ogilvy

"Fred zheng" wrote in message
...




*** Sent via Developersdex http://www.developersdex.com ***

How to transpose an excel worksheet having more than 10000 rows into a text file, may by tab delimit
 

Currently, the worksheet has 9800 rows of names and 50 columns of
different data. I would like to have it transposed to have 9800 columns
of names and 50 rows of data. I knew that excel worksheet can not handle
that many columns. So I want it to be transposed into a tab delimited
text file so that I can open it with some other program for
manipulation. Thanks for your help.

Fred


*** Sent via Developersdex http://www.developersdex.com ***

How to transpose an excel worksheet having more than 10000 rows into a text file, may by tab delimit
 

Sub TransposeTabExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim v As Variant
' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", _
"Transpose Tab Exporter")

' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err < 0 Then
MsgBox "Cannot open filename " & DestFile
Exit Sub
End If

' Turn error checking on.
On Error GoTo 0
v = ActiveSheet.Range("A1").CurrentRegion.Value
' Loop for each Column in the array.
For ColumnCount = 1 To UBound(v, 2)

' Loop for each row in the array.
For RowCount = 1 To UBound(v, 1)
' Write current cell's text to file.
Print #FileNum, v(RowCount, _
ColumnCount);
' Check if cell is in last column.
If RowCount = UBound(v, 1) Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a tab.
Print #FileNum, vbTab;
End If
' Start next iteration of RowCount loop.
Next RowCount
' Start next iteration of ColumnCount loop.
Next ColumnCount

' Close destination file.
Close #FileNum
End Sub

--
Regards,
Tom Ogilvy


"Fred" wrote in message
...
Currently, the worksheet has 9800 rows of names and 50 columns of
different data. I would like to have it transposed to have 9800 columns
of names and 50 rows of data. I knew that excel worksheet can not handle
that many columns. So I want it to be transposed into a tab delimited
text file so that I can open it with some other program for
manipulation. Thanks for your help.

Fred


*** Sent via Developersdex http://www.developersdex.com ***

How to transpose an excel worksheet having more than 10000 rows into a text file, may by tab delimit
 

It worked without a glitch. Thank you so much.

Best regards,
Fred Zheng

*** Sent via Developersdex http://www.developersdex.com ***