Folks, let me first qualify this - I'm a developer and really don't know a
lot about Excel.

I need to automate some reports that run every morning. The end user needs
Excel format, and I'm somewhat familiar with DTS so I thought that approach
would make sense, but I'm having data typing problems.

Here's the script I use in DTS to set things up (swiped from a website
somewhere, can't remember where, then 'massaged') and it works quite well !
All the query stuff works great - didn't see the need to bore you with it
here.

My problem is that the data types in Excel aren't correct and it puts a
little green arrow in the corner of every cell because it doesn't know what
to do with the data. I need a way to tell Excel what each column data type
is and how to format it - especially datetime types and longer integers.
Otherwise I'm right back to doing things manually (export to text - import
to Excel) through a text file again. I know there's shuch a thing as
'templates' in Excel.
Will that help me ?

Any help ?

Thanks - jim
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

dim appExcel, newBook, oSheet, oPackage, oConn
dim queryDate

DTSGlobalVariables("gDate").Value = Cdate(inputBox("Enter the Date for the
query"))

set appExcel = CreateObject("Excel.Application")
set newBook = appExcel.Workbooks.Add
set oSheet = newBook.Worksheets(1)

oSheet.Range("A1").Value = "Pl ID"
oSheet.Range("B1").Value = "Ln Num"
oSheet.Range("C1").Value = "Acct Num"
oSheet.Range("D1").Value = "Tag Num"
oSheet.Range("E1").Value = "Rev Coll"
oSheet.Range("F1").Value = "Rev Exp"
oSheet.Range("G1").Value = "Class ID"
oSheet.Range("H1").Value = "Trans Date Time"

DTSGlobalVariables("sFileName").Value = _
"C:\thepathgoeshere\UO " & _
year(now) & "-" & month(now) & "-" & day(now) - 1 & ".xls"

newBook.SaveAs DTSGlobalVariables("sFileName").Value
newBook.Save

appExcel.Quit

set oPackage = DTSGlobalVariables.parent

set oConn = oPackage.connections("ExcelSheet")
oConn.DataSource = DTSGlobalVariables("sFileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''

1-create a template of excel
2-copy excel template using activx script inside the dts to the report filr
you want
3-export data to excel report file

the template is important to have all column types you need



"Jim Bailey" <jimREMOVETHIS (AT) ANDTHISjbaileygroup (DOT) com> wrote

Thanks Patrick. Per you advice, I'm trying to do that (see script below).
I created a spreadsheet - NonRevTemplate.xlt.
In that template I used 'Format Cells' to set each column to the appropriate
format.
In the script I save under another filename anyway as you can see.
In the designer (transforms), I need a way to map the fields over don't I ?
Thats the part where I, or the software is getting confused.
I did try enough combinations of table names and fields in the transforms
that I did get it to fill with data finally, but it still put the green
arrows in all the cells.
I hate to ask but could you be a little more detailed in how to walk through
the designer transform dialogs?
All I wind up with is messages 'can't find that column name', 'that table
already exists', and various others when trying to set up the transformation
(a simple copy fields) and the 'Create Table' dialog

SELECT FK_P_ID, FK_CLASS_ID, SUM(NUM_COLL_VEHICLE) as TheSum

FROM VEHICLE_SUMMARY(NOLOCK)

WHERE SUMMARY_DATE_TIME >= DATEADD(day, -1, CAST(CONVERT(char(8), GETDATE(),
112) as datetime))
AND SUMMARY_DATE_TIME < CAST(CONVERT(char(8), GETDATE(), 112) as
datetime)
AND FK_PAYMENT_CODE = 11

GROUP BY FK_P_ID,FK_CLASS_ID


Function Main()

Dim oFSO
Dim sSourceFile
Dim sDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "C:\NonRevTemplate.xlt"
sDestinationFile = "C:\NonRev1.xls"

oFSO.CopyFile sSourceFile, sDestinationFile

Set oFSO = Nothing

Dim appExcel, newBook, oSheet, oPackage, oConn

set appExcel = CreateObject("Excel.Application")
set newBook = appExcel.Workbooks.open("c:\NonRev1.xls")
set oSheet = newBook.Worksheets(1)

DTSGlobalVariables("sFileName").Value = "C:\NR " & year(now) & "-" &
month(now) & "-" & day(now) - 1 & ".xls"

newBook.SaveAs DTSGlobalVariables("sFileName").Value
newBook.Save

appExcel.Quit

set oPackage = DTSGlobalVariables.parent

set oConn = oPackage.connections("ExcelSheet")
oConn.DataSource = DTSGlobalVariables("sFileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success
End Function


"Patrick" <patriarck (AT) gmail (DOT) com> wrote

I am having the same issue. I tried formatting the range to Numeric
and still get the same error. All the data that came is as a number
field is converted to Char and I see the little green arrow at the
corner.

Have you find any solution for this problem yet?

Thanks


"Jim Bailey" <jimREMOVETHIS (AT) ANDTHISjbaileygroup (DOT) com> wrote

Biti - no aswer as yet - i've been reading some articles about how the
Create Table works when DTS trys to create the table (worksheet ?) in
Excel - and i'm honestly more confused than i was before,
I've been able to get it to work OK by simply creatig an Excel file, letting
the create table happen on that file, then using this 'New_Table' sheet
that's created. All the types seem OK. At his point that's the best I can
do but i haven't given up completely yet.

jim

"Biti" <yenebiti (AT) yahoo (DOT) com> wrote