error while copying excel sheet to another excel

Hello

Start

1.Read the two cells from Result Summary Excel

2. Combine two cells of Result summary Excel, It will give us Sheet name

3. Create Dummy Excel file

4. Search sheet name in desired location of Excels

5. Copy those sheet

6. Paste into Dummy Excel file with name.

Repeat 1 to 6 untill lenth of 2nd step's array data.

End

function [sheetIdx] = IncDecAnalysis()
selPath = uigetdir(pwd,' Please select the target folder');
oldPath = pwd;
%% Creating New Folder to store result 
folder = 'NewSfun_SummaryResult';
if ~exist(folder, 'dir')
    mkdir(folder);
    newPath = uigetdir(pwd,' Please select NewSfun_SummaryResult Folder');
else
    newPath = uigetdir(pwd,' Please select NewSfun_SummaryResult Folder');
end
%% Reference Exel
[file,path] = uigetfile('*.xlsx','SelectSummaryResult Excel File');
if isequal(file,0)
    disp('Select Summary Excel');
else
    disp(['User selected ', fullfile(path,file)]);
end
%Excel Sheet name being collected here from two cells.
newlyAdded = readtable('SummaryResult.xlsx', 'Sheet', 'aaa', 'Range','A2:P8','TextType','string');
Newlyaddednames = string(newlyAdded.BLFFileName) + "_" + num2str(newlyAdded.Time_sec_);
%Here  Excel file like 'aaa' 'bbb'... will be having those sheet Names which will be at Newlyaddednames
selNewPath = uigetdir(pwd,'Please select ALL NEW Summary Result Path');
newSumPath = pwd;
chkNewExcelList = ["aaa","bbb", ...
    "ccc","ddd", ...                    
    "eeef","fff",];

for i=1:length(chkNewExcelList)       
    excelFile = strcat(chkNewExcelList(i),'.xlsx');
    excelName= fullfile(selNewPath,excelFile);
    if isfile(excelName)         
        sheets = sheetnames(excelName);
        % idx = ismember(Newlyaddednames, sheets, 'rows' );
        idx = Newlyaddednames();
        %newidx = cell( size(idx));
        % idxSize = size(idx);
        T = table;
        fname = excelFile;
        writetable(T,fname);
        movefile (fname, newPath)
        for j= 1:length(idx)
            newidx = idx(j);
            excelFile1 = fname;
            try
                excelName1 = fullfile(newPath,excelFile1);
                Excel = actxserver('Excel.Application');  %start excel
                Workbooks = Excel.Workbooks;
                wbsource = Workbooks.Open(excelName);
                wbdest = Workbooks.Open(excelName1);
                Sheets = Excel.ActiveWorkBook.Sheets;
                ws = wbsource.WorkSheets.Item(newidx);  
                DuplicateExcelSheet(wbdest, ws, newidx) %---> functioin calling, not sure whether it is correct way or not
            end
            %wbdest.Save  %--->Commented because thowing error 
                Excel.Quit  %quit excel
            end
        end
     end
end
function DuplicateExcelSheet(wbdest, ws, newidx)
    % Duplicates the specified sheet in the active workbook 
    % and gives it the specified new name.
    % Sample call:
    %   Excel_utils.DuplicateExcelSheet(Excel, 'Results1', 'Results2');
    % Duplicate the 'Results' workbook.
      try
        Sheets = wbdest.sheets;
        for sheetIndex = 1 : Sheets.count
          % Get the name of the worksheet with this sheet index.
          thisName = Sheets.Item(sheetIndex).Name;
          if strcmpi(thisName, ws)
            % We found the sheet to copy.
            Sheets.Item(sheetIndex).Activate;  % Guessing this may be one not needed --dpb
            % Run code from Mathworks technical support, on 11/9/2018, to duplicate a sheet.
            MathWorks = get(Sheets, 'Item', sheetIndex);
            MathWorks.Copy([], MathWorks);
            Sheets.Item(sheetIndex+1).Name = newidx;
% this looks like ill-fated first try -- dpb            
%             copiedSheetName = sprintf('%s (2)', sourceSheetName); % For example "Results 1 (2)"
%             Sheets(copiedSheetName).Select
%             Sheets(copiedSheetName).Name = newSheetName;
          end
        end
      catch ME
        errorMessage = sprintf('Error in function DuplicateExcelSheet.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
      end
      return; % from DuplicateExcelSheet
    end % of the DuplicateExcelSheet() method.

Error Message:

Dot indexing is not supported for variables of this type.

Error in function DuplicateExcelSheet.

Error Message:

Dot indexing is not supported for variables of this type.

Thank you

Please let me know for brief

[Walter Roberson]

Probably something is coming out [] when you expect a struct or an object.

Because of your try/catch we do not know which line the problem is happening on. Use

dbstop if caught error

to debug down to which line, and then trace backwards from there to figure out what is coming out as []

[Santosh Biradar]

Hello @Walter Roberson

Thank you for your responce.

Here I got below error.

Error

Caught-error breakpoint was hit in Copy_2_of_IncDecAnalysis>DuplicateExcelSheet at line 83. The error was:

Dot indexing is not supported for variables of this type.

83 Sheets = wbdest.sheets;

[Walter Roberson]

                wbdest = Workbooks.Open(excelName1);

What result do you get if that fails?

What result do you get if that file does not exist yet because this is the first sheet to be transferred to it?

[Santosh Biradar]

Hi @Walter Roberson

wbdest = Workbooks.Open(excelName1); ---->There was blank data.

As Excel = actxserver('Excel.Application'); ---> only takes one excel at time so I added two actxserver for two different excels.

Now both excel can be read at a time with two differ actxserver.

for the moment,

function function DuplicateExcelSheet(wbdest, ws, newidx) is not returning the sheet copied data.

Rest above code is same. Few changes done below.

try
    excelName1 = fullfile(newPath,excelFile1);
    Excel = actxserver('Excel.Application');
    Excel2 = actxserver('Excel.Application');%start excel
    Workbooks = Excel.Workbooks;
    Workbooks2 = Excel2.Workbooks;
    wbsource = Workbooks.Open(excelName);
    wbdest = Workbooks2.Open(excelName1);
    Sheets = Excel.ActiveWorkBook.Sheets;
    Sheets2 = Excel2.ActiveWorkBook.Sheets;
    dbstop if caught error
    ws = wbsource.WorkSheets.Item(newidx);  %or you can you the sheet index: ws = wbsource.Worksheets.Item(index);
    dbstop if caught error
    DuplicateExcelSheet(wbdest,ws, newidx) %copy worksheet before 1st worksheet of destination workbook
catch
end
wbdest.Save   %save destination workbook
Excel.Quit  %quit excel
end
end
end
end

function DuplicateExcelSheet(wbdest, ws, newidx)
% Duplicates the specified sheet in the active workbook 
% and gives it the specified new name.
% Sample call:
% Duplicate the 'Results' workbook.
try
    Sheets = wbdest.sheets;
    for sheetIndex = 1 : Sheets.count         
        if size(newidx)
            Sheets.Item(sheetIndex).Activate; 
            MathWorks = get(Sheets, 'Item', sheetIndex);
            dbstop if caught error
            MathWorks.Copy([], MathWorks);
            Sheets.Item(sheetIndex).Name = newidx;
        end
    end
    dbstop if caught error
catch ME
    errorMessage = sprintf('Error in function DuplicateExcelSheet.\n\nError Message:\n%s', ME.message);
    fprintf('%s\n', errorMessage);
end
return; % from DuplicateExcelSheet
end % of the DuplicateExcelSheet() method.

thank you