Transpose columns into rows while importing into Excel

Question

I have a text file with many more columns in it than are allowed in Excel, so I would like to transpose the data into columns while importing it into Excel. Any ideas on how I can do this?

Answer 1

EDIT found a better way, so updated code

Probably best to write a VBA macro to do the import.

Here's a starter for you. Note that it will need some adjusting to suit you data.

Sub ImportTranspose()
    Dim fso As New FileSystemObject
    Dim txtFile As TextStream
    Dim col As Long
    Dim dat As Variant
    Dim sh As Worksheet

    Set sh = ActiveSheet
    Set txtFile = fso.OpenTextFile("C:\Tmp\SampleData.txt")
    col = 1
    Do While Not txtFile.AtEndOfStream
        dat = Application.Transpose(Application.Index(Split(txtFile.ReadLine, ","), 1, 0))
        sh.Cells(1, col).Resize(UBound(dat), 1) = dat
        col = col + 1
    Loop

CleanUp:
    On Error Resume Next
    txtFile.Close
    Set txtFile = Nothing
    Set fso = Nothing
End Sub

Notes:

1. It assumes that all rows in the data file are comma seperated
2. Includes no error handling
3. Puts the data in the active sheet, starting at cell A1 and overwrites any existing data
4. Hard coded file name and location - change to suit or add a GetFile dialog