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
Medium Priority
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
  • 2
  • 2
LVL 35

Expert Comment

by:David Todd
ID: 23667992

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

Mark Wills earned 1500 total points
ID: 23669795
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

ID: 23772305
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
ID: 23774484
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 Closing Comment

ID: 31548153

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 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