Solving the “The Invoked Object has disconnected from its clients” error in Excel VBA

This error may be common in other situations too but I had to spend an hour getting to solve it and wanted to put it up for someone else who needs it.

A screen shot of the error is below

My code quite simply does the following

1. Embeds 3 pdfs into a sheet
2. Creates 3 buttons
3. Sets up the code so clicking the button opens the pdf file.

Here is the code that was generating the error

For i = 1 To 3
'add ole object
OLEFileString = PDFPath & "sheet" & Trim(Str(i)) & ".pdf"
wbtarget.Sheets(2).OLEObjects.Add(Filename:=OLEFileString, link:=False, displayasicon:=True).Select
'add button
wbtarget.Sheets(1).OLEObjects.Add ClassType:="Forms.CommandButton.1", _
Left:=126, Top:=96 * i, Width:=126.75, Height:=25.5
'Code for button
code = ""
code = "Sub CommandButton" & Trim(Str(i)) & "_Click()" & vbCrLf
code = code & "Dim OleOle as OLEObject" & vbCrLf
code = code & "Set OleOle = Sheet2.OLEObjects(" & Trim(Str(i)) & ")" & vbCrLf
code = code & "OleOle.Activate" & vbCrLf
code = code & "End Sub" & vbCrLf
'Write code for button
With wbtarget.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CountOfLines + 1, code
End With
DoEvents
Next i

Just to clean up the interface, I added the pdf files on a separate sheet. So this code wasn’t working. From a few forums, I tried to add a wait through Application.Wait (Now + TimeValue("0:00:02")) but it didn’t solve the error.

Finally it seems to have something to do with the inner workings which is not documented or buggy or both. Apparently after adding the code to the module, the object interface is closed for the worksheet. Simply adding the code generation to a separate loop fixed the problem.


For i = 1 To 3
'add ole object
OLEFileString = PDFPath & "sheet" & Trim(Str(i)) & ".pdf"
wbtarget.Sheets(2).OLEObjects.Add(Filename:=OLEFileString, link:=False, displayasicon:=True).Select
'add button
wbtarget.Sheets(1).OLEObjects.Add ClassType:="Forms.CommandButton.1", _
Left:=126, Top:=96 * i, Width:=126.75, Height:=25.5
Next i
For i = 1 To 3
code = ""
code = "Sub CommandButton" & Trim(Str(i)) & "_Click()" & vbCrLf
code = code & "Dim OleOle as OLEObject" & vbCrLf
code = code & "Set OleOle = Sheet2.OLEObjects(" & Trim(Str(i)) & ")" & vbCrLf
code = code & "OleOle.Activate" & vbCrLf
code = code & "End Sub" & vbCrLf
'Write code for button
With wbtarget.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CountOfLines + 1, code
End With
DoEvents
Next i

It now works perfectly! If you have any suggestions or questions, please comment.