February 10, 2009 at 10:16 am
Hi,
I am using an ActiveX to generate a new Excel file and then I define the headers with
.Sheets(1).Range("A1").FormulaR1C1 = "HeaderText"
Then using Transformation Data task I export a query to this excel file.
If I look at my query result I see my date fields as 12/20/2009 (MM/DD/YYYY) and any numbers fields with no decimal or 2 decimals however after the process, in Excell the date shows up as
2009-12-20 00:00:00 and all numbers fields are like 1.0000000000 and they are treated as text.
Even when I update the Excel cells with proper format it will not make a change except if I go cell by cell on the numeric cells and select to convert to numeric.
Any help would be greatly appreciated.
Thanks
February 10, 2009 at 10:35 am
Hi,
Faced an Issue of this kind, bit everything was reverse.
wrote a macro to pull data from sql to excel. the format was the same as you have explained over here, so in the macro itself I have embedded code to set the format.
so, maybe if possible you can try and set the format of the cell from activex??
Sriram
February 10, 2009 at 12:10 pm
It doesn't look like this would work.
After generating my Excel file with the ActiveX I manually opened it and reformated the cell properties to show the right format for the date and numeric and saved the file.
I then executed the next part of my DTS which is Transform Data Task and I ended up with the same problem.
If I open the Transform Data Task in the Destination TAB I see that all of my fields are of type Varchar and I guess this is the problem but it is not modifyable from there!
Any one know how I can change this?
Thanks
February 10, 2009 at 12:23 pm
Taking from a Forum answer posted by Jeff Moden on 2/24/2008 this might be just what you need. Jeff posted this url
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
hope it helps - if not come back and ask again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply