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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-02 : 07:39:09
|
| Mike writes "Hope this qualifies as hard, it's certainly driving me nuts!Also, I've already posted this under the DTS forum, didn't see this link until after the post.Running WindowsXP/OfficeXP all SP's and Sql2000 client tools only/all SP's all SQL Servers running all SP'sI've been creating ActiveXscripts in a DTS package that does a number of things. 1) Tests to see if an existing instance of a workbook is open.Function Main() On Error Resume Next Set oSSBExcelWbook = GetObject("C:\ssb\testssb01.xls") If Err <> 0 Then On Error GoTo 0 msgbox "Not running" Else msgbox "running" oSSBExcelWbook.ActiveWorkBook.Close False oSSBExcelWbook.Quit Set oSSBEXcelWbook = nothing End If Main = DTSTaskExecResult_SuccessSometimes this works sometimes it doesn't. Doesn't always find running instances. 2) Clear any existing data on the spreadsheets.Function Main() Set oSSBExcelWbook = CreateObject( "Excel.Application.10") 'oSSBExcelWbook.Visible = False oSSBExcelWbook.Workbooks.Open "C:\ssb\testssb01.xls",,,1 oSSBExcelWbook.WorkSheets("BO").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Credit").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Staff Page").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Quarterly $60").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Rebuild Fee").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Half Setup Fee").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Quarterly").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Setup Fee").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("Exceptions").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.WorkSheets("NETADJ Credit").Activate oSSBExcelWbook.Range("A2").Activate Set tbl = oSSBExcelWbook.ActiveCell.CurrentRegion tbl.Offset(1,0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select oSSBExcelWbook.Selection.ClearContents oSSBExcelWbook.ActiveWorkBook.Save oSSBExcelWbook.Workbooks("TestSS |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-03 : 11:09:04
|
| Why not just load the data in to a table and do all of your work against that?Brett8-) |
 |
|
|
|
|
|
|
|
DTS (Script failures)