how to copy sheet from one excel to another sheet of excel.

Question

Santosh Biradar 2022-8-3

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1773100-how-to-copy-sheet-from-one-excel-to-another-sheet-of-excel

编辑： dpb 2022-8-7

采纳的回答： dpb

在 MATLAB Online 中打开

Hello

I tried below code

excelFile1 = fname;                  --> It is having Excel file format data with .xlsx extension
excelName1 = fullfile(newPath,excelFile1);
excel = actxserver('Excel.Application');  
Workbooks = excel.Workbooks;
wbsource = Workbooks.Open(excelName);
wbdest = Workbooks.Open(excelName1);
ws = wbsource.Worksheets.Item(newidx);   ---> here newidx will be having sheet name from OLD excel.
ws.Copy(wbdest.Worksheets.Item(1));     ----> error at this line
wbdest.Save   %save destination workbook
excel.Quit
Error:
Unable to resolve the name wdest.Worksheets.Item. 
Error in IncDecAnalysis (line 58)  ws.Copy(wdest.Worksheets.Item(1)); 

Thank you

11 个评论
显示 9更早的评论隐藏 9更早的评论

dpb 2022-8-4

在 MATLAB Online 中打开

I've not tried the Copy method with ActiveX -- many/most VBA syntax shown in the Excel document won't work verbatim with MATLAB/ActiveX because it doesn't know anything about the VBA compiler syntax. You've got to translate those arguments to the method into ActiveX syntax -- and I don't have an example for Copy() at hand to go look at.

Before you get there, however,

ws = wbsource.Worksheets.Item(newidx);   ---> here newidx will be having sheet name from OLD excel.

newidx is undefined in the posted code...maybe you left it out of the posting, but...

I've never used this particular function, bu here's a routine from the Excel_utils set of utility functions one of the other regulars here posted some time back. Most of the other routines work albeit many are overly complex; I've edited a number that I have used extensively, but not done anything at all to this one. I see from comments here it gave him trouble, too, ...

    function DuplicateExcelSheet(Excel, sourceSheetName, newSheetName)
    % 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 = Excel.sheets;
        for sheetIndex = 1 : Sheets.count
          % Get the name of the worksheet with this sheet index.
          thisName = Sheets.Item(sheetIndex).Name;
          if strcmpi(thisName, sourceSheetName)
            % 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 = newSheetName;
% 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.

As noted, I've no idea if this particular routine works or not, but I'd guess it's close...

Santosh Biradar 2022-8-4

编辑：Walter Roberson 2022-8-5

在 MATLAB Online 中打开

Hello @dpb

I have written code in such manner.

please let me know where I did wrong.

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) 
            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.

Santosh Biradar 2022-8-5

编辑：Santosh Biradar 2022-8-5

Hi @dpb

New updated code

I highlighted the changes. Maybe a very stuff remainng to complete this.

Your responce and time for this will be so helpful.

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');

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

function DuplicateExcelSheet(wbdest, ws, newidx)

try

Sheets = wbdest.sheets;

for sheetIndex = 1 : Sheets.count

if size(newidx) we can not compare ws and timenew, as both are different formatting.

Sheets.Item(sheetIndex).Activate;

MathWorks = get(Sheets, 'Item', sheetIndex);

dbstop if caught error

MathWorks.Copy([], MathWorks);

Sheets.Item(sheetIndex).Name = newidx;

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.

Walter Roberson 2022-8-5

在 MATLAB Online 中打开

dbstop if caught error

You should only put that in once, at the command line; there are not many circumstances under which it should appear in the flow of control.

dpb 2022-8-5

编辑：dpb 2022-8-5

在 MATLAB Online 中打开

My first step in trying to write COM for Excel (or any such product) is to record a macro that does what I want to do and open it in the VBA editor to see what the vendor-generated code looks like. Often one can streamline it, but it will provide a working template of what needs to be done and the necessary objects to address. It's the overwhelming complexity of the Excel object model that makes just diving into the VBA doc almost an impossible way to begin unless one is willing to invest the time required to become (or is already) reasonably adept at writing VBA code and knows the model. Trying to do it "cold" is almost guaranteed to lead to nothing but frustration and failure; particularly since with COM and inside MATLAB, one doesn't have the benefit of the full text of error messages that would be provided inside the VBA development environment.

When then writing and debugging, one just has to set a breakpoint at the first line in the function and step through line-by-line and ensure that each line works in turn and returns either the expected value or a valid handle to the desired object or accomplishes the desired modification to the workbook. There is some auto-complete in being able to find methods/properties for a given object at that level, but it's still more trial 'n error than otherwise; one big problem is that named parameters canNOT be passed through COM, one has to ensure to pass ALL required arguments in their proper positional order including empty placeholders for any not needed prior to any in the calling sequence that are.

