Check if Object is embedded in Excel

Hi all,

I have a requirement to check if an object (can be any format -.docx, .pdf, etc) has been embedded to a spreadsheet.
The code I have will embed the file to cell P7 as the location.

There is a 'Check Document' Sub that checks if certain criteria have been completed (e.g. specific cells) in the spreadsheet before uploading to our SharePoint, if not all criteria is satisfied, it will exit the Sub and not post to SP, with a message box advising the user what is missing.
The code I use to check if cells are populated or not is (should noted that there is another bit of code to create the message box):

EInv = Worksheets("Registration Form").Range("E101").Value
    If IsEmpty(EInv) Then
        str = str + "Please complete 'E-Invoicing fields'" & vbCrLf
    End If

I have searched these forums and google but haven't been able to determine how (or if its possible) to check if a file is embedded at cell P7.

2 Questions

1st - How can I check if a file (Object) is located in cell P7?
2nd - Can I amend the above code to so that it will exit the sub if an object is not in the nominated cell?

Hoping someone can help me with this.
Thank you in advance,
Chris

One problem with identifying objects embedded in a sheet is that the OLEObject is not associated with a specific cell. Scenario: for example, using a macro, an object is embedded in cell P7 (more precisely, the upper-left corner of the object is in cell P7). An unruly user moved the object (top left corner) to cell P6 (by 1-2 pixels at most). Searching the addresses of the "source" cells with the macro, we are not able to check whether the required object has been added to the sheet. We see that it is there, while the macro "does not see".
There are at least two solutions to this problem. Both require giving a proper name to the embedded object.
1 Either the name of the object will contain the address of the cell with which we will bind it. Then, by searching OLE objects, we can see if the object has been associated with a specific cell. Example of the name P7_Object1 or E178_Object2
2. or we give each OLE object a specific (unique) name, e.g. CreditAppForm, AMLForm, VATCert, VATExCert, etc. Then we check that the sheet has all the objects with the required names.

Assuming that the first form of object naming was chosen, the macro to check for the presence of OLE objects could look like this:

Sub Test_Art()
    Dim objEmbed      As OLEObject
    Dim rngCellsOLE As Range
    Dim rngCell     As Range
    Dim rngOLE      As Range
    Dim strMsg      As String
    Dim rngUnionOLECells As Range
    Dim ActvSh      As Worksheet

    Set ActvSh = ActiveSheet

    'addresses of required cells associated with OLE objects
    Set rngCellsOLE = ActvSh.Range("C9,I9,C15,I15")

    On Error Resume Next

    'try to associate OLE objects with cells (based on the object name)
    For Each objEmbed In ActvSh.OLEObjects
        Set rngOLE = Nothing

        If objEmbed.OLEType = xlOLEEmbed Then
            Set rngOLE = ActvSh.Range(Split(objEmbed.Name, "_")(0))

            If Not rngOLE Is Nothing Then
                If rngUnionOLECells Is Nothing Then
                    Set rngUnionOLECells = rngOLE
                Else
                    Set rngUnionOLECells = Union(rngUnionOLECells, rngOLE)
                End If
            End If
        End If
    Next objEmbed

    'check if any of the objects are missing
    For Each rngCell In rngCellsOLE
        If Intersect(rngCell, rngUnionOLECells) Is Nothing Then
            strMsg = GenerateMsg(strMsg, rngCell.Address(0, 0))
        End If
    Next rngCell

    On Error GoTo 0


    If Len(strMsg) Then
        MsgBox strMsg
        Exit Sub
    End If


    'let the macro go on
    '...
End Sub


Function GenerateMsg(strMsg As String, strAddress As String) As String
  Dim strTmp As String
    Select Case strAddress
      Case "C9"
        strTmp = "Credit Application Form"
      Case "I9"
        strTmp = "AML Form"
      Case "C15"
        strTmp = "VAT Certificate"
      Case "I15"
        strTmp = "VAT Exempt Certificate"
    End Select
    
    If Len(strTmp) > 0 Then
      GenerateMsg = strMsg & "Please attached " & strTmp & vbLf
    End If
End Function

You should also add a line of code in the macros that insert an OLE object into the sheet, which will appropriately name each of the inserted objects.

Artik

Thank you Artik, very much appreciate the time taken to reply and to explain,
I will play around and see if I can get this to work.

Again, thank you very much.
Chris