Delete Method of Range object doesn't work in VB script

[vera]

Hi guys! Here is something that I don't yet have an explanation to. In
the code below I am trying to delete all rows in the used range and
above. I need to make the sheet ready for the next portion of data. The
code runs fine in VB module in Excel but it fails if run it as vb
script (one of DTS package steps). It fails on Range...Delete line with
the message "Delete Method of class Range Failed". Does anyone know
what needs to be specified for the code to run. It runs fine if loop
across all rows and use Worksheet.Rows.Delete(r) method. Icould use
this approach but loops are slower. Please help!

Here is the code in vbs script:

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

Dim excel_app, num_rows
Set excel_app = CreateObject("Excel.Application")
excel_app.Workbooks.Open "C:\vera\MS Office
projects\Dynamic_Calendar_test.xls"

Set excel_sheet = excel_app.ActiveWorkbook.Sheet"New_Table")
With excel_sheet

num_rows = .UsedRange.Row - 1 + .UsedRange.Rows.Count

' .Range("A2" & LastRow).Clear

Range("A2" & num_rows).Delete (xlShiftUp)

' For r=num_rows To 2 Step-1
' 'If excel_app.CountA(.Rows(r)) = 0 Then .Rows(r).Delete
' .Rows(r).Delete
' Next

End With

'Close the workbook saving changes.
excel_app.ActiveWorkbook.Close True
excel_app.Quit

Set excel_app = Nothing

Main = DTSTaskExecResult_Success

End Function

I commented out Range.Clear and loop that deletes rows in succession -
both work fine.

Thanks,
Vera

 

[Jim Cone]

Vera,

Some general guidelines on automating Excel...
Declare, set and use variables for all Excel objects.
Do not use ActiveSheet or ActiveWorkbook or pretty much anything with "Active"
in it.
Avoid the use of the "With" construct.
Qualify EVERY reference to an Excel object with an object variable (see line 2).

With that said, the following is how I would do it...
'--------------------------------------
Function Main()

Dim Excel_App As Excel.Application
Dim Excel_WB As Excel.Workbook
Dim Excel_Sheet As Worksheet
Dim Num_rows As Long
Dim LastRow As Long
Dim r As Long

Set Excel_App = New Excel.Application
'Excel_App.Visible = True 'optional - default is not visible

Set Excel_WB = Excel_App.Workbooks.Open("C:\vera\MS Office
projects\Dynamic_Calendar_test.xls")
Set Excel_Sheet = Excel_WB.Sheets("New_Table")

Num_rows = Excel_Sheet.UsedRange.Row - 1 + Excel_Sheet.UsedRange.Rows.Count
LastRow = 1000 'arbitrary definition

Excel_Sheet.Range("A2" & LastRow).Clear
Excel_Sheet.Range("A2" & Num_rows).Delete (xlShiftUp)

For r = Num_rows To 2 Step -1
If Excel_App.CountA(Excel_Sheet.Rows(r)) = 0 Then
Excel_Sheet.Rows(r).Delete
'Excel_Sheet.Rows(r).Delete ' Duplication?
Next

'Close the workbook saving changes.
Set Excel_Sheet = Nothing
Excel_WB.Close True
Set Excel_WB = Nothing
Excel_App.Quit
Set Excel_App = Nothing

'Main = DTSTaskExecResult_Success

End Function
'----------------------------------------------------
Regards,
Jim Cone
San Francisco, CA