When I export numeric fields to excel using a Data
Transform task the fields become text with an apostrophe.
What is the best method for handling this? Currently I am
copying a blank spreadsheet then exporting the data. Can I
use an Active X script to format the columns in Excel? If
so, please provide example code.

One way of doing it would be to use a Macro then formats the cells. The
beauty of this is you can do it in Excel and simply Record and Save the
Macro. You can call it from an Active Script task using code similar to

Function Main()

'References to Excel
dim xl_app
dim xl_Spreadsheet

SET xl_app = CREATEOBJECT("Excel.Application")

SET xl_spreadsheet = xl_app.Workbooks.Open _
(DTSGlobalVariables("gv_str_SpreadsheetName").Valu e)


'Run the macro

xl_app.Run DTSGlobalVariables("gv_str_Macroname").Value


'Very important to clean up.
'if you make changes to a spreadsheet then XL will ask you if you want to
save
'it on closing so let's pre-empt it and save the workbook then close

xl_spreadsheet.Save

xl_spreadsheet.Close

xl_app.Quit
set xl_app = Nothing

Main = DTSTaskExecResult_Success
End Function


You may also like to explicitly CAST() the column as an integer.


--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Liz" <elizabeth.gann (AT) suntrust (DOT) com> wrote

Alan:

Thanks, I already had that example code. Here is a
solution I got from Excel help to be used in a Macro.

In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the range of numbers stored as text you want to
convert.
On the Edit menu, click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.