Closing Embedded Excel Document

[tomexcel1]

Hi All,

I currently use the below code to allow users to attach documents (Normally Excel) to the current sheet.

When the Excel documents are embedded, they automatically open in the background. Is there a way of closing them? Currently, we embed the document, then close the master file and reopen it again. Could I add anything to the code to close the file as soon as it's embedded whilst keeping the master file open?

Thanks
In Advance

Private Sub CommandButton1_Click() 
Sheet1.Unprotect ("passwordhere")
    Set Rng = Range("F8")
    Rng.RowHeight = 7.8
    On Error Resume Next
    fpath = Application.GetOpenFilename("All Files,*.*", Title:="Select file", MultiSelect:=True)
    For i = 1 To UBound(fpath)
        Rng.Select
        Rng.ColumnWidth = 8.11
        
        ActiveSheet.OLEObjects.Add _
        Filename:=fpath(i), _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:="excel.exe", _
        IconIndex:=0, _
        IconLabel:=extractFileName(fpath(i))
        ActiveSheet.OLEObjects.Locked = False
        Set Rng = Rng.Offset(0, 1)
    Next i
    Sheet1.Range("H5").Value = "1" 
    Sheet1.Protect ("passwordhere")
End Sub


Public Function extractFileName(filePath) 'Used in code for attaching raw file
    For i = Len(filePath) To 1 Step -1
        If Mid(filePath, i, 1) = "\" Then
        extractFileName = Mid(filePath, i + 1, Len(filePath) - i + 1)
        Exit Function
        End If
    Next
End Function

 

[Micron]

Tested your code & embedded file does not open for me. You could (at the end of the sub)

  Set wb = FPath(i)
  If Not wb Is Nothing Then
     wb.Close savechanges:=False
  Else
     Set wb = Nothing
  End If

or change False to True to save changes.

 

[tomexcel1]

Thanks for your reply. I added your code, and I still get the same outcome.

The embedded file doesn't physically open on screen, but after I embed it, I can see it open in the background when looking on VBA editor

 

[Micron]

AFAIK, that is normal because the embedded file becomes part of the vba project scope. No experience with that in Excel but I'd liken it to creating a reference in Access to another db. The referenced db isn't open, but its code project is in scope (available to be used by the 'parent' db). At first I thought you were saying you could see it in Task Manager. That would mean that it's either open or hasn't been cleared from memory.