How can i specify a Format in a Excel Column

Posted on 2004-11-30
Last Modified: 2010-05-02
I have a function in VB 6 which opens Microsoft Excel and exports the data of
a Query into the worksheet. All works fine but the problem is that i have a field
which i populate with some credit card nbrs and the shhet displays them as a
5.47464E+15 instead of 5474643171576970

so my question, how can i set the format of a specific cell as well as how can i set this
for a specific column

Question by:AlexPonnath
    LVL 5

    Assisted Solution

    If you want Excel to ignore the number characteristic of the Credit Card Number put an apostrophe (') on the front of it as you pass it to the worksheet.  Excel then handles it as text


    xlSheet.Cells(1,1).Value = "'" & strCreditCardNumber
    LVL 5

    Expert Comment


    Sorry, to answer your question, it's not really a formatting issue.  The 'number' has no useful meaning, better to handle it as a string.

    The apostrophe doesn't show in Excel by the way.
    LVL 26

    Accepted Solution

    ' set b5

    Application.Cells(5, 2).NumberFormat = "################"
    Application.Cells(5, 2).Value = "5474643171576970
    LVL 5

    Expert Comment


    Actually, following on from Eddy's comment, I think you'd be better to go with ...

    Application.Columns("B:B").NumberFormat = "0000000000000000"

    A Credit Card number is always 16 digits (at least here in the UK) so you wouldn't want to lose any proceeding zeros, although I doubt any numbers start like that (at the moment!)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now