Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

vba - Saving embedded OLE Object (Excel workbook) to file in Excel 2010

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.

The macro first tests if the file exists on the user's C drive.

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.

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.

Could there be a possible use of DoEvents or Application.Wait here?

Some things that I've noticed:

  1. The crash does not generate any error code. It simply gives "Has stopped responding".
  2. I've tried multiple versions of .SaveAs fileformat:=52 vs .SaveCopyAs. Both methods produce the same crash in 2010.
  3. 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.

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 & "OTPReport"  & ".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) > 0 And InStr(1, wbk.Name, Left(cbrWB.Name, Round(Len(cbrWB.Name) / 2)), vbTextCompare) > 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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use the actual embedded COM object instead of the default action that .Verb 0 gives you.

OLEObjects expose a reference to the underlying object if they are being administered by a COM server (it's the .Object property). In your case, since you have an embedded workbook, it's just a Workbook object like any other Workbook object you'd encounter in VBA. All you should need to do is call .SaveAs on it:

oEmbFile.Object.SaveAs fName

Then you can simply skip the rest of the gymnastics related to trying to find it in your current Excel server.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...