ActiveX can't create component

S

Sean

Good morning,

I would like to use this code to step through an Excel worksheet and delete
rows where the value in column 'A' is NOT like ######. I can get it to do
one specific row, but I can't get the loop to work right. The problem
seems to be my CurrentCell and NextCell objects, I must not be defining them
correctly. I get an error saying ActiveX cannot create object. I would
appreciate any help. Thank you.

Sean

Private Sub btnXL_Click()
On Error GoTo Err_btnXL_Click
Dim Test As Integer

'Create an object for Excel
Set xlObject = CreateObject("excel.application")

'Make the Excel Application visible
xlObject.Visible = True
Set xlBook = xlObject.Workbooks.Add("U:\Batching Files\batchcontrol.xls")

'Activate worksheet, select the range.
xlObject.Worksheets("Sheet1").Activate
xlObject.ActiveSheet.Range("A2").Select
xlObject.ActiveSheet.Range("A2").Activate

Set currentCell = CreateObject("Excel.Cells")
Set nextcell = CreateObject("Excel.Cells")

Set currentCell = xlObject.ActiveSheet.Range("A2")
Do While Not IsEmpty(currentCell)
Test = xlObject.ActiveSheet.Range(currentCell).Value Like "######"
If Test = False Then
xlObject.ActiveSheet.Rows(currentCell).Delete
End If

Set nextcell = currentCell.Offset(1, 0)
Set currentCell = nextcell
Loop

On Error Resume Next
xlObject.UserControl = True

Exit_btnXL_Click:
Exit Sub

Err_btnXL_Click:
MsgBox Err.Description
Resume Exit_btnXL_Click

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top