title: Saving embedded OLE Object (Excel workbook) to file in Excel 2010
body: <p>I am trying to save an embedded OLE Object (Excel workbook) from my current/open workbook to a location on the user's PC. This OLE object is a template/dashboard that gets populated during the execution of the macro.</p>
  
  <p>The macro first tests if the file exists on the user's C drive.</p>
  
  <p>If it does exist, it opens that file and sets a workbook variable to this newly opened workbook. This works in both Excel 2010 and Excel 2013.</p>
  
  <p>Where the user does NOT have the file saved to their C drive, the macro opens the OLE object to save it to drive. The macro then points back to that location and opens the file. The code works in Excel 2013, however in Excel 2010, the macro crashes Excel when I try to save the file to the drive. If I run the macro in break mode, saving works, it is only during run-time that there is a crash.</p>
  
  <p>Could there be a possible use of DoEvents or Application.Wait here? </p>
  
  <p>Some things that I've noticed:</p>
  
  <ol>
  <li>The crash does not generate any error code. It simply gives "Has stopped responding".</li>
  <li>I've tried multiple versions of .SaveAs fileformat:=52 vs .SaveCopyAs. Both methods produce the same crash in 2010. </li>
  <li>The OLE object opens as "Worksheet in", it would be nice if this opens in a new workbook. I'm thinking this crash could be related to how the object is opened as a "Worksheet in" rather than it's own workbook.</li>
  </ol>
  
  <p>Code:    </p>
  
  <pre><code>Dim uName As String
  Dim fName As String
  Dim wbk As Workbook
  Dim sumWB as Workbook
  Dim cbrWB as Workbook
  
  Set cbrWB = Workbooks("PreviouslySet")    
  
  uName = Left(Environ("AppData"), Len(Environ("AppData")) - 16)
  fName = uName &amp; "\OTPReport"  &amp; ".xlsm"
  
  If Dir(fName) = "" Then
  
      Set oEmbFile = cbrWB.Worksheets("CBRDATA").OLEObjects("OTPReport")
      oEmbFile.Verb 0
  
      For Each wbk In Workbooks
          If InStr(1, wbk.Name, "Worksheet in", vbTextCompare) &gt; 0 And InStr(1, wbk.Name, Left(cbrWB.Name, Round(Len(cbrWB.Name) / 2)), vbTextCompare) &gt; 0 Then
              Set sumWB = Workbooks(wbk.Name)
          End If
      Next wbk
  
      With sumWB
          .Activate
          .Application.DisplayAlerts = False
  
          '==ISSUE EXISTS HERE==
          .SaveCopyAs (fName)
  
          .Close
      End With
      Set sumWB = Nothing
      Set sumWB = Workbooks.Open(fName)
  Else:
      Set sumWB = Workbooks.Open(fName)
  End If
  </code></pre>