Matlab可以通过ActiveX在Excel文件中编写宏吗? [英] Can Matlab write a macro in an Excel file via ActiveX?

问题描述

关于如何通过ActiveX在Matlab中运行Excel宏，有几篇文章和说明.但是可以用Matlab将这样的宏写入Excel文件吗?

There are several posts and instructions how to run Excel macros with Matlab via ActiveX. But is it possible to write such a macro with Matlab into an Excel file?

背景:我编写了一个Matlab工具，可将数据导出到Excel文件中.这些Excel文件应包含一个特定的宏，该宏取决于所包含的数据.

Background: I wrote a Matlab tool which exports data into Excel files. These Excel files should have a specific macro included which is dependend on the containing data.

有一个丑陋的解决方法:我可以使用已经包含的特定宏来创建Excel模板.然后复制相应的模板，并用导出数据填充.但是也许有一个更好，更灵活的解决方案……

Well there is an ugly workaround: I can create Excel templates with the specific macro already included. Then the respective template is copied and filled with the export data. But maybe there is a better and more flexible solution to this problem...

推荐答案

一旦允许以编程方式访问VBA项目，我建议将宏写入文本文件(* .txt或* .bas)，然后运行将导入此文本文件的命令的VBA代码模块.例如:

Once you have allowed programmatical access to the VBA Project, I would suggest to write your macro to a text file (*.txt or *.bas) and then run the command that would import this text file has a VBA code module. For example:

DataFileName = 'D:\Test\DataFile.xlsm';
CodeFileName = 'D:\Test\CodeFile.txt';

Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
Workbook=Workbooks.Open(DataFileName);

% Make Excel visible (Optional)
Excel.visible = 1;

%% Import the code
Import(Workbook.VBProject.VBComponents,CodeFileName);

%% Save
Excel.Application.DisplayAlerts = 0; %Avoid overwrite warning
SaveAs(Workbook,DataFileName);
Excel.Application.DisplayAlerts = 1;

%% Close Excel
Quit(Excel);
delete(Excel);

在这种情况下，CodeFile.txt可能看起来像这样:

In this case, CodeFile.txt could look like this:

Attribute VB_Name = "ModuleName"
Option Explicit

Sub SomeMacro()

    Msgbox "From MATLAB, with love..."

End Sub

在这里，Attribute VB_Name = ...行是必不可少的，它将确定模块的名称.对于Option Explicit，这不是强制性的，而是良好做法.

Here, the line Attribute VB_Name = ... is essential and will determine the name of the module. For Option Explicit, it is not mandatory, but it's good practice.

这篇关于Matlab可以通过ActiveX在Excel文件中编写宏吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！