Exporting Excel in ActiveX DTS Script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exporting Excel in ActiveX DTS Script

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-15 : 09:57:00

Marty writes "I want to create a ActiveX DTS Module that will export data to an Excel template. I have this working fine in VB6.0 I copy the code into an ActiveX Script. and it bomb on this line.

xlAPM.Selection.Insert shift:=xlShiftdown (when done with 1 row of date move down to the next row)

Could some one help please

below is the VB Code I copied to the ActiveX Control

we are using SQL Server 2000

Thanks

Marty Nahtygal
mnahtygal@tweddle.com

Attribute VB_Name = "Module1"
Function loadexcel()
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'*************************************************************************
'* Create QOS Excel Workbooks
'* system:
'* Author: Marty Nahtygal
'* Date: 05/10/2002
'* Desc: This Module Create the QOS Workbooks
'*************************************************************************
'Excel Variables
Dim xlAPM
Dim xlNewlyEnrolled
Dim xlWkBook
Dim xlWkSheet

'File variables

Dim sNewEnrollFile
Dim sRepFolder
Dim iCounter1
Dim iInsertRow

' Objects

Dim objConn
Dim objRec
Dim strConnectString
Dim objExcelSumm

' Strings
Dim smonth
Dim sstartdate
Dim senddate
Dim emonth
Dim eday
Dim eyear

Dim sPublicTemplatePath
Dim errLoop
Dim sMsgTitle
Dim sMsgText
Dim iMsgType
Dim iMsgValue
Dim strExcelSumm
Dim TODATE

' counters
Dim cnt
Dim cnt1

'intialize counters
TODATE = Now()
cntEnrolledPct = 0
cnt = 0
cnt1 = 0

Set objConn = New ADODB.Connection

strConnectString = "Driver={SQL Server};Server=EINSTEIN;DATABASE=Primac;UID=sa;pwd=;"
objConn.Open strConnectString

' Creating Work Book in Excell

intEnrolledPct = 0
intActive = 0
intActivePct = 0

sNewEnrollFile = "\\EINSTEIN\FTP\QOS\MRProblemsTemplate.xls"
sRepFolder = "\\EINSTEIN\FTP\QOS\" & Format(Now(), "mmyyyy")

If Dir(sRepFolder, vbDirectory) = "" Then
MkDir sRepFolder
End If

' If Dir(sNewEnrollFile) = "" Then
' frmReports.MousePointer = vbDefault
' sMsgText = "The requested template does not appear to be at specified location " & sNewEnrollFile & "." & vbCrLf
' sMsgText = sMsgText & "Please check the availability of template."
' MsgBox sMsgText, vbOKOnly + vbExclamation, "Create Report"
' Exit Sub
' End If

If Format(Now(), "mm") = 1 Then
smonth = "12"
eyear = Format(Now(), "yyyy") - 1
Else
smonth = Format(Now(), "mm") - 1
eyear = Format(Now(), "yyyy")
End If

Select Case smonth
Case 4, 6, 9, 11
eday = "30"
Case 1, 3, 5, 7, 8, 10, 12
eday = "31"
Case 2
eday = "28"
End Select

sstartdate = smonth & "/01/" & eyear
senddate = smonth & "/" & eday & "/" & eyear

FileCopy sNewEnrollFile, sRepFolder & "\MRProblemsTemplate.xls"
' *************************************************************************
' *************************************************************************

strExcelSumm = " SELECT j1.JLB_DATE as RunDate, j1.JLB_CCTR as Press, (CASE WHEN CONVERT(decimal(12,2),j1.JLB_HRS)+CONVERT(decimal(12,2),j2.JLB_HRS) <= 0 THEN 'REVERSE/RECLASS' ELSE 'NORMAL' END) as TransType,"
strExcelSumm = strExcelSumm & "j1.JLB_SEQ as FirstTrans, j1.JLB_OPER as FirstOper, CONVERT(decimal(12,2),j1.JLB_HRS) as FirstHours, j2.JLB_SEQ as SecondTrans,"
strExcelSumm = strExcelSumm & "j2.JLB_OPER as SecondOper,CONVERT(decimal(12,2),j2.JLB_HRS) as SecondHours "
strExcelSumm = strExcelSumm & "FROM JobLabor j1 INNER JOIN JobLabor j2 ON j1.JLB_SEQ + 1 = j2.JLB_SEQ "
strExc

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 10:00:49

Unfortunately, your code got truncated at 4000 characters, you'll have to repost it.

The FIRST THING you must do IMMEDIATELY is

CHANGE YOUR SA PASSWORD!!!!!!!

Seriously, DO NOT POST YOUR CODE until you change your password! You just provided a means for unscrupulous users to hack your SQL Server. NEVER NEVER NEVER have a blank password for "sa"! EVER.

Also, you should NEVER use "sa" as a login for an application. Create a new login specifically for your Excel app, and use only that login. Do NOT give this new login more permissions than it absolutely requires. Otherwise, this is equivalent to leaving your keys in your unlocked car, or handing them to total strangers.

Edited by - robvolk on 05/15/2002 10:04:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources