在Visual Studio中使用excel宏 [英] Working with excel macros in Visual studio

问题描述

HI，

目前我们正在尝试使用Visual Studio和C＃自动化单元测试脚本。客户端要求是我们需要使用Data访问excel文件（大多数数据是使用公式和宏计算的）。我们需要在
应用程序UI中验证这些值。 

关注：我们不确定访问Excel宏和公式并操纵它们以在执行时获取值脚本。我们知道我们可以访问Excel单元格值并更新/读取/编辑/写入单元格。但不确定是否要访问单元格中存在的宏
和excel公式。

请告诉我们如何实现此目的。 

先谢谢。

解决方案

您好，

我建议您使用Microsoft.Office.Interop.Excel自动化Excel，然后您可以使用Excel对象模型来访问宏/单元格公式。请访问

如何从Microsoft Visual C＃.NET自动化Microsoft Excel 

要访问单元格公式，您可以使用
Range.Formula属性

要运行Excel宏，您可以使用
Application.Run方法

如果要访问VBE以添加/编辑/删除宏，可以添加引用：
Microsoft Visual Basics for Applications Extensibility 5.3。 有关详细信息，请访问
Visual Basic可扩展性参考

该代码显示了如何在模块1中添加新的子例程

使用Microsoft.Vbe.Interop; 
 
 VBProject VBProj = Application.VBE.ActiveVBProject; 
 VBComponent VBComp = VBProj.VBComponents.Item（" Module1"）; 
 CodeModule CodeMod = VBComp.CodeModule; 
 string text =" Sub test（）" + System.Environment.NewLine + @" MsgBox（"" Hello""）" + Microsoft.VisualBasic.Constants.vbCrLf +" End子英寸; 
 CodeMod.AddFromString（text）; 

问候，

Celeste

HI,

Currently we are trying to automate unit test script using Visual Studio and C#. The client requirement is that we need to access a excel file with Data ( most of the data is calculated using formulas and macros). And we need to verify this values in the application UI. 

Concern: We are not sure about accessing Excel macros and formula's and manipulating them to get the Value while executing the scripts. We know that we can access Excel cell values and update/read/edit/write into cell. But not sure about accessing macros and excel formulas that are present in the cell.

Please let us know How to achieve this. 

Thanks in Advance.

解决方案

Hello,

I suggest you use Microsoft.Office.Interop.Excel to automate Excel, then you could use Excel object model to access macro/cell formula. Please visit How to automate Microsoft Excel from Microsoft Visual C#.NET 

To access cell formula, you could use Range.Formula Property

To run Excel macro, you could use Application.Run Method

If you want to access VBE to add/edit/delete macro, you could add reference: Microsoft Visual Basics for Applications Extensibility 5.3. For more information, please visit Visual Basic Extensibility Reference

The code shows how to add a new subroutine in Module 1

using Microsoft.Vbe.Interop;

  VBProject VBProj = Application.VBE.ActiveVBProject;
            VBComponent VBComp = VBProj.VBComponents.Item("Module1");
            CodeModule CodeMod = VBComp.CodeModule;
            string text = "Sub test()"+ System.Environment.NewLine + @"MsgBox(""Hello"")"+ Microsoft.VisualBasic.Constants.vbCrLf + "End Sub";
            CodeMod.AddFromString(text);

Regards,

Celeste

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