将嵌入式OLE对象(Excel工作簿)保存到Excel 2010中的文件 [英] Saving embedded OLE Object (Excel workbook) to file in Excel 2010

问题描述

我试图将嵌入式OLE对象(Excel工作簿)从当前/打开的工作簿保存到用户PC上的某个位置.该OLE对象是在宏执行期间填充的模板/仪表板.

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.

宏首先测试文件是否存在于用户的C驱动器上.

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

如果确实存在，它将打开该文件，并将工作簿变量设置为此新打开的工作簿.在Excel 2010和Excel 2013中均可使用.

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.

如果用户没有将文件保存到其C驱动器，则宏将打开OLE对象以将其保存到驱动器.宏然后指向该位置并打开文件.该代码在Excel 2013中有效，但是在Excel 2010中，当我尝试将文件保存到驱动器时​​，宏使Excel崩溃.如果我以中断模式运行宏，则保存工作，只有在运行时才会崩溃.

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.

是否可以使用DoEvents或Application.Wait在这里?

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

我注意到的一些事情

1. 崩溃不会生成任何错误代码.它只是给出已停止响应".
2. 我尝试了多个版本的.SaveAs文件格式:= 52与.SaveCopyAs.两种方法在2010年都会产生相同的崩溃.
3. OLE对象以"Worksheet in"打开，如果在新的工作簿中打开它会很好.我认为这次崩溃可能与如何以工作表形式"而不是其自己的工作簿打开对象有关.

代码:

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.

OLEObjects将公开对基础对象的引用(这是.Object属性).在您的情况下，由于您具有嵌入式工作簿，因此它只是一个Workbook对象，就像您在VBA中遇到的任何其他Workbook对象一样.您需要做的就是在其上调用.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中的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！