error while copying excel sheet to another excel

2 次查看（过去 30 天）
Santosh Biradar
Santosh Biradar 2022-8-4
编辑： Walter Roberson 2022-8-5
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
  4 个评论
显示 2更早的评论
Walter Roberson
Walter Roberson 2022-8-4
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
Santosh Biradar 2022-8-5
编辑：Walter Roberson 2022-8-5
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

请先登录，再进行评论。

请先登录，再回答此问题。

回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by