how to copy sheet from one excel to another sheet of excel.
Show older comments
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 Comments
dpb
on 3 Aug 2022
Unable to resolve the name wdest.Worksheets.Item.
But you used wbdest as the workbook name variable...
Santosh Biradar
on 4 Aug 2022
dpb
on 4 Aug 2022
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
on 4 Aug 2022
Santosh Biradar
on 4 Aug 2022
Edited: Walter Roberson
on 5 Aug 2022
Santosh Biradar
on 4 Aug 2022
dpb
on 4 Aug 2022
That shows you didn't get a valid Excel object returned earlier, it thinks wbdest is a variable/doesn't recognize sheets as a property.
You've just got to poke at the Excel object model using the doc's for Excel to figure out the syntax...
Look at the sample routine I just posted and the examples in the VBA documentation for the Copy method for hints...
Santosh Biradar
on 5 Aug 2022
Edited: Santosh Biradar
on 5 Aug 2022
Walter Roberson
on 5 Aug 2022
if size(newidx) we can not compare ws and timenew, as both are different formatting.
You need to comment out the part starting with "we"
Using if on size() of something is not forbidden, but it is confusing. size() with a single parameter returns a vector (always at least two elements) of the dimensions of the item. if applied to a vector of values is considered true if all of the elements of the vector are non-zero. So if size(newidx) is the same as if all(size(newidx)~=0) . Which is much more clearly written as if ~isempty(newidx)
Walter Roberson
on 5 Aug 2022
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.
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.
Accepted Answer
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 Comments
Santosh Biradar
on 7 Aug 2022
dpb
on 7 Aug 2022
Both "Duplicate..." and 'Copy...' work just fine for me for what they are documented to do...
More Answers (1)
dpb
on 5 Aug 2022
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 Comment
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)
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.
Categories
Find more on Use COM Objects in MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
