Need Macro to transpose data

I am looking for Create a template in Excel which can transpose the data I imported from a .txt file. Probably need more than just transpose function.
Attached is the sample text file I need to import  .
I created spreadsheet manually, where I am looking for my output to be.


We will be getting these text files every day and want to use a spread sheet to help us easy understand.
Failed-Events---08092012-170003.txt
Book1.xlsx
sweety_745Asked:
Who is Participating?
 
SteveCommented:
LOL, norrie beat me to it... but heres my file for consideration:
txtFileHandle.xlsm
0
 
NorieVBA ExpertCommented:
This wil import the text file to a new worksheet and then transpose the data.
Option Explicit

Sub ImportAndTranspose()
Dim ws As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim strFileName As String

    Set ws = Worksheets.Add ' ("Cell1")

    strFileName = "C:\Users\Norie\Documents\ExcelStuff\Forums\ExpertsExchange\Failed-Events---08092012-170003.txt"
        With ws.QueryTables.Add(Connection:= _
                                     "TEXT;" & strFileName _
                                     , Destination:=ws.Range("$A$1"))
            .Name = "Failed-Events---08092012-170003"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 23
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = ":"
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    ws.Range("A1:A5").Copy

    ws.Range("D1").PasteSpecial Transpose:=True

    Set rngSrc = ws.Range("B1:B5")
    Set rngDst = ws.Range("D2")

    While rngSrc(1).Value <> ""

        rngSrc.Copy

        rngDst.PasteSpecial Transpose:=True

        Set rngSrc = rngSrc.Offset(6)
        Set rngDst = rngDst.Offset(1)
    Wend
    
End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
Barman

I might have beaten you to it but what I posted might not work consistently.

I've made a big assumption in the code based on the one file we've seen.

That assumption is that the data starts at row 23, which it might not in another file.

Oh, and you tidied up the results nicely - I didn't even AutoFit.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.