I used the bellow query to format the excel sheet column as date. but it is not working. kinely assist me guru.

Posted on 2009-02-17
Last Modified: 2012-05-06
I used the bellow query toformat the excel sheet column as date. but it is not working. kinely assist me guru.
query as bellow
     SELECT @strErrorMessage='formatting the column headings in bold ',
 @objErrorObject=@objWorkSheet,   @command1='Range("G1:' +
           'G'+ltrim(rtrim(STR(@currentRow))) +'").NumberFormat = "dd/mm/yyyy"'
EXEC @hr=sp_OASetProperty @objWorkSheet, @command1, 1      

like that how can i set the excel sheet column format  as date

kindly asisst me

SELECT @strErrorMessage='formatting the column headings in bold ', 

 @objErrorObject=@objWorkSheet,   @command1='Range("G1:' +

	     'G'+ltrim(rtrim(STR(@currentRow))) +'").NumberFormat = "dd/mm/yyyy"' 

EXEC @hr=sp_OASetProperty @objWorkSheet, @command1, 1

Open in new window

Question by:mseit
    LVL 35

    Expert Comment

    by:David Todd

    Short answer is that the formatting is way easier to do from excel using a macro, than doing it from sql.

    Suggest using a macro from excel to call a procedure, and iterate through the results set row by row, iterate through each row pasting the value in each cell. Along with that, you can format each cell, dpending on the type of each field.

    LVL 51

    Accepted Solution

    Well the macro format command is :

    Range("G1:G109").NumberFormat = "dd/mm/yyyy"

    BUT... The setproperty has TWO parts the property and the value... some of which do not have a value and so normally supply a 1 (such as when doing bold). In cases where a property is = a value then you do need to supply the two parts seperately.

    So, in this case the correct format is :

          set @command1 = 'Range("G1:G100").NumberFormat'                                           -- or whatever the tricky range thingy is that you are doing....
          exec @hr = sp_OASetProperty @objWorkSheet, @command1, 'dd/mm/yyyy'


    Author Comment

    Bellow sql query to i used to format the color of font. but it is not working, kindly give me a idya how format font color and row color ,etc..

    if @hr=0
        SELECT @strErrorMessage='formatting the column headings in bold ',
                +SUBSTRING(@alphabet,@currentColumn % 26,1)
    if @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 'RED'
    LVL 51

    Expert Comment

    by:Mark Wills
    What the heck has that go to do with setting date format ?

    try (by way of example):

    sp_OASetProperty @objWorkSheet, 'range("A1:A10").Font.ColorIndex', 3

    for a list of the 56 colorindex values scroll down to the color chart in the link :

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    779 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

    13 Experts available now in Live!

    Get 1:1 Help Now