During this process one is bound to error frequently and may well end up with "zombie" Excel sessions that can only terminate forcefully with the task manager.

Then, as the TMW code shows, there are some methods that simply cannot be referenced by the use of the "dot" notation in COM; I've never been able to figure out why that is so that some things work and others don't -- there seems to be no real rule (that I've discovered, anyway).

All in all, it's a process not for the faint at heart...

Sub Macro1()
'
' Macro1 Macro
'
'
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Sheets(1)
    Sheets("Sheet1 (2)").Select
    Sheets("Sheet1 (2)").Name = "Sheet2"
End Sub

Above is the macro from selecting the first sheet in a new, blank workbook and copying it -- seems straightforward-enough to mimic.

请先登录，再进行评论。

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

Answer 1

dpb 2022-8-6

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1773100-how-to-copy-sheet-from-one-excel-to-another-sheet-of-excel#answer_1022235

编辑：dpb 2022-8-7

在 MATLAB Online 中打开

OK, here's a barebones routine that works to duplicate the given sheet, placing it after the copied one and names it to the requested name string.

    function DuplicateExcelSheet(Excel, sourceSheetName, newSheetName)
    % DuplicateExcelSheet: 
    %   Creates copy of the specified sheet in the active workbook
    %   with the requested new name.  Places the new sheet after the
    %   copied one; no other options implemented in this basic function
      try
        Sheets=Excel.sheets;                                      % the whole collection
        SheetToCopy=Sheets.Item(sourceSheetName);                 % the particular sheet of interest
        sheetIndex=SheetToCopy.Index;                             % the array index to copied sheet
        SheetToCopy.Copy([],SheetToCopy)                          % copy after this sheet only option here
        Sheets.Item(sheetIndex+1).Name = newSheetName;            % and put the new name on it
      catch ME
        errorMessage = sprintf('Error in function DuplicateExcelSheet.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
      end
      return;
    end

This presumes the Excel COM.Excel_Application handle Excel has been created and the desired workbook has been openend prior to calling this routine -- also since it's most often wanted to do quite a few things at one time once one has a workbook open, this also presumes the SAVE and CLOSE operations are handled outside this function.

% sample usage...
Excel = actxserver('Excel.Application');
excelWorkbook = Excel.Workbooks.Open(excelFullFileName);
DuplicateExcelSheet(Excel,'Sheet1','Sheet2')
...
Excel.ActiveWorkbook.Save;
Excel.ActiveWorkbook.Close(false);
delete(Excel); clear Excel excelWorkbook

I was going to create a more general function, but the logic starts getting enough more complex I didn't have the time at the moment.

It wouldn't be hard to add just a direction flag to this one to place before/after the copied sheet; the only change in the copy is to reorder the argument list to have the empty argument second; you would then need to subtract 1 from the index instead of adding 1 to get the right sheet for the rename target sheet.

If one starts to make it totally general as to before/after any other sheet in the workbook, then one has to find the target as well and that plus the various options just adds enough extra logic didn't want to follow through at the moment -- "exercise for Student"...

ADDENDUM

Well, if one doesn't try to get too clever with things like optional inputs, etc., etc., ..., then it's not all that bad, either --

function CopyeExcelSheet(Excel, sourceSheet, targetSheet, position, newSheetName)
    % CopyExcelSheet: 
    %  SYNTAX:
    %   CopyeExcelSheet(Excel,sourceSheet,targetSheet,position,newSheetName)
    %
    %   Creates copy of the specified sheet in the active workbook with the requested new name.
    %   Places the new sheet before or after the targetSheet depending on positon string value.
      try
        Sheets=Excel.sheets;                                      % the whole collection
        SheetSource=Sheets.Item(sourceSheet);                     % the sheet to copy
        SourceIndex=SheetSource.Index;                            % its position index
        SheetTarget=Sheets.Item(targetSheet);                     % the particular sheet of interest
        sheetIndex=SheetTarget.Index;                             % index to copied sheet
        if contains(position,'After','IgnoreCase',1)
          SheetSource.Copy([],SheetTarget)                        % copy after the target sheet
          Sheets.Item(sheetIndex+1).Name = newSheetName;          % and put the new name on it
        else
          SheetSource.Copy(SheetTarget,[])                        % copy before the target sheet
          Sheets.Item(sheetIndex-1).Name = newSheetName;          % and put the new name on it
        end
      catch ME
        errorMessage = sprintf('Error in function CopyExcelSheet.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
      end
      return;
    end

2 个评论
显示无隐藏无

Santosh Biradar 2022-8-7

Last worked!!!

Thank you so much @dpb for your response and your time.

dpb 2022-8-7

Both "Duplicate..." and 'Copy...' work just fine for me for what they are documented to do...

请先登录，再进行评论。

Answer 2

dpb 2022-8-5

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1773100-how-to-copy-sheet-from-one-excel-to-another-sheet-of-excel#answer_1021820

在 MATLAB Online 中打开

Had a few minutes so poked around a little as explained above...

Excel = actxserver('Excel.Application')
excelFullFileName=fullfile(cd,'Data.xlsx');
workbook = Excel.Workbooks.Open(excelFullFileName)
sheets=workbook.Sheets;
sheets.Item('Sheet1').Select
sheets.Item('Sheet1').Copy([],sheets.Item('Sheet1'))
sheets.Item(2).Select
sheets.Item(2).Name='Sheet2'
Excel.ActiveWorkbook.Save
Excel.ActiveWorkbook.Close(0)
delete(Excel);clear Excel workbook sheets

successfully copied Sheet1 from the workbook and renamed it to Sheet2 in a pretty straightforward transliteration of the VBA macro.

NB however: you CANNOT reference the sheets object handle above with something like

Sheets("Sheet1").Copy After:=Sheets(1)

as an argument in VBA -- Sheets in VBA is a reference to the collection of sheets and looks and acts like an array but remember that's inside VBA -- the compiler converts the syntax to the necessary code to actually perform the action. But, in MATLAB, you don't have a VBA compiler; you've got to make the translation yourself to something COM will understand; hence the need above to refer to the Item() array by index/name.

That's a bare outline of something that works; as discussed above, use the debugger and poke around at the objects there; you will get some autocompletion from the tab that can help; what you can't get is actual syntax; that has to come from the VBA doc -- like the .Copy method above -- the above places the copy after the existing sheet I believe, but I didn't go look at the doc to see about how to implement the 'After' option to place it somewhere else.

Similarly, the rename code above is specific and will take adding a fair amount of logic to create a generic routine -- one can select an existing sheet by name as shown and

  >> sheets=Excel.Worksheets;
>> sheets.Count
ans =
     2
>> sheets.Item(1).Name
ans =
    'Sheet1'
>> sheets
sheets =
	Interface.000208D7_0000_0000_C000_000000000046
>> sheets.Item(2).Name
ans =
    'Sheet1 (2)'
>> 

but one must iterate through the collection handle AFAIK; at least I've not discovered any function that will return the list of names as an array. That's the point of (and need for) the loop in the previous code to find the index to the given sheet -- to be able to have the Item() index by position instead of by string-matching.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2022-8-5

编辑：dpb 2022-8-6

在 MATLAB Online 中打开

https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.copy is the VBA documentation; the syntax for the method is

Syntax

expression.Copy (Before, After)

expression A variable that represents a Sheets object.

Parameters

Name Required/Optional Data type Description

Before Optional Variant The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

After Optional Variant The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

NB: As mentioned before, with COM you can't use the named argument syntax shown in the example macro as "After:" but by the syntax list one sees that it is the second argument, the "Before:" sheet would be first. Hence the [] in the function code I posted means the sheet will be placed after the sheet that was found since the sheet being copied and the one referenced in the argument list are the same one.

One could take the second sheet and make a copy of it BEFORE the first by changing the code slightly to be

sheets=workbook.Sheets;             % the sheets collection object
copySheet=sheets.Item(2);           % copy second sheet
beforeSheet=sheets.Item(1);         % which to use to be before/after is first
copySheet.Copy(beforeSheet,[])      % copy is before since is first positional argument

This does, in fact, work -- I just tested it on a dummy workbook.

The numbers now will change in the Count and "who's who in the zoo" so have to handle that to rename the correct one...

>> workbook = Excel.Workbooks.Open(excelFullFileName);
>> sheets=workbook.Sheets;
>> sheets.Count
ans =
     3
>> for i=1:sheets.Count
sheets.Item(i).Name
end
ans =
    'Sheet2 (2)'
ans =
    'Sheet1'
ans =
    'Sheet2'
>> 

after the above where the workbook had only Sheet1, Sheet2 before...you see now that the first sheet is now second. Not sure if the previous handles still refer to the prior object or not...let's see...oh--can't test that right now because I closed the file in order to verify it did work as expected so those objects now are invalid

>> sheet
sheet =
	Interface.000208D8_0000_0000_C000_000000000046
>> sheet.Name
Error: Object returned error code: 0x800A01A8 
>> 

the variable is still retained in memory, but it no longer is tied to anything current.

请先登录，再进行评论。

how to copy sheet from one excel to another sheet of excel.

11 个评论
显示 9更早的评论隐藏 9更早的评论

采纳的回答

2 个评论
显示无隐藏无

更多回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

how to copy sheet from one excel to another sheet of excel.

11 个评论 显示 9更早的评论隐藏 9更早的评论

采纳的回答

2 个评论 显示 无隐藏 无

更多回答（1 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

11 个评论
显示 9更早的评论隐藏 9更早的评论

2 个评论
显示无隐藏无

1 个评论
显示 -1更早的评论隐藏 -1更早的评论