Array question - Excel 2007

[ITALIAORIANA]

Hi,

I also posted this in the VB Script forum, but it was suggested it was better suited for this one.

I have been searching for quite a while trying to figure this out. I have a simple comma delimited formatting macro I recorded in excel to format an exported file from a program called Redgate.
This file is exported in .csv format. After using the macro I noticed the data in certain columns lost the leading zero which is variable based on a 3 character field.
I am not familiar with arrays and have been trying to find information explaining how they work based on what it looks like in the macro below.

After playing around with it, I changed all the ",1" to ",2" (Array(Array(1, 1) to Array(Array(1, 2), Array(Array(2, 1) to Array(Array(2, 2) and so forth.)
This actually worked and now the leading zero's are showing. But I need to understand why this worked.

It's difficult to google this when I have no idea how to ask for what I need. If anyone can point me in a direction where I can find information to explain why this worked THAT would be fantastic.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), _
Array(34, 1), Array(35, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.NumberFormat = "@"
End With
Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub

Thanks
Deana

 

[SkipVought]

hi,

Check this out first. faq68-6659

Bottom line, the number 1 is not equal to the character 1.

So the issue is, are you dealing with NUMBERS or IDENTIFIERS?

If you have NUMBERS in a column and you want to DISPLAY leading zeros, you can do that with a Number Format, BUT you still have a NUMBER in that column with NO ACTUAL LEADING ZEROS!

If you have NUMBERS in a column and you want ACTUAL LEADING ZEROS, then the data in that column must be CONVERTED to TEXT of whatever number of numeric CHARACTERS you desire. You can CONVERT these numbers to TEXT, via the TEXT function on the sheet or using the Format function in VBA. The Format function in VBA returns TEXT.

Second bottom line: Changing the Number Format changes NOTHING -- it simply DISPLAYS the numeric values you have in some different way. If you want to change a number to text, simply changing the Number Format does absolutely nothing! The actual VALUE must change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Hi Skip,

Thanks for the FAQ. Yep, these would be identifiers and NOT numbers.


Thanks
Deana

 

[SkipVought]

NumCell   TxtCell
      1   00001

[tt]
B2: =TEXT(A2,"00000")
[/tt]
Is one method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

NumCell   TxtCell
      1   00001

[tt]
B2: =TEXT(A2,"00000")
[/tt]
Is one method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

In the Array(Array(1,1),Array(2,1),Array(3,1), etc. part of the TextToColumns method, each of the inner Array bits describes field location and how to handle the conversion. In these inner Array pieces, the first digit is the position of the leading character in that field (starting with 0) and the second is a code. The code is 1 for General format, 2 for Text, 3 through 8 for various date formats, and 9 for do not import.

TextToColumns will likely delete the leading 0 if using General format because it decides you are trying to import a number. But TextToColumns will always accept that 0 if using Text or some of the date formats.

 

[SkipVought]

Why try to figure this out in VBA?

IMPORT this .csv file using Data > Import external data > Text files...

This uses the same parsing interface that Data > Text to columns

Be sure to assign the TEXT property to the column in question.

Turn on your macro recorder if you need the code.

It is possible to REFRESH the import if you have a new .csv file, without code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}