Close embedded Excel Object in VBA

[jake14569]

Hello,

I'm going crazy trying to figure this out. I have, for security reasons, placed an embedded excel object (as an icon) in a very hidden sheet. Various code pulls up the object, extracts or adds data, and closes it with screen updates off. The code below works perfectly in Excel 2013, but in 2010 I am getting an error message: '-2147417848 *80010108)': Method 'Close' of object'_Workbook' failed.

After several hours of trying different methods and researching, I've got nothing. In debugging, the highlighted code is Workbook.Close. Regardless of what I do, Excel 2010 freezes up and I have to end the program. Please help!

Sub testing()


Dim wb As Workbook: Set wb = ThisWorkbook
Dim wbname As String: wbname = Replace(wb.Name, ".xlsm", "")




Dim oEmbFile As Object
Set oEmbFile = ThisWorkbook.Sheets("Login").OLEObjects("ok")
    oEmbFile.Verb Verb:=xlPrimary




For Each Workbook In Workbooks
    If Workbook.Name = ("Worksheet in " & wbname) Then Workbook.Close
    Next
Set oEmbFile = Nothing




End Sub

 

[Domenic]

I've tested it in Excel 2010 and it seems to work fine, except that I needed to replace...

wbname = Replace(wb.Name, ".xlsm", "")

with

wbname = wb.Name

...since the name of the embedded workbook includes the .xlsm file extension. But this wouldn't account for the error you're getting. Also, you can eliminate the "For Each/Next" loop and simply having the following...

[COLOR=darkblue]Sub[/COLOR] testing()

    [COLOR=darkblue]Dim[/COLOR] oEmbFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] oEmbFile = ThisWorkbook.Sheets("Login").OLEObjects("ok")
    
    oEmbFile.Verb Verb:=xlPrimary
    
    oEmbFile.Object.Close
    
    [COLOR=darkblue]Set[/COLOR] oEmbFile = [COLOR=darkblue]Nothing
[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[jake14569]

Thank you for the response. Unfortunately, I am still getting the same error, which leads me to believe it is something else in my sheet. Again, it all works great in Excel 2013 (although your code is much better than mine was!). I am also able to start a new sheet in Excel 2010 that does not have the error, and after having spent considerable time recreating my spreadsheet model in a new workbook, I ended up getting the same error again. So its clearly something else wrong in spreadsheet, I just have no idea what that might be. I've turned off all worksheet events and the error persists, so I am not sure what else it would be. Any ideas?

 

[jake14569]

Also, I think its worth noting that it seems like the problem is in closing the embedded sheet in the same run as it is opened. I can open and close separately (i.e. open with one set of code, close with another, and must run them separately, cannot call both open and close in the same routine) with no issue. However, that is not a solution since it leaves a 'secure' sheet open.

 

[ZVI]

Using of Verb is not stable because it depends on registry values (individually registered for each Excel version).
I’d suggest this more stable one:

Sub Test()
  Dim embWb As Workbook
  Dim embSheet As Worksheet
  Set embWb = ThisWorkbook.Sheets("Login").OLEObjects(1).Object
  Set embSheet = embWb.Sheets(1)
  With embSheet.Range("A1")
    .Value = Val(.Value) + 1
    ThisWorkbook.Save
    If .Value >= 100 Then
      MsgBox "Trial usage is over", vbCritical, ThisWorkbook.Name
    End If
  End With
  embWb.Close
  Set embWb = Nothing
End Sub

 

[jake14569]

You, sir, are a life saver! It will take me some time to fully integrate this into my workbook, but as of now the basic applications of this code are working for me and my problem appears to be resolved. THANK YOU!!!

 

[ZVI]

You are welcome!
BTW there is no need in Application.ScreenUpdating = False / True because embedded workbook in this case opens in invisible mode like the PERSONAL workbook.
Good luck and feel free to ask here if you have problems