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?


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

    On Error Resume Next
    Set txtFile = Nothing
    Set fso = Nothing
End Sub


  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.