Search code examples
vbamatlabexcelactivex

Can Matlab write a macro in an Excel file via ActiveX?

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?

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.

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...

Solution

  • 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);

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

    Attribute VB_Name = "ModuleName"
Option Explicit

Sub SomeMacro()

    Msgbox "From MATLAB, with love..."

End Sub

    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.