How do I set Excel cell format to "Text" from VB.NET?

G

Guest

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
 
G

Gareth

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

Guest

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

Gareth

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top