Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed width parsing with trailing minus
I'm not sure how you're building this, but maybe one of these will help:
Dim myArray() As Variant ReDim myArray(1 To 5, 1 To 2) myArray(1, 1) = 0 myArray(1, 2) = 1 myArray(2, 1) = 9 myArray(2, 2) = 1 myArray(3, 1) = 23 myArray(3, 2) = 1 myArray(4, 1) = 34 myArray(4, 2) = 1 myArray(5, 1) = 48 myArray(5, 2) = 1 'or Dim myArr As Variant myArr = Array(Array(0, 1), Array(9, 1), Array(23, 1), _ Array(34, 1), Array(48, 1)) MSweetG222 wrote: I have written a macro that parses fixed width text files using a parse line based upon a parse string on a worksheet cell. Cell A1 = "[xxx] [xxxxxxxx]" --------------------- sParseString = Sheet("Sheet2").Range("A1").Value Worksheets("Sheet1").Columns("A").Parse _ parseLine:sParseString, _ destination:=Worksheets("Sheet1").Range("B1") --------------------- The users just change the parse line (then one stored on a worksheet cell), then macro picks up the parse line value and uses the variable in the macro. Everything worked just fine. If the fixed with was different, user just changed the parse cell on worksheet and the macro works. Now they are receiving fixed width text files with trailing minus signs. I want to switch to this method... Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(23, 1), Array(34 _ , 1), Array(48, 1)), TrailingMinusNumbers:=True And I can figure out where the "brackets" are in the parse line to compute the numbers in the array above, but I don't know how to "build" an array within an array. Can someone give me help on how to do this? Thanks for your help. MSweetG222 -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed width parsing with trailing minus
Dave,
Thanks for your help. I thought I needed an array within an array. Your example shows me I just need an array and you have show me how to build it. Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=myArray, TrailingMinusNumbers:=True Thanks again for your help. -- Thx MSweetG222 "Dave Peterson" wrote: I'm not sure how you're building this, but maybe one of these will help: Dim myArray() As Variant ReDim myArray(1 To 5, 1 To 2) myArray(1, 1) = 0 myArray(1, 2) = 1 myArray(2, 1) = 9 myArray(2, 2) = 1 myArray(3, 1) = 23 myArray(3, 2) = 1 myArray(4, 1) = 34 myArray(4, 2) = 1 myArray(5, 1) = 48 myArray(5, 2) = 1 'or Dim myArr As Variant myArr = Array(Array(0, 1), Array(9, 1), Array(23, 1), _ Array(34, 1), Array(48, 1)) MSweetG222 wrote: I have written a macro that parses fixed width text files using a parse line based upon a parse string on a worksheet cell. Cell A1 = "[xxx] [xxxxxxxx]" --------------------- sParseString = Sheet("Sheet2").Range("A1").Value Worksheets("Sheet1").Columns("A").Parse _ parseLine:sParseString, _ destination:=Worksheets("Sheet1").Range("B1") --------------------- The users just change the parse line (then one stored on a worksheet cell), then macro picks up the parse line value and uses the variable in the macro. Everything worked just fine. If the fixed with was different, user just changed the parse cell on worksheet and the macro works. Now they are receiving fixed width text files with trailing minus signs. I want to switch to this method... Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(23, 1), Array(34 _ , 1), Array(48, 1)), TrailingMinusNumbers:=True And I can figure out where the "brackets" are in the parse line to compute the numbers in the array above, but I don't know how to "build" an array within an array. Can someone give me help on how to do this? Thanks for your help. MSweetG222 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed width parsing with trailing minus
I have this gut suspicion that maybe this can be done simpler, but I am
having troubling visualizing exactly what is going on. Can you give 2 or 3 sample lines of data that you want to parse and show what the data looks like after it gets parsed (and show what is in A1 that you are using as a template)? Rick "MSweetG222" wrote in message ... Dave, Thanks for your help. I thought I needed an array within an array. Your example shows me I just need an array and you have show me how to build it. Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=myArray, TrailingMinusNumbers:=True Thanks again for your help. -- Thx MSweetG222 "Dave Peterson" wrote: I'm not sure how you're building this, but maybe one of these will help: Dim myArray() As Variant ReDim myArray(1 To 5, 1 To 2) myArray(1, 1) = 0 myArray(1, 2) = 1 myArray(2, 1) = 9 myArray(2, 2) = 1 myArray(3, 1) = 23 myArray(3, 2) = 1 myArray(4, 1) = 34 myArray(4, 2) = 1 myArray(5, 1) = 48 myArray(5, 2) = 1 'or Dim myArr As Variant myArr = Array(Array(0, 1), Array(9, 1), Array(23, 1), _ Array(34, 1), Array(48, 1)) MSweetG222 wrote: I have written a macro that parses fixed width text files using a parse line based upon a parse string on a worksheet cell. Cell A1 = "[xxx] [xxxxxxxx]" --------------------- sParseString = Sheet("Sheet2").Range("A1").Value Worksheets("Sheet1").Columns("A").Parse _ parseLine:sParseString, _ destination:=Worksheets("Sheet1").Range("B1") --------------------- The users just change the parse line (then one stored on a worksheet cell), then macro picks up the parse line value and uses the variable in the macro. Everything worked just fine. If the fixed with was different, user just changed the parse cell on worksheet and the macro works. Now they are receiving fixed width text files with trailing minus signs. I want to switch to this method... Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(23, 1), Array(34 _ , 1), Array(48, 1)), TrailingMinusNumbers:=True And I can figure out where the "brackets" are in the parse line to compute the numbers in the array above, but I don't know how to "build" an array within an array. Can someone give me help on how to do this? Thanks for your help. MSweetG222 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed width parsing with trailing minus
Rick,
Here are a couple of lines. They are wrap'd here. One line of data does not have a trailing minus, the other does. 260010000101XXXXXXXXXX 0000000010432230 0000000009392390 0000000001899480 0000000008532750 0000000009482500 0000000000201600 260010000201XXXXXXXXXX 0000000010575000- 0000000010575000- 0000000000000000 0000000010575000- 0000000004935000 0000000010575000- Can you "read" these? Thanks for your help. -- Thx MSweetG222 "Rick Rothstein (MVP - VB)" wrote: I have this gut suspicion that maybe this can be done simpler, but I am having troubling visualizing exactly what is going on. Can you give 2 or 3 sample lines of data that you want to parse and show what the data looks like after it gets parsed (and show what is in A1 that you are using as a template)? Rick "MSweetG222" wrote in message ... Dave, Thanks for your help. I thought I needed an array within an array. Your example shows me I just need an array and you have show me how to build it. Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=myArray, TrailingMinusNumbers:=True Thanks again for your help. -- Thx MSweetG222 "Dave Peterson" wrote: I'm not sure how you're building this, but maybe one of these will help: Dim myArray() As Variant ReDim myArray(1 To 5, 1 To 2) myArray(1, 1) = 0 myArray(1, 2) = 1 myArray(2, 1) = 9 myArray(2, 2) = 1 myArray(3, 1) = 23 myArray(3, 2) = 1 myArray(4, 1) = 34 myArray(4, 2) = 1 myArray(5, 1) = 48 myArray(5, 2) = 1 'or Dim myArr As Variant myArr = Array(Array(0, 1), Array(9, 1), Array(23, 1), _ Array(34, 1), Array(48, 1)) MSweetG222 wrote: I have written a macro that parses fixed width text files using a parse line based upon a parse string on a worksheet cell. Cell A1 = "[xxx] [xxxxxxxx]" --------------------- sParseString = Sheet("Sheet2").Range("A1").Value Worksheets("Sheet1").Columns("A").Parse _ parseLine:sParseString, _ destination:=Worksheets("Sheet1").Range("B1") --------------------- The users just change the parse line (then one stored on a worksheet cell), then macro picks up the parse line value and uses the variable in the macro. Everything worked just fine. If the fixed with was different, user just changed the parse cell on worksheet and the macro works. Now they are receiving fixed width text files with trailing minus signs. I want to switch to this method... Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(23, 1), Array(34 _ , 1), Array(48, 1)), TrailingMinusNumbers:=True And I can figure out where the "brackets" are in the parse line to compute the numbers in the array above, but I don't know how to "build" an array within an array. Can someone give me help on how to do this? Thanks for your help. MSweetG222 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trailing minus | Excel Worksheet Functions | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) | |||
Importing values w/trailing minus signs | Excel Discussion (Misc queries) | |||
Automatically Parsing Fixed-width Text Import | Excel Programming | |||
Fixed Width - Opening Certain Fixed Width Files | Excel Programming |