Invalid or deleted object. By using .FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor = 8109667
3 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Hello everyone,
I have a problem with the excel api. I found a solution with this code
xls = actxserver('Excel.Application');
wb = xls.Workbooks.Open([pwd() filesep file]);
as = wb.ActiveSheet;
as.Columns.Item('B').FormatConditions.AddColorScale(3);
as.Columns.Item('B').FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor = 8109667;
Everytime I try to change the scale I get an error = Invalid or deleted object.
I have no Idea what Iam doing wrong.
I tried to save first the excel
wb.Save;
And than I operrate the code
as.Columns.Item('B').FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor = 8109667;
But there is the same problem.
I also tried to op it before I add a Colorscale but then I get the error = Invoke Error, Dispatch Exception: Ungültiger Index.
0 commentaires
Réponses (1)
Shubham
le 22 Jan 2024
Hi David,
Below is the MATLAB code you can use to add a three-color scale conditional formatting to column B of an Excel worksheet. Make sure to replace 'xyz.xlsx' with the actual name of your Excel file, and ensure that the file is located in the current working directory.
xls = actxserver('Excel.Application'); % Start Excel application
xls.Visible = true; % Make Excel visible
file = 'xyz.xlsx'; % Replace with your actual Excel file name
filePath = fullfile(pwd(), file);
% Check if the file exists
if exist(filePath, 'file') == 2
wb = xls.Workbooks.Open(filePath); % Open the workbook
as = wb.ActiveSheet; % Get the active sheet
% Add a 3-color scale conditional formatting to column B
cf = as.Columns.Item('B').FormatConditions.AddColorScale(3);
% Modify the colors of the color scale criteria
cf.ColorScaleCriteria.Item(1).FormatColor.Color = 8109667; % Set the first color (typically the lowest value)
cf.ColorScaleCriteria.Item(2).FormatColor.Color = 8711167; % Set the second color (typically the midpoint)
cf.ColorScaleCriteria.Item(3).FormatColor.Color = 7039480; % Set the third color (typically the highest value)
wb.Save; % Save the workbook
wb.Close; % Close the workbook
else
error(['File ' file ' does not exist in the current directory.']);
end
xls.Quit; % Quit Excel application
When you run this MATLAB script, it will open the specified Excel workbook and apply a three-color scale conditional formatting to column B. The first color in the scale will be set to a light blue (RGB equivalent to #7B7BFF), the second color to a light green (RGB equivalent to #84FF84), and the third color to a light red (RGB equivalent to #FF6D6D). After applying the formatting, the script saves and closes the workbook, and then quits the Excel application.
To see the changes, simply open the Excel file xyz.xlsx after running the script, and you should see the conditional formatting applied to column B.
0 commentaires
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!