I use TSQL to create Excel files and send them as reports to a list of people. I use the OPENROWSET function to do the insert into the Excel file. The problem is that all values appear as text in the resulting Excel file. You can't even use the values in SUM functions until you convert it to a real value, which you can do manually by selecting the cell, pressing [F2], and then [Enter]. This converts it to a value, but obviously this is not the solution. I cannot figure out how to control the formatting. Must be a simple way. Anyone know? Thanks.

--smv

Sample OPENROWSET to insert into Excel
____________________________________
insert into OpenRowSet ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes; DATABASE=\\server\folder\GMReport-01-25-2006.xls', 'select * from [Sheet1$A460000]') select * from ##OrderInfo  where dept = 'abc'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

Hi, I've got a quick fix but probably not the real answer to your problem.

If you copy a blank cell and do a paste special / values / add to the range of text that you want as numbers it should sort out your immediate problem. Excel tries to add zero to all of the range and converts the text to values.