VBA Extract and Save Embedded Objects... Step Thru Works, Run Does Not!

[mark5767]

When I step thru this code using F8 it works ok to save the embedded objects.

When I run the code, it gets confused and thinks the ActiveWorkbook is the WBN file for some reason, instead of the extracted WBE file. Does anyone have a thought on why the code would yield different results step thru verus run?

Thank you in advance for any input!!

Mark

Sub Extract_Embedded_Files()
    Dim WBO As Workbook ' Manage Files workbook
    Dim WBN As Workbook ' Individual Workorder workbooks
    Dim WBE As Workbook ' Extracted object workbooks
    Dim WSL As Worksheet ' List of files worksheet
    Dim WSN As Worksheet ' Sheet where embedded object is being activated
    
    Dim i As Long
    Dim NextRow As Long
    Dim FinalRow As Long
    Dim LastRow As Long
    Dim RowCount As Long
    Dim ThisFile As String
    Dim oEmbFile As Object
        
    ' Define object variables
    Set WBO = ThisWorkbook
    Set WSL = WBO.Worksheets("List")
    
    Application.DisplayAlerts = False
    
    ' Loop through all the files on WSL
    FinalRow = WSL.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To FinalRow
        
        Application.StatusBar = i & " files processed out of " & FinalRow
        
        ThisFile = WSL.Cells(i, 1)
        ' Open a file
        Set WBN = Workbooks.Open(Filename:=ThisFile)
        Set WSN = WBN.Worksheets("Submission")
                
        ' Reference the OLEObject by index or by name (if Object is named)
        ' Sheets(xxx) refers to the sheet where object is embedded
        Set oEmbFile = WBN.Sheets("Submission").OLEObjects(1)
            oEmbFile.Verb Verb:=xlPrimary
        Set oEmbFile = ActiveWorkbook
        Application.EnableEvents = False 'allows .xlsm files to close
        oEmbFile.SaveAs Filename:=ThisWorkbook.Path & "\" & oEmbFile.Name, FileFormat:=xlOpenXMLWorkbook
        oEmbFile.Close
        WBN.Close SaveChanges:=False
                
    Next i
Application.EnableEvents = True
Application.StatusBar = False
Application.DisplayAlerts = True
MsgBox Title:="Extract_Embedded_Files has Finished", Prompt:="Workbooks have been extracted and saved to " & ThisWorkbook.Path & "." _
& vbCr & vbCr & "Please review extracted files for accuracy.", Buttons:=vbInformation
  
End Sub

 

[AlphaFrog]

When you open a workbook, that opened workbook becomes the ActiveWorkbook thereafter until another workbook is activated or the opened workbook is closed. When you stepped through the code, you may have changed the ActiveWorkbook manually.

I don't see where you set WBE to a workbook.

 

[mark5767]

Thanks AlphaFrog, you are correct, the version above does not set or use WBE for much of anything. Sorry for the confusion. In this version I am just using the oEmbFile object variable to refer to the embedded object. I think the WBE variable was sort of redundant so I (mostly) removed it from this version.

Anyway, using oEmbFile object variable like in the code posted above, the script works using F8 and I don't think I am manually activating the embedded workbook, I just press F8 a bunch of times and it works without touching anything else (keyboard or mouse).

And when I read the code it appears to me that oEmbFile should be the ActiveWorkbook when the SaveAs command is executed. How the WBN becomes the ActiveWorkbook when the macro is run I just don't understand. When I watch the windows I can see the WBN workbook is active when the macro runs, but I don't know why. It almost appears as if both WBN and oEmbFile are active which makes no sense. Another thought is somehow the fact that oEmbFile is an OLE Object it is not being properly refered to by my code and I need to activate it using some other technique?

Anyway, thank you for that clarification, hopefully we'll get some more insight as well!