Formatting Excel from VB

Hi all,
 I have written a VB program that generates a formatted excel file from the recordset. Here are 2 of its columns
------------------------------------------------------
THIS IS THE HEADING PART

clmNumber = Len(Str(Int(3)))
clmName = Right(Str(Int(3)), clmNumber - 1)
clmRange = "a" & clmName
xls.Range(clmRange).Value = "Account"
   
clmNumber = Len(Str(Int(3)))
clmName = Right(Str(Int(3)), clmNumber - 1)
clmRange = "b" & clmName
xls.Range(clmRange).Value = "Shares"
-----------------------------------------------------
THIS IS THE PART WHERE DATA IS POPULATED
WITHIN A LOOP I HAVE

clmNumber = Len(Str(Int(i)))
clmName = Right(Str(Int(i)), clmNumber - 1)
clmRange = "g" & clmName
xls.Range(clmRange).HorizontalAlignment = xlHAlignLeft
xls.Range(clmRange).Borders.Weight = xlThin
xls.Range(clmRange).Value = rs!Account

clmNumber = Len(Str(Int(i)))
clmName = Right(Str(Int(i)), clmNumber - 1)
clmRange = "h" & clmName
a = Format(rs!Shares, "$###,###,###,###,##0.00")
xls.Range(clmRange).Style = "Currency"
xls.Range(clmRange).NumberFormat = "$###,###,###,##0.00"
xls.Range(clmRange).HorizontalAlignment = xlHAlignRight
xls.Range(clmRange).Borders.Weight = xlThin
xls.Range(clmRange).Value = a
--------------------------------------------------------
PROBLEM
 The shares part is displayed correctly ie. if 100 in the recordset then it is representes as $100.00.Perfect
 The account part is not ie. 123e67 becomes 1.23E+69 and 789e12 as 7.89E+14. It does formatting on its own and i think it is because of the "e". I was thinking of first converting the account to a text and then displaying it.
Got this idea because when you right-click on a cell and then click format-cell you get the option of displaying it as text and it works. But I dont know how to get that property to work from my code as I got it for displaying currency.

Any help
aspguruAsked:
Who is Participating?
 
PaulHewsConnect With a Mentor Commented:
Looks like the formatting codes are actually the ones in the Custom format box.
0
 
PaulHewsCommented:
xls.Range(clmRange).NumberFormat = "Text"
0
 
PaulHewsCommented:
From the help:   The format code is the same string as the Format Codes option in the Format Cells dialog box.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
cpearl_sCommented:
hai,
did u try converting the out coming recordset value to  CStr(value)
hope this works
regards
cpearl
0
 
aspguruAuthor Commented:
To PaulHews,
 I added the line to my code but still it does not work. Is there anything wrong that I am doing

-------------------------------------------------------
clmNumber = Len(Str(Int(i)))
clmName = Right(Str(Int(i)), clmNumber - 1)
clmRange = "g" & clmName
xls.Range(clmRange).NumberFormat = "Text"
xls.Range(clmRange).HorizontalAlignment = xlHAlignLeft
xls.Range(clmRange).Borders.Weight = xlThin
xls.Range(clmRange).Value = rs!Account
--------------------------------------------------------
0
 
PaulHewsCommented:
Oops.  Tried it myself; didn't work.  Opened an excel worksheet with a text formatting and found this.

xls.Range(clmRange).NumberFormat = "@"
0
 
LunchyCommented:
<ping>
0
 
aspguruAuthor Commented:
Thanks paul.  You were very helpful.
0
 
PaulHewsCommented:
Happy to help.  Thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.