Invalid or deleted object. By using .FormatCon​ditions.It​em(1).Colo​rScaleCrit​eria.Item(​1).FormatC​olor = 8109667

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.

Answer 1

[Shubham]

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.