mahesh_g
New member
Hi,
I am working on a macro, can some one help me in finding the logic.
My requirement I
1. I have a text file which is of Comma separated, which has some number of fields.
2. I need to import the data to sheet-2 from this text file and separate the fields which are delimited by comma.
3. copy the text from sheet-2 and paste the data into sheet 1 by transposing.
4. there are some fields which has multiple values for example 01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4
i want this field to be like
01 - TEST CARD1
02 - TEST CARD2
03 - TEST CARD3
04 - TEST CARD4
Note:here the values not constant there might be four as shown in above or 5 or 6... some times there might not be any value (Blank)
can you please help in solving this logic.
I have written a logic to import the data from the text file to sheet-2 and copy the data to the sheet-1 by transposing.
Input file:
AA,Y,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4
AB,Y,01 - TEST CARD1 02 - TEST CARD2
AC,Y,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3
AD,Y,
AE,Y,01 - TEST CARD1 62 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5
AF, ,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3
AG,Y,
AH,N,01 - TEST CARD1 15 - TEST CARD2 03 - TEST CARD3
AI,N,01 - TEST CARD1 11 - TEST CARD2 03 - TEST CARD3
AJ, ,01 - TEST CARD1
AK,Y,01 - TEST CARD1 10 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5 06 - TEST CARD6
expected Output
I am working on a macro, can some one help me in finding the logic.
My requirement I
1. I have a text file which is of Comma separated, which has some number of fields.
2. I need to import the data to sheet-2 from this text file and separate the fields which are delimited by comma.
3. copy the text from sheet-2 and paste the data into sheet 1 by transposing.
4. there are some fields which has multiple values for example 01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4
i want this field to be like
01 - TEST CARD1
02 - TEST CARD2
03 - TEST CARD3
04 - TEST CARD4
Note:here the values not constant there might be four as shown in above or 5 or 6... some times there might not be any value (Blank)
can you please help in solving this logic.
I have written a logic to import the data from the text file to sheet-2 and copy the data to the sheet-1 by transposing.
Code:
[TABLE="width: 500"]
[TR]
[TD]Sub TEst()
'
' TEst Macro
'
' Sheets("Sheet").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\mgandla\Desktop\16.6\TESTING.txt", Destination:=Range("$A$1"))
.Name = "TESTING"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A1:C11").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Cells.Select
Range("C8").Activate
Cells.EntireColumn.AutoFit
End Sub[/TD]
[/TR]
[/TABLE]
AA,Y,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4
AB,Y,01 - TEST CARD1 02 - TEST CARD2
AC,Y,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3
AD,Y,
AE,Y,01 - TEST CARD1 62 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5
AF, ,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3
AG,Y,
AH,N,01 - TEST CARD1 15 - TEST CARD2 03 - TEST CARD3
AI,N,01 - TEST CARD1 11 - TEST CARD2 03 - TEST CARD3
AJ, ,01 - TEST CARD1
AK,Y,01 - TEST CARD1 10 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5 06 - TEST CARD6
expected Output
AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK |
Y | Y | Y | Y | Y | Y | N | N | Y | ||
01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 | 01 - TEST CARD1 02 - TEST CARD2 | 01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 | 01 - TEST CARD1 62 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5 | 01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 | 01 - TEST CARD1 15 - TEST CARD2 03 - TEST CARD3 | 01 - TEST CARD1 11 - TEST CARD2 03 - TEST CARD3 | 01 - TEST CARD1 | 01 - TEST CARD1 10 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5 06 - TEST CARD6 |
Last edited by a moderator: