Results 1 to 6 of 6

VB6 Excel format cell as "text". Use numberformat

This is a discussion on VB6 Excel format cell as "text". Use numberformat within the Excel Questions forums, part of the Question Forums category; I am using VB 6 to create Excel files using CreateObject. I pass the ADO recordset and have the code ...

  1. #1
    New Member
    Join Date
    Nov 2004
    Posts
    4

    Default VB6 Excel format cell as "text". Use numberformat

    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

  2. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,740

    Default

    Welcome to the board!

    Try:

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

    (@ as in SHIFT 2)

  3. #3
    New Member
    Join Date
    Nov 2004
    Posts
    4

    Default 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)

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,608

    Default 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.

  5. #5
    New Member
    Join Date
    Nov 2004
    Posts
    4

    Default 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

  6. #6
    New Member
    Join Date
    Nov 2004
    Posts
    4

    Default 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!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com