Excel文件打开后，Excel交互不起作用 [英] Excel interactions don't work after Excel file opened

问题描述

我使用OUTLOOK VBA中的以下代码打开Excel工作簿:

I open a Excel workbook using this code from within OUTLOOK VBA:

'~~> BEGINNING OF EXCEL CODE <~~
Dim xlApp As Excel.Application
Dim sourceWB As Excel.Workbook
Dim sourceWS As Excel.Worksheet

Set xlApp = New Excel.Application

With xlApp
    .Visible = True
    .EnableEvents = True
    .UserControl = False
    .DisplayAlerts = False
    .AskToUpdateLinks = False
End With


strFile = scPATH & "Seattle ScoreCard " & Format(prevSCdate, "mm") & "." & Format(prevSCdate, "dd") & "." & Format(prevSCdate, "yy") & ".xlsm"

Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceWS = xlApp.sourceWB.Worksheets(Chr(34) & "Week " & Format(prevSCdate, "ww") & " " & Format(prevSCdate, "mm") & "." & Format(prevSCdate, "dd") & "." & Format(prevSCdate, "yy") & Chr(34))

'~~> CANT GET THIS TO RUN ONCE EXCEL OPENS <~~

sourceWS.Copy Before:=Sheets(2)

'~~>

...

它将打开正确的文件和所有内容，但是一旦打开，将不执行sourceWS.Copy行.我已经通过使用直接从VBA在Excel文件中执行命令来验证该命令是否有效.我还激活了Excel库.

It opens the correct file and everything, but once it opens it does not execute the sourceWS.Copy line. I have verified that that command works by using executing it directly from VBA in the Excel file. I also have the Excel library activated.

有帮助吗?结果就像最后一行不存在一样.它会打开文件，然后什么都不会发生.

Any help? The results are as if that last line were non existent. It opens the file and then nothing happens.

推荐答案

第一件事:

Set sourceWS = xlApp.sourceWB.Worksheets(...)

在此行中，您尝试使用名称sourceWB调用xlApp对象(类Excel.Application的实例)的属性，但是，当然xlApp不支持该属性名称为sourceWB.

In this line you tries to call property of xlApp object (instance of class Excel.Application) with name sourceWB, but, of course, xlApp doesn't support property with name sourceWB.

除非您正在使用On Error Resume Next，否则它将给您一个错误-如果这样，请谨慎使用它，请参阅

It would give you an error, unless you are using On Error Resume Next - if so, be carefully of using it, see image to my another answer for explanation why it's bad:)

因此，当打开工作簿并且Outlook知道sourceWB属于xlApp时，您已经将sourceWB分配给了xlApp.这意味着您不应该在xlApp.sourceWB上下文中使用它.将上面的行更改为

So, you have already assign sourceWB to xlApp when opened workbook and Outlook knows that sourceWB belongs to xlApp. That means that you shouldn't use it in this context xlApp.sourceWB. Change the line above to

Set sourceWS = sourceWB.Worksheets(...)

另一件事是对Sheets(2)使用完全限定的名称(如果在执行代码时打开多个工作簿，仅使用Sheets(2)可能会触发错误).更改:

Another thing is to use fully qualified name for Sheets(2) (using just Sheets(2) may trigger an error if you have more than one opened workbook while code is executing). Change:

sourceWS.Copy Before:=Sheets(2)

到

sourceWS.Copy Before:=sourceWB.Sheets(2)

这篇关于Excel文件打开后，Excel交互不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！