在代码中启用Excel宏? [英] Enable Excel Macro in codes?

问题描述

我正在做一个基于Excel类的现有项目，将值从一个工作表A复制到另一个工作表B.ws A单元格中有一些公式和Addin函数.结果，复制代码仅用于将值复制到wsB.这是一段简化的代码:

I am working an existing project which is base do Excel class to copy values from one worksheet A to another one B. There are some formulas and Addin functions in ws A cells. The copy codes are used to copy only values out to ws B as a result. Here is a block of simplified codes:

using Excel = Microsoft.Office.Interop.Excel;
....

object missing = System.Type.Missing;

Excel.Application app = new Excel.Application();
// Creating Excel application with source workbook in memory
Excel.Workbook workbook = app.Workbooks.Open("SourceA.xls",
  Excel.XlUpdateLinks.xlUpdateLinksNever,....);
...

app.Calculate() // Forcing to recalculate value in cells.
...

//create destination workbook
Excel.Workbook destWb = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.WorkSheet destSheet = (Excel.Worksheet)destWb.Worksheets[1];
Excel.Range destRange = (Excel.Range)destSheet.Cells[1, 1];
//rename sheet 1
destSheet.Name = "wsB Report";

//Set Source Data Range from 
Excel.WorkSheet sourceWA = (Excel.Worksheet)workbook.Worksheets["wsA"];
//Get the predefined named range "DataRange" from source wsA
Excel.Range sourceRange = sourceWA.get_Range("DataRange", missing);
//copy data
sourceRange.Copy(missing);

//paste values and number formats
destRange.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats,
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, missing, missing);
//paste formats
destRange.PasteSpecial(Excel.XlPasteType.xlPasteFormats,
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, missing, missing);
//paste column widths
destRange.PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths,
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, missing, missing);
....

destWb.SaveAs("destB.xls", missing, ....);

我遇到的问题是destB.xls中的副本单元都标记为"#NAME?".它们应该是复制的值.我打开源excel文件，可以看到源范围中的数据已更新为值.但是，当我运行代码时，这些值似乎不可用.

The problem I have is that the copies cells in destB.xls are all marked as "#NAME?". They should be values copied. I open the source excel file and I can see the data in the source range are updated with values. However, when I run my codes, the values seem not available.

我猜我在代码中创建的Excel应用程序可能未启用宏.这可能是无法复制值的原因.例如，当我打开源excel文件时，会看到提示，以启用或禁用宏.如果单击启用"按钮，excel将需要一些时间才能在数据单元格中显示值.

I guess that the Excel application I created in my codes may not have Macro enabled. This may be reason values cannot be copied. For example, when I open the source excel file, I'll see a prompt to enable or disable Macros. If I click on Enable button, the excel will take some time to display values in data cells.

如果这是原因，代码中是否有任何方法可以启用Macro，以便单元格中的所有公式都可以更新值?

If that is the reason, is there any way in codes to enable Macro so that all formulas in cells will be able to update values?

另一个原因可能是由OS或Windows安全更新引起的.不知道任何Windows安全设置是否会影响Excel宏.如果是这种情况，我应该更改Windows安全级别或以任何方式强制在项目中启用宏吗?

Another reason might be caused by OS or Windows security updates. Not sure if any Windows security settings may affect Excel macros. If this is the case, should I change Windows security level or any way to force Macros enabled in my project?

顺便说一句，我的项目在安装了Office Excel 2003的Windows XP和Windows 2008 Server中运行.该项目是在VS 2008中完成的.

By the way, my project runs in Windows XP and Windows 2008 Server, with Office Excel 2003 installed. The project was done in VS 2008.

推荐答案

我进一步研究了代码，发现通过使Excel应用程序可见，我可以看到实际发生的情况.按照 Ben Voigt 的建议，Macro的安全设置可以在Excel中完成，但是在代码中，您可以无论设置如何，都可以强制执行任何类似于VBA的代码.以下是使Excel可见并查看所有提示对话框的一些代码:

I went further into my codes and I found that by making Excel app visible, I'd able to see what actually happened. As Ben Voigt's suggestion, the security settings for Macro can be done in Excel, but in codes, you can force to execute any VBA-like codes no matter what the setting is. Here are some codes to make Excel visible and see all the prompt dialogs:

Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open("SourceA.xls", ...);
app.Visible = true;       //set to 'true' when debbugging, Exec is visible
app.DisplayAlerts = true; //enable all the prompt alerts for debug. 
...

例如，我用C#代码重建链接，在单元格中设置值，并刷新或计算值(模拟F9).通过使Excel可见，我看到了正在发生的事情，并在代码中发现了无法满足我期望的错误.

For example, I rebuild links in my C# codes, set values in cells and refresh or calculate values(simulate F9). By making Excel visible, I saw what was going on and found bugs in my codes which did not do what I want.

这篇关于在代码中启用Excel宏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！