Opening Workbook as object from Ms-Word and Find text using checkbox, Delete entire Row then Close workbook

Question

My situation is I am opening Workbook from MS Word where I find some Text and delete entire Row of found Text. To achieve that I am using Userform having Check-boxes and Buttons. The code below loop through all check-boxes and if C.Value return True it perform action by pressing CEEMEA Button.

I am having problem with Excel Object naming Xc.

For the first time I run CEEMEA macro it run properly (Opens workbook--> Find text--> delete row--> close workbook, etc);

but second time, it return error Run-time Error '13': Type mismatch. , so far I think there may be something left on first run, which I did not Quit/Close/set to nothing, (duplication of workbook)

I had checked all spellings, Everything is Correct.

I had Xc.Quit at the end, and Set Xc= Nothing.

I did not understand where its going wrong. I thing on the first run there may be something which I did not Quit or Set to Nothing. I put whole code below. kindly help...

If there is beter way of doing this job kindly suggest.

Dim Xc As Object
Set Xc = CreateObject("Excel.Application")
Xc.Visible = True
Set Wb = Xc.Workbooks.Open("C:\Users\dell\Desktop\EMEA CEEMEA\EMEA CC FINAL LIST.xls")

Dim C As MSForms.Control
For Each C In Me.Controls
    If TypeName(C) = "CheckBox" Then
    If C.Value = True Then
    If C.Caption = "Select All" Then
    Else

    Dim ff As String
    ff = Trim(C.Caption)
  With Wb
        .Application.Sheets("Sheet2").Select
        .Application.Range("A1").Select

        .Application.Cells.Find(What:=ff, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        .Application.ActiveCell.Rows("1:1").EntireRow.Select
        .Application.Selection.Delete Shift:=xlUp
        .Application.Range("A1").Select

  End With

    End If
    End If
    End If
Next C

Wb.Close SaveChanges:=True
Workbooks.Close
Set Wb = Nothing
Xc.Quit
Set Xc = Nothing

Answer 1

You don't need to open workbook and make it visible. This may work for you. Workbooks.close will close all workbooks if exist.

Dim WB As Workbook
Set WB = Workbooks.Open("C:\Users\dell\Desktop\EMEA CEEMEA\EMEA CC FINAL LIST.xls")
Dim C As MSForms.Control
For Each C In Me.Controls
If TypeName(C) = "CheckBox" Then
If C.Value = True Then
If C.Caption = "Select All" Then
Else


With WB
    .Application.Sheets("Sheet2").Select
    .Application.Range("A1").Select

    .Application.Cells.Find(What:=C.Caption, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    .Application.ActiveCell.Rows("1:1").EntireRow.Select
    .Application.Selection.Delete Shift:=xlUp

End With

End If
End If
End If
Next C

WB.Close SaveChanges:=True
Workbooks.Close
Set WB = Nothing