如何用c#代码从文本文件加载vba宏到excel文件?

My C# code has to create an Excel file with two Worksheets and output some data over there. Besides data columns, the Sheet 1 has to be enabled with a VBA macros which would allow a user to perform some mathematical calculations with provided data upon clicking on a particular cell. This VBA macros are stored in a text file, like C:\VBA_MACROS\VBA1.txt.

我的c#代码必须创建一个包含两个工作表的Excel文件，并在那里输出一些数据。除了数据列之外，表1还必须启用VBA宏，该宏允许用户在单击特定单元时使用提供的数据执行一些数学计算。这个VBA宏存储在一个文本文件中，比如C:\VBA_MACROS\VBA1.txt。

Right now I can do it manually, i.e.

现在我可以手动操作。

C# code creates an Excel file and populates it with data.
c#代码创建一个Excel文件并使用数据填充它。
I do a right click on Sheet1 and select an option "View Code".
我在Sheet1上右键单击并选择“查看代码”选项。
I click on the button "Insert" from the Microsoft Visual Basic for Applications Menu and load the file C:\VBA_MACROS\VBA1.txt.
我点击Microsoft Visual Basic for Applications菜单中的“插入”按钮，并加载文件C:\VBA_MACROS\VBA1.txt。
I close the VBA code window.
我关闭VBA代码窗口。

Question: can steps 2 - 4 be performed automatically by a C# code as well as the step 1? In this case a user would not have to perform them manually which would be a way more comfortable for her.

问题:步骤2 - 4可以由c#代码和步骤1自动执行吗?在这种情况下，用户不需要手动执行它们，这对她来说是一种更舒适的方式。

To be exact, this is how the application is created:

确切地说，这就是创建应用程序的方式:

Excel.Application application = new Excel.Application();
Excel.Workbook workbook = application.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Sheets[1];
Excel.Worksheet worksheet2 = workbook.Sheets[2];

// output data to the worksheets
DataTable2Worksheet(tableMain, worksheet, verSize);
DataTable2Worksheet(tableExtra, worksheet2, 0);

// output workbook to the file
string fileDir = @"D:\MyTests\ExcelTests\";
Output2File(fileDir, workbook);

DataTable2Worksheet and Output2File functions are quite trivial, but how to attach the content of the text file to worksheet = workbook.Sheets[1] by using AddFromFile method?

DataTable2Worksheet和Output2File函数非常简单，但是如何将文本文件的内容附加到worksheet = workbook中。使用AddFromFile方法生成[1]表?

1 个解决方案

#1

You'll need a reference to Microsoft.Vbe.Interop;.

您将需要对Microsoft.Vbe.Interop的引用。

Then you need to get a handle on the module you want to insert into. Then you can just use the CodeModule.AddFromFile method to insert the code in your text file into the module.

然后，您需要获得要插入的模块的句柄。然后你就可以用这个复合体了。AddFromFile方法将文本文件中的代码插入到模块中。

VBE.VBProjects("NameOfProject").VBComponents.Item("NameOfWorksheet").CodeModule.AddFromFile("C:\Path\to\file.txt");

The default name for a newly created project is "VBAProject" and you the name of the component for a sheet is the name of the sheet.

新创建的项目的默认名称是“VBAProject”，而您的工作表的组件名称是工作表的名称。

So, for your particular case, you could add this line of code at the end of your snippet to insert the VBA into Sheet1.

因此，对于特定的情况，您可以在代码片段的末尾添加这一行代码，将VBA插入到Sheet1中。

application.VBE.VBProjects("VBAProject").VBComponents.Item("Sheet1").CodeModule.AddFromFile("C:\VBA_MACROS\VBA1.txt");

I just learned that another option is to use the VBProject property of the Workbook, which makes the call a little cleaner.

我刚刚了解到，另一个选项是使用工作簿的VBProject属性，这会使调用更简洁一些。

workbook.VBProject.VBComponents.Item("Sheet1").CodeModule.AddFromFile("C:\VBA_MACROS\VBA1.txt");

#1