asked on

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

Norie

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

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

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.