How to Format the entered Numeric Values of a Excel sheet's column as Text

[DPM]

Hi,

I need to convert the Numbers as Text entered in a column of a Excel sheet.
Eventhough formatted the Column with the following the Column and each cell
gets formatted but the values in the cell still remains as Number.
NumberFormat = "@"

Found the following function from Excel by running a macro and selecting the
"Data"=>"Text to Columns" option which converts the Numbers to Text

The VB script of the recorded macro is as follows,
Selection.TextToColumns Destination:=Range("D4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True

Could someone please advice on what these parameters mean, specially the
"FieldInfo:=Array(1, 2)", don't know from where this array is
generated.
I need the code to be run from VFP.

Also is there any other way to convert the Numbers into Text,
programmetically ?

 

[Nayab]

Hi,

I need to convert the Numbers as Text entered in a column of a Excel sheet.
Eventhough formatted the Column with the following the Column and each cell
gets formatted but the values in the cell still remains as Number.
NumberFormat = "@"

Found the following function from Excel by running a macro and selecting the
"Data"=>"Text to Columns" option which converts the Numbers to Text

The VB script of the recorded macro is as follows,
Selection.TextToColumns Destination:=Range("D4"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True

Could someone please advice on what these parameters mean, specially the
"FieldInfo:=Array(1, 2)", don't know from where this array is
generated.
I need the code to be run from VFP.

Also is there any other way to convert the Numbers into Text,
programmetically ?

you can try to put a single quote prior to the number in the cell and
that will convert it to text. So if your H50 has 1 then change it to
'1 and it will be treated as text.
You can vverify it using the function =T(H50) where H50 is the cell
number where you entered the '1

As for fieldino: you can check excel vba help. It shows:

FieldInfo: Optional Variant. An array containing parse information
for the individual columns of data. The interpretation depends on the
value of DataType. When the data is delimited, this argument is an
array of two-element arrays, with each two-element array specifying
the conversion options for a particular column. The first element is
the column number (1-based), and the second element is one of the
xlColumnDataType constants specifying how the column is parsed.

 

[DPM]

Thanks

Hi,

I need to convert the Numbers as Text entered in a column of a Excel
sheet.
Eventhough formatted the Column with the following the Column and each
cell
gets formatted but the values in the cell still remains as Number.
NumberFormat = "@"

Found the following function from Excel by running a macro and selecting
the
"Data"=>"Text to Columns" option which converts the Numbers to Text

The VB script of the recorded macro is as follows,
Selection.TextToColumns Destination:=Range("D4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True

Could someone please advice on what these parameters mean, specially the
"FieldInfo:=Array(1, 2)", don't know from where this array is
generated.
I need the code to be run from VFP.

Also is there any other way to convert the Numbers into Text,
programmetically ?

you can try to put a single quote prior to the number in the cell and
that will convert it to text. So if your H50 has 1 then change it to
'1 and it will be treated as text.
You can vverify it using the function =T(H50) where H50 is the cell
number where you entered the '1

As for fieldino: you can check excel vba help. It shows:

FieldInfo: Optional Variant. An array containing parse information
for the individual columns of data. The interpretation depends on the
value of DataType. When the data is delimited, this argument is an
array of two-element arrays, with each two-element array specifying
the conversion options for a particular column. The first element is
the column number (1-based), and the second element is one of the
xlColumnDataType constants specifying how the column is parsed.