Need Macro to transpose data

Posted on 2012-08-13
Last Modified: 2012-08-14
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.
Question by:sweety_745
    LVL 33

    Expert Comment

    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("D1").PasteSpecial Transpose:=True
        Set rngSrc = ws.Range("B1:B5")
        Set rngDst = ws.Range("D2")
        While rngSrc(1).Value <> ""
            rngDst.PasteSpecial Transpose:=True
            Set rngSrc = rngSrc.Offset(6)
            Set rngDst = rngDst.Offset(1)
    End Sub

    Open in new window

    LVL 24

    Accepted Solution

    LOL, norrie beat me to it... but heres my file for consideration:
    LVL 33

    Expert Comment


    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.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now