Running excel VBA script via Matlab yields different chart position
7 views (last 30 days)
Show older comments
dear community,
I am trying to run a VBA script in excel using Matlab. Running in Excel, the created chart is positioned 2 columns right from the data (resulting into L) and 4 rows down from top.
The occupied data range is A1 to J78 in each sheet. Running the same code via Matlab will place the resulting chart not at the same posiiton, but near G and y coordinate about 0.75
Any idea? I am using Office 365
best regards
Jonas
To try this we need to enable "Trust Access to the VBA project object model" in Excel over File->Options->Trust Center->Trust Center Settings->MacroSettings
excel VBA code
Sub addBoxplotToEverySheet()
Dim myChart As Chart
Dim rng As Range
Dim excludedRows As Long
Dim ws As Worksheet
excludedRows = 4
For Each ws In ThisWorkbook.Worksheets
' select all but e.g. without first row:
Set rng = ws.UsedRange.Offset(excludedRows, 0).Resize(ws.UsedRange.Rows.Count - excludedRows)
Set myChart = ws.Shapes.AddChart2(406, xlBoxwhisker).Chart
With myChart.Parent
.Top = rng.Rows(1).Top ' Set the top position
.Left = rng.Cells(1, rng.Columns.Count).Offset(0, 2).Left ' Set the left position
End With
Next ws
End Sub
Matlab code:
% Create Excel server
Excel = actxserver('Excel.Application');
% Make Excel visible
Excel.Visible = 1;
% Open an Excel file
Workbook = Excel.Workbooks.Open([cd filesep 'E3_langsam_JittFactor.xlsx']);
% Access the VBA project
VBAProject = Workbook.VBProject;
VBAModule = VBAProject.VBComponents.Add(1); % 1 = vbext_ct_StdModule
% Your VBA code
VBACode = ["Sub addBoxplotToEverySheet()"...
"Dim myChart As Chart", ...
"Dim rng As Range", ...
"Dim excludedRows As Long", ...
"Dim ws As Worksheet", ...
"excludedRows = 4", ...
"For Each ws In ThisWorkbook.Worksheets", ...
" Set rng = ws.UsedRange.Offset(excludedRows, 0).Resize(ws.UsedRange.Rows.Count - excludedRows)", ...
" Set myChart = ws.Shapes.AddChart2(406, xlBoxwhisker).Chart", ...
" With myChart.Parent", ...
" .Top = rng.Rows(1).Top", ...
" .Left = rng.Cells(1, rng.Columns.Count).Offset(0, 2).Left", ...
" End With", ...
"Next ws"...
"End Sub"];
VBACode=strjoin(VBACode,'\n');
VBAModule.CodeModule.AddFromString(VBACode);
% Run the VBA code
Excel.Run('addBoxplotToEverySheet');
% Save and close the workbook
% Workbook.Save;
% Workbook.Close;
%
% % Quit Excel
% Excel.Quit;
2 Comments
Kautuk Raj
on 26 Feb 2024
I tried running the given script using MATLAB R2022a and got the behaviour as you expected. I am not able to reproduce the odd behaviour you observe.
Accepted Answer
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!