1

I am running the same code line son multiple command buttons that open assigned excel templates embedded in the parent spreadsheet.

Private Sub M113_Click()
Dim WDObj As Object
Dim WDApp As Object
Dim str As String

str = ActiveWorkbook.Path & "\CND Scaled Template.xlsm"

Set WDObj = Sheets(2).OLEObjects("CNDS")
WDObj.Verb xlOpen

Dim i As Integer
i = Workbooks.Count

Workbooks(i).SaveCopyAs str
Workbooks(i).Close

Workbooks.Open (str)

Set WDObj = Nothing
Set WDApp = Nothing
End Sub

this is the line where I get error message "Unable to get OLEObjects property of worksheet class"

Set WDObj = Sheets(2).OLEObjects("CNDS")

Running it the first time after opening excel does not give any errors but running it second time is issue. Also going to VBA editor through debug mode and pressing F5 works fine and the entire code executes well. What could be the issue? Also this doesn't happen with Ole objects other than excel.

Kartiki Jagtap
  • 31
  • 4
  • 1
    You probably need to specify in which workbook `Sheets(2)` is. Like: `ThisWorkbook.Sheets(2)` also you probably meant to use `ThisWorkbook.Path` instead of `ActiveWorkbook.Path`. • `ThisWorkbook` is the workbook the code is written in but `ActiveWorkbook` is the workbook that has the focus (is on top) at the moment the code runs. – Pᴇʜ Sep 05 '18 at 10:02

1 Answers1

0

This is your revised code:

Private Sub M113_Click()

    Dim WDObj As Object
    Dim WDApp As Object
    Dim str As String

    With ThisWorkbook
        str = .Path & "\CND Scaled Template.xlsm"
        Set WDObj = .Worksheets(2).OLEObjects("CNDS")
        WDObj.Verb xlOpen
    End With

    Dim i As Long
    i = Workbooks.Count
    Workbooks(i).SaveCopyAs str
    Workbooks(i).Close
    Workbooks.Open str

End Sub

As mentioned in the comments by @PEH, most probably the error is in referring ThisWorkbook and the ActiveWorkbook.

I have removed the Set WDObj = Nothing, because they are local variables and would be destroyed anyway after the end.

Why Use Integer Instead of Long?

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    awesome!!! That worked perfect. Do you know any way an embedded OLE object can be opened read only? – Kartiki Jagtap Sep 05 '18 at 11:21
  • @KartikiJagtap - try to pass it like this `Workbooks.Open str, readonly:=True` - https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open – Vityata Sep 05 '18 at 11:23
  • ok @Vityata. If I understand correctly, workbooks.open would open a workbook with a specific path. To open an embedded OLE object can we use 'OLEobject.open WDObj, readonly:=True' instead of 'WDObj.Verb xlOpen' – Kartiki Jagtap Sep 05 '18 at 11:36
  • @KartikiJagtap - nope, actually I was thinking you wanted to open the `str` one. For the OleObject readonly, check some info here, there is a way to change the readonly status and open it - https://social.msdn.microsoft.com/Forums/office/en-US/73da844e-312e-4b73-a68a-30dd4664437d/how-to-open-an-ole-object-by-readonly – Vityata Sep 05 '18 at 11:47
  • 1
    Thanks. I want to open the oleobject as read only to avoid any changes in the parent file. Seems like there is no way to set this attribute to readonly for these ole objects, hence I am saving the file in another location and opening that instead of the OLE object. – Kartiki Jagtap Sep 05 '18 at 12:02