Fixed width parsing with trailing minus

[MSweetG222]

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]

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))

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

 

[MSweetG222]

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

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))

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

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))

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

 

[MSweetG222]

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

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

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

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