Select method of Worksheet class failed

Come up with error Select method of Worksheet class failed, when try to select a xcel sheet using xlswrite

sheet_source.Select;

Have tried all methods previously mentioned, disabling add ins in xcel, saving the directory in users/public, repairing xcel, closing all xcel instances using taskmanager, changing xslwrite to xlswrite1, giving Admin priveleges to Matlab though this has not solved the problem.

I am using code from reputable author on Github, from an academic pape, that has been validated on Matlab 2017b what I used (also tried on Matlab 2018a), so am stuck for ideas. ("On-demand serum-free media formulations for human hematopoietic cell expansion using a high dimensional search algorithm" by Julie Audet).

[Guillaume]

Can you

• attach the problematic excel file. If content is confidential, you can remove the data from the spreadsheets as long as the spreadsheets are still there
• show us the exact xlswrite call that you use
• give us the entire text of the error message you get, without any edit.

[Edward Contreras]

Thanks very much

Line 40 = activate the sheet

if run == 1

% Get source sheet/template from source file.

sheet_source = e_file_source.Sheets.Item('Reagent Cf');

sheet_source.Activate; % activate the sheet

sheet_source.Select; % select the sheet

Line 99 = SaveExcelsheet

if run == 0

run = run + 1;

disp(repmat('- ',1,textWidth/2));

SaveExcelSheet; % create new worksheet in excel workbook

disp(['main: starting G' num2str(run)]);

disp([' F = ' num2str(mutCon) ', CR = ' num2str(crossCon)]);

Error message

Error using Interface.000208D8_0000_0000_C000_000000000046/Select

Invoke Error, Dispatch Exception:

Source: Microsoft Excel

Description: Select method of Worksheet class failed

Help File: xlmain11.chm

Help Context ID: 0

Error in SaveExcelSheet (line 40)

sheet_source.Select; % select the sheet

Error in main (line 99)

SaveExcelSheet; % create new worksheet in excel workbook

Alternatively I have tried with another computer and got error meassage Interface.000208D8_0000_0000_C000_000000000046/Select

Additionally all code is on github https://github.com/julieaudet?tab=repositories

Answer 1

[Guillaume]

I'm sure the author is vey good at her research but a good programmer she's not. Global variables in every single function, what a nightmare!

I can't see why you'd get an error on line 40. It is probably something specific to your excel installation unfortunately. However, since it appears that the code succesfully loads the excel file and locates the worksheet (otherwise you'd get an error much earlier), you could simply delete the offending line. None of the

sheet_source.Activate; % activate the sheet
sheet_source.Select; % select the sheet

on line 39, 40, 47, 48 do anything useful. The code works exactly the same without these lines.

[Edward Contreras]

Yes that seems to have fixed it stopping there, thanks very much. However it is stopping at a piece of code that is trying to write output into an xcel sheet, although it is clunky I dont see why it is throwing up an error Index exceeds matrix dimensions (run is equal to 1).

for k=1:numFact

if run == 1

switch k

case 1, Factors(k).name = factorNameList(k,1);

[Image Analyst]

What does this say if you put it before that for loop

whos Factors
whos factorNameList

What is the value for k when it throws the error?

[Guillaume]

Which file is that in? It's clearly not in SaveExcelSheet.

On the other hand, the problem is most likely with factorNameList, and factorNameList is a global variable, so good luck findind which function may have messed up. It can be any, that's the problem with global, it's impossible to follow what happens to the variables.

The code has more than 220 global variables defined in main.m!

[Edward Contreras]

Thanks for your input, out of intrest it's in GenCocktailList, Factors and factorNameList are 0x0 matrices which is why it throws up the error I think.

[Guillaume]

Doing a search in all files shows that factornamelist is created in line 15 of saveExcelSheet with:

[~,factorNameList,~] = xlsread(sourcefile,'Reagent Cf',allListRange);

So you'll have to look in the Reagent Cf tab of whichever file sourcefile is and see if it contains at least some text (in whatever range is specified by allListRange).

And since we're editing the code, in GenCocktailList you may as well replace the absurd

        switch k
            case 1,     Factors(k).name = factorNameList{k,1};
            case 2,     Factors(k).name = factorNameList{k,1};
            case 3,     Factors(k).name = factorNameList{k,1};
            case 4,     Factors(k).name = factorNameList{k,1};
            case 5,     Factors(k).name = factorNameList{k,1};
            case 6,     Factors(k).name = factorNameList{k,1};
            case 7,     Factors(k).name = factorNameList{k,1};
            case 8,     Factors(k).name = factorNameList{k,1};
            case 9,     Factors(k).name = factorNameList{k,1};
            case 10,    Factors(k).name = factorNameList{k,1};
            case 11,    Factors(k).name = factorNameList{k,1};
            case 12,    Factors(k).name = factorNameList{k,1};
            case 13,    Factors(k).name = factorNameList{k,1};
            case 14,    Factors(k).name = factorNameList{k,1};
%             case 15,    Factors(k).name = factorNameList{k,1};
        end

by

if k <= 14
    Factors{k}.name = factorNameList{k, 1};
end

which does exactly the same.