I'm looking for a code that I could use to format an excel cell dynamically
for an card account number field type with more than 15 digits.

Here is the scenario.

Column name: Card Account
Card account number: xxxxxxxxxxxxxxxx

I cannot format the cells as number so I have to format as text. However,
when I run the process with the VBA code that I have telling to format as
text (@) it gives me this result in scientific notation because of the
length, I think.
Here is the VBA .NET code:
Select Case sFieldType
Case "Text"
objWorksheet.Range(objWorksheet.Cells(2, i + 1),
objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@"

I just need to display the acc. number as is...can someone please help me!
I gladly appreciatted,
Sincerely,
Marlei Taylor

Alternatively you can try the following (copied and pasted from a
previous posting with exactly the same subject by someone else):

<A simple way to force Excel to accept anything as text is to prepend an
apostrophe to the text.

e.g. to write an integer 123 as 00000123 just write:
ActiveCell = "'" & Format(123, "00000000")

Note that this cell is now definitively text - this precludes the use of
formulae such as SUM on these cells, or further number formatting. If
this is an issue consider setting the numberformat as described in other
posts.

HTH,
Gareth>



Toppers wrote:
> Hi,
> The only way to solve this is ensure the cell is formatted as text
> BEFORE entering a value i.e. the card number has to treated as a string not a
> number.
>
>
> Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't
> know how you reference a previous posting more directly!).
>
> HTH
>
> ".Net" wrote:
>
>
>>I'm looking for a code that I could use to format an excel cell dynamically
>>for an card account number field type with more than 15 digits.
>>
>>Here is the scenario.
>>
>>Column name: Card Account
>>Card account number: xxxxxxxxxxxxxxxx
>>
>>I cannot format the cells as number so I have to format as text. However,
>>when I run the process with the VBA code that I have telling to format as
>>text (@) it gives me this result in scientific notation because of the
>>length, I think.
>>Here is the VBA .NET code:
>>Select Case sFieldType
>> Case "Text"
>> objWorksheet.Range(objWorksheet.Cells(2, i + 1),
>>objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@"
>>
>>I just need to display the acc. number as is...can someone please help me!
>>I gladly appreciatted,
>>Sincerely,
>>Marlei Taylor
>>

Sorry, I accidently placed the other post. I'm a newbie here. Thank you...
this helped a bunch!

"Gareth" wrote:

> Alternatively you can try the following (copied and pasted from a
> previous posting with exactly the same subject by someone else):
>
> <A simple way to force Excel to accept anything as text is to prepend an
> apostrophe to the text.
>
> e.g. to write an integer 123 as 00000123 just write:
> ActiveCell = "'" & Format(123, "00000000")
>
> Note that this cell is now definitively text - this precludes the use of
> formulae such as SUM on these cells, or further number formatting. If
> this is an issue consider setting the numberformat as described in other
> posts.
>
> HTH,
> Gareth>
>
>
>
> Toppers wrote:
> > Hi,
> > The only way to solve this is ensure the cell is formatted as text
> > BEFORE entering a value i.e. the card number has to treated as a string not a
> > number.
> >
> >
> > Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't
> > know how you reference a previous posting more directly!).
> >
> > HTH
> >
> > ".Net" wrote:
> >
> >
> >>I'm looking for a code that I could use to format an excel cell dynamically
> >>for an card account number field type with more than 15 digits.
> >>
> >>Here is the scenario.
> >>
> >>Column name: Card Account
> >>Card account number: xxxxxxxxxxxxxxxx
> >>
> >>I cannot format the cells as number so I have to format as text. However,
> >>when I run the process with the VBA code that I have telling to format as
> >>text (@) it gives me this result in scientific notation because of the
> >>length, I think.
> >>Here is the VBA .NET code:
> >>Select Case sFieldType
> >> Case "Text"
> >> objWorksheet.Range(objWorksheet.Cells(2, i + 1),
> >>objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@"
> >>
> >>I just need to display the acc. number as is...can someone please help me!
> >>I gladly appreciatted,
> >>Sincerely,
> >>Marlei Taylor
> >>
>

You're welcome.

Note, you can check the status of a cell using .PrefixCharacter

e.g. debug.print ActiveCell.PrefixCharacter (check it out in help)

It's ReadOnly so you don't write to it but you may find it useful


..Net wrote:
> Sorry, I accidently placed the other post. I'm a newbie here. Thank you...
> this helped a bunch!
>
> "Gareth" wrote:
>
>
>>Alternatively you can try the following (copied and pasted from a
>>previous posting with exactly the same subject by someone else):
>>
>><A simple way to force Excel to accept anything as text is to prepend an
>>apostrophe to the text.
>>
>>e.g. to write an integer 123 as 00000123 just write:
>>ActiveCell = "'" & Format(123, "00000000")
>>
>>Note that this cell is now definitively text - this precludes the use of
>>formulae such as SUM on these cells, or further number formatting. If
>>this is an issue consider setting the numberformat as described in other
>>posts.
>>
>>HTH,
>>Gareth>
>>
>>
>>
>>Toppers wrote:
>>
>>>Hi,
>>> The only way to solve this is ensure the cell is formatted as text
>>>BEFORE entering a value i.e. the card number has to treated as a string not a
>>>number.
>>>
>>>
>>>Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't
>>>know how you reference a previous posting more directly!).
>>>
>>>HTH
>>>
>>>".Net" wrote:
>>>
>>>
>>>
>>>>I'm looking for a code that I could use to format an excel cell dynamically
>>>>for an card account number field type with more than 15 digits.
>>>>
>>>>Here is the scenario.
>>>>
>>>>Column name: Card Account
>>>>Card account number: xxxxxxxxxxxxxxxx
>>>>
>>>>I cannot format the cells as number so I have to format as text. However,
>>>>when I run the process with the VBA code that I have telling to format as
>>>>text (@) it gives me this result in scientific notation because of the
>>>>length, I think.
>>>>Here is the VBA .NET code:
>>>>Select Case sFieldType
>>>> Case "Text"
>>>> objWorksheet.Range(objWorksheet.Cells(2, i + 1),
>>>>objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@"
>>>>
>>>>I just need to display the acc. number as is...can someone please help me!
>>>>I gladly appreciatted,
>>>>Sincerely,
>>>>Marlei Taylor
>>>>
>>