+ Post New Thread
Results 1 to 3 of 3
Scripts Thread, Preserve leading zero's in Coding and Web Development; Hi All, Basically I have a vbscript that exports data from a SQL server 2000 into Excel. There is a ...
  1. #1

    Join Date
    Jul 2010
    Posts
    25
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Preserve leading zero's

    Hi All,

    Basically I have a vbscript that exports data from a SQL server 2000 into Excel. There is a db field "Supplier_Code" held as a varchar, 7 digits long with leading zeros. When the data is pumped to excel the leading zeros are dropped. Below is the line of code that pumps this specific column to excel.

    objSheet.cells(intRow,2).value = Trim(rsBatchResults.Fields("Supplier_Code").value)

    I need to preserver the leading zero's in the excel output. I have tried

    Right("0000000" & rsBatchResults.Fields("Supplier_Code").value),7) which does not give me an error but doesn't add the leading zeros either

  2. #2

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,332
    Thank Post
    525
    Thanked 2,596 Times in 2,014 Posts
    Blog Entries
    24
    Rep Power
    888
    The problem is, Excel treats leading zeros as unnecessary as the field will see them as numbers. The only way I know of to keep the zeros is to use a custom format on a field:

    Using a custom number format to display leading zeros

    However I don't know how you'd do that in vbscript.

  3. #3

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124
    Try:

    Code:
    objSheet.cells(intRow,2).value = Trim(rsBatchResults.Fields("Supplier_Code").value) 
    objSheet.cells(intRow,2)..numberformat = "0000000"

  4. Thanks to srochford from:

    Teaser (22nd September 2010)



SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 0
    Last Post: 14th August 2010, 11:44 PM
  2. Replies: 0
    Last Post: 10th May 2010, 10:16 PM
  3. Replies: 23
    Last Post: 26th January 2010, 03:55 PM
  4. Problem with Excel Replacing leading zeros
    By chris-t in forum Office Software
    Replies: 4
    Last Post: 28th August 2009, 02:15 PM
  5. Replies: 19
    Last Post: 21st April 2008, 11:53 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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