We help IT Professionals succeed at work.

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

Medium Priority
683 Views
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
regards
venkey

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

Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

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.

HTH
  David
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
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'


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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 ',
        @objErrorObject=@objWorkSheet,
        @command='Range("A1:'
            +SUBSTRING(@alphabet,@currentColumn/26,1)
            +SUBSTRING(@alphabet,@currentColumn % 26,1)
            +'1'+'").font.color'
if @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 'RED'
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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 : http://msdn.microsoft.com/en-us/library/cc296089.aspx

Author

Commented:
good
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.