Macro in excel is getting removed while running MATLAB

5 次查看（过去 30 天）
Mohammad Azadi Tabar
评论： Mohammad Azadi Tabar 2024-2-15
I am using MATLAB and Excel interface to run excel macro from MATLAB. I did not have any problem with this for a long time. However, recently (maybe due to some securrity or windows update), after few hours of runing the code, MATLAB fails to connect to excel and run the macro (it can happen after 2 hrs or 24 hr). Then when I check the macro in excel, I notice it has been deleted. There is no macro code available.
Also VBA macros are enabled.
The error is this:
Error using COM.Excel_Application/Run
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Cannot run the macro 'Sheet1.FirstExample'.
The macro may not be available in this workbook or all
macros may be disabled.
Help File: xlmain11.chm
Help Context ID: 0
  3 个评论
显示 1更早的评论
Mohammad Azadi Tabar
Mohammad Azadi Tabar 2024-1-17
Yes, simple version of the code is as follow:
function [Obj] = Fun_opt (F)
e = actxserver('Excel.Application');
e.Visible = 1;
e.Workbooks.Open(fullfile(pwd,'\Aspen run CC.xlsm'));
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate
eActivesheetRange = get(e.Activesheet,'Range','A2');
eActivesheetRange.Value = F;
e.Run('Sheet1.FirstExample');
eRange = get(e.Activesheet,'Range','G2');
Obj = eRange.Value;
info=[F Obj];
dlmwrite('CS.txt',info,'delimiter',',','-append');
e.ActiveWorkbook.Save
Quit(e)
e.release;
end
Walter Roberson
Walter Roberson 2024-1-17
I wonder if it would be more robust if you were to use .NET instead of activex ?

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Aditya
Aditya 2024-1-24
Hi Mohammad,
I understand that you're facing issues with a VBA macro being deleted or not executing correctly when using MATLAB with Excel. To address this issue, you can follow these troubleshooting steps:
  • Check Excel's Trust Center Settings: Ensure that Excel's Trust Center settings permit the execution of macros
  • Test Macro in Excel Manually: Before running the macro from MATLAB, verify that it works correctly in Excel
Here's a sample VBA script you can use to test:
Sub FirstExample()
' This macro writes "Hello, World!" into cell G2 of the active sheet.
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Write the string "Hello, World!" to cell G2.
ws.Range("G2").Value = "Hello, World!"
' Inform the user that the macro has run successfully.
MsgBox "Macro 'FirstExample' has run successfully!", vbInformation, "Macro Complete"
End Sub
  • Check for File Corruption: Save the workbook under a new name and see if the issue persists.
  • Monitor File Access: Make sure the Excel file isn't being accessed by multiple processes concurrently
After confirming that macros are enabled in Excel and the macro runs correctly within Excel itself, use the following MATLAB code with a “try-catch-finally” block to handle errors gracefully and ensure resources are released properly:
try
% Start Excel application
e = actxserver('Excel.Application');
e.Visible = 1;
% Open the workbook
workbook = e.Workbooks.Open(fullfile(pwd, 'AspenRunCC.xlsm'));
% Run the macro from a module
e.Run('FirstExample');
% Alternatively, run the macro from a sheet
% e.Run('Sheet1.FirstExample');
% Save the workbook
workbook.Save();
% Quit Excel
e.Quit;
catch ME
disp('An error occurred:');
disp(ME.message);
finally
% Release the COM object
if exist('e', 'var')
e.release;
end
end
For further reference on running Excel macros from MATLAB, please refer to the following MATLAB Central Link:
Hope this helps!
  1 个评论
Mohammad Azadi Tabar
Mohammad Azadi Tabar 2024-2-15
Thanks Aditya,
As I mentioned I have done all this steps, and it is runing well. The problem is that after while, it removes the macro. For example, as it is an optimization code, MATLAB runs the macro for 100 times without problem, but after that, the macro is removed and consequtently MATLAB gives out the Error.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 MATLAB Functions in Microsoft Excel 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by