VB6 Excel format cell as "text". Use numberformat

clmeyerva

New Member
Joined
Nov 23, 2004
Messages
4
I am using VB 6 to create Excel files using CreateObject. I pass the ADO recordset and have the code loop through the fields and records writing the info to the spreadsheet. I want to explicitly format columns. Anyone know where I am going wrong with the following code?

Select Case ExportRS.Fields(N).Type
Case adBoolean, adSmallInt
'Boolean:
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "Boolean"
Case adBigInt, adBinary, adInteger, adLongVarBinary, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt, adVarBinary
'Integer:
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "#,##0"
Case adCurrency, adDecimal, adDouble, adNumeric, adSingle
'Number: format x,xxx.xx
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "#,##0.00"
Case adDate, adDBDate, adDBTime, adDBTimeStamp
'Date: format (mm/dd/yyyy)
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "mm/dd/yyyy"
Case Else
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "Text"
End Select


Biggest problem is the last one. Setting NumberFormat as "Text" makes Excel try to create a custom format rather than use the "display everything as text" format that you can manually choose while in Excel.

TIA
Candice
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Welcome to the board!

Try:

Case Else
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "@"

(@ as in SHIFT 2)
 
Upvote 0
Re: VB6 Excel format cell as "text". Use numberfo

Tried that and am getting an error "Application-defined or object-defined error" and "Method 'Cells' of object '_Global' failed"

Is it possible that I am not using the "Range" object properly? I am using links to Excel 2000 (EXCEL9.OLB)
 
Upvote 0
Re: VB6 Excel format cell as "text". Use numberfo

Try:

xlSheet.Range(xlSheet.Cells(2, N + 1), xlSheet.Cells(65501, N + 1)).NumberFormat = "@"

You have to qualify the Cells property as well as the Range property.
 
Upvote 0
Re: VB6 Excel format cell as "text". Use numberfo

No error now, but "@" seems to be the same as "general" which means that zip codes are treated like numbers (leading zero stripped).

Do you know where I could find a list of possibilities for .NumberFormat options? I've looked all over the web and can't find anything.

Thanks,
Candice
 
Upvote 0
Re: VB6 Excel format cell as "text". Use numberfo

Wait! Ignor that last "it still doesn't work" whine. It's behaving on the zip code issue now. YIPEE!

If anyone knows where to find other "NumberFormat" coding, please let me know. I have a feeling I'm going to need it.

Candice

PS: Looks like no explicit coding is needed for currency or date either (Thank Goodness), so now all I have to worry about is odd stuff like places where smallint is used instead of bit for boolean fields. YUCK!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top