将嵌入式OLE对象（Excel文档）保存到Excel 2010 vs 2013中 [英] Saving embedded OLE Object (Excel doc) to file in Excel 2010 vs 2013

问题描述

我正在尝试将嵌入式OLE对象（excel doc）从我当前/打开的工作簿中保存到用户PC上的某个位置。此OLE对象是在执行宏期间填充的模板/仪表板。这部分宏的目标是首先测试该文件是否存在于用户C驱动器上。如果它存在，它将打开该文件，并将工作簿变量设置为此新打开的文档。据我所知，Excel 2010和Excel 2013之间的功能完美无缺。在用户没有将文件保存到C盘的情况下，宏需要打开OLE对象，然后将其保存到驱动器中。一旦完成，宏然后指向该位置并打开该文件。

I am trying to save an embedded OLE Object (excel doc) from my current/open workbook to a location on the users PC. This OLE object is a template/dashboard that gets populated during the execution of the macro. The goal of this portion of the macro is to first test if the file exists on the users C drive. If it does exist, it opens that file and sets a workbook variable to this newly opened document. As far as I can tell, this works flawlessly between both Excel 2010 and Excel 2013. In the case where the user does NOT have the file saved to their C drive, the macro needs to open the OLE object and then save it to drive. Once that is complete, the macro then points back to that location and opens the file.

以下代码在Excel 2013中工作100％，但在Excel 2010中，宏每当我尝试将文件保存到驱动器时​​，崩溃都会崩溃。如果我在中断模式下运行宏，则保存工作正常，只有在导致崩溃的运行时间内。在这里可以使用DoEvents或Application.Wait吗？我有点不熟悉保存文件和计算机资源的技术。

The below code works 100% in Excel 2013, however in Excel 2010, the macro crashes excel whenever I try to save the file to the drive. If I run the macro in break mode, the saving works fine, it is only during run-time in which I cause a crash. Could there be a possible use of DoEvents or Application.Wait here? I'm a bit unfamiliar with the technicalities of saving documents and computer resources.

我注意到的一些事情：

1. 崩溃不生成任何错误代码。它只是给出了已经停止响应


2. 我尝试过多个版本的.SaveAs文件格式：= 52 vs .SaveCopyAs。这两种方法在2010年都会产生相同的崩溃。


3. OLE对象总是以Worksheet的形式打开，如果我可以以某种方式在全新的工作簿中打开该文件，这将是很好的。我认为这个崩溃可能与如何将对象作为工作表打开而不是自己的工作簿相关。

代码：

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

推荐答案

使用实际的嵌入式COM对象，而不是 .Verb 0 给你。

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

如果OLEObject由COM服务器管理，那么它会暴露对底层对象的引用（它是 .Object 属性）。在你的情况下，由于你有一个嵌入式工作簿，它只是一个工作簿对象像任何其他工作簿对象，你会在VBA遇到所有你需要做的是调用 .SaveAs ：

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

然后，您可以跳过与当前Excel服务器中尝试找到的体操相关的其余部分。

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

这篇关于将嵌入式OLE对象（Excel文档）保存到Excel 2010 vs 2013中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！