Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Hello all
I am trying to import a text file into my workbook. The text file is 10 rows by 13 colums. I need to convert this file from row wise to columnwise with a twist. I need the first 3 cells of each row (from the text file) concatenated to create a header for each column. The headed should be delimited by "-" Example: Text file Row 1 a b c 1 2 3 4 5 6 7 8 9 10 Row2 d e f 5 6 2 6 7 8 3 4 2 11 Imported it would be would be a-b-c d-e-f 1 5 2 6 3 2 4 6 5 7 6 8 7 3 8 4 9 2 10 11 Thanks in advance! BOB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Option Explicit
Sub testme() Dim curWks As Worksheet Dim newWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oCol As Long Dim rngToCopy As Range Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add With curWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oCol = 0 For iRow = FirstRow To LastRow oCol = oCol + 1 newWks.Cells(1, oCol).Value _ = .Cells(iRow, "A").Value & "-" _ & .Cells(iRow, "B").Value & "-" _ & .Cells(iRow, "C").Value Set rngToCopy = .Range(.Cells(iRow, "D"), _ .Cells(iRow, .Columns.Count).End(xlToLeft)) rngToCopy.Copy newWks.Cells(2, oCol).PasteSpecial Transpose:=True Next iRow End With newWks.UsedRange.Columns.AutoFit Application.CutCopyMode = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Bob wrote: Hello all I am trying to import a text file into my workbook. The text file is 10 rows by 13 colums. I need to convert this file from row wise to columnwise with a twist. I need the first 3 cells of each row (from the text file) concatenated to create a header for each column. The headed should be delimited by "-" Example: Text file Row 1 a b c 1 2 3 4 5 6 7 8 9 10 Row2 d e f 5 6 2 6 7 8 3 4 2 11 Imported it would be would be a-b-c d-e-f 1 5 2 6 3 2 4 6 5 7 6 8 7 3 8 4 9 2 10 11 Thanks in advance! BOB -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Thanks for the code and the link. Im trying to learn as I go and this NG is
a great help. This looks at sheet one in the current book. I need to import from the text file (myfile.for) Thanks! "Dave Peterson" wrote: Option Explicit Sub testme() Dim curWks As Worksheet Dim newWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oCol As Long Dim rngToCopy As Range Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add With curWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oCol = 0 For iRow = FirstRow To LastRow oCol = oCol + 1 newWks.Cells(1, oCol).Value _ = .Cells(iRow, "A").Value & "-" _ & .Cells(iRow, "B").Value & "-" _ & .Cells(iRow, "C").Value Set rngToCopy = .Range(.Cells(iRow, "D"), _ .Cells(iRow, .Columns.Count).End(xlToLeft)) rngToCopy.Copy newWks.Cells(2, oCol).PasteSpecial Transpose:=True Next iRow End With newWks.UsedRange.Columns.AutoFit Application.CutCopyMode = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Bob wrote: Hello all I am trying to import a text file into my workbook. The text file is 10 rows by 13 colums. I need to convert this file from row wise to columnwise with a twist. I need the first 3 cells of each row (from the text file) concatenated to create a header for each column. The headed should be delimited by "-" Example: Text file Row 1 a b c 1 2 3 4 5 6 7 8 9 10 Row2 d e f 5 6 2 6 7 8 3 4 2 11 Imported it would be would be a-b-c d-e-f 1 5 2 6 3 2 4 6 5 7 6 8 7 3 8 4 9 2 10 11 Thanks in advance! BOB -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Record a macro that opens myfile.for.
Then add that to the top of the routine. But use set curwks = activesheet (to point at the worksheet created when you import the file) Bob wrote: Thanks for the code and the link. Im trying to learn as I go and this NG is a great help. This looks at sheet one in the current book. I need to import from the text file (myfile.for) Thanks! "Dave Peterson" wrote: Option Explicit Sub testme() Dim curWks As Worksheet Dim newWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oCol As Long Dim rngToCopy As Range Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add With curWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oCol = 0 For iRow = FirstRow To LastRow oCol = oCol + 1 newWks.Cells(1, oCol).Value _ = .Cells(iRow, "A").Value & "-" _ & .Cells(iRow, "B").Value & "-" _ & .Cells(iRow, "C").Value Set rngToCopy = .Range(.Cells(iRow, "D"), _ .Cells(iRow, .Columns.Count).End(xlToLeft)) rngToCopy.Copy newWks.Cells(2, oCol).PasteSpecial Transpose:=True Next iRow End With newWks.UsedRange.Columns.AutoFit Application.CutCopyMode = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Bob wrote: Hello all I am trying to import a text file into my workbook. The text file is 10 rows by 13 colums. I need to convert this file from row wise to columnwise with a twist. I need the first 3 cells of each row (from the text file) concatenated to create a header for each column. The headed should be delimited by "-" Example: Text file Row 1 a b c 1 2 3 4 5 6 7 8 9 10 Row2 d e f 5 6 2 6 7 8 3 4 2 11 Imported it would be would be a-b-c d-e-f 1 5 2 6 3 2 4 6 5 7 6 8 7 3 8 4 9 2 10 11 Thanks in advance! BOB -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |