• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

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
0
sweety_745
Asked:
sweety_745
  • 2
1 Solution
 
NorieCommented:
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
 
SteveCommented:
LOL, norrie beat me to it... but heres my file for consideration:
txtFileHandle.xlsm
0
 
NorieCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now