data export to excel

[elena1]

I have an empty Excel workbook with a defined range, column names, and column types. I am inserting data from SQL Server query into the Excel workbook using the Excel ODBC driver and ADO 2.1. After that I open the workbook and check the format of the numbers in the columns.
All numbers are formatted as numbers. However, the Excel SUM function for the column doesn't recognize the numbers: the SUM of them is always 0. Did anybody have the same problem? Any workaround for that?

 

[sunshine19]

Have you tried to write your own global module in the Excel workbook that reformats the cells that have the calculation in them to "general" cell format. Or maybe you could create your calculated field in the global module and insert it in the fields needed because sometimes when you import data into Excel it automatically formats the cells to text inwhich your calculation will not work. Just a thought or two.

 

[thepunisher]

try the 'transferSpreadsheet' method!

 

[xlbo]

Quite a common issue when importing data from SQL Server / Access - the numbers are being treated as text

The best way to get round this will depend on various parameters including how the data is transferred, what format it is in in SQL Server and whether you already have any code running in the excel workbook that you could tag another bit onto....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question