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

16 views (last 30 days)
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
dpb on 5 Aug 2022
Edited: dpb on 5 Aug 2022
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.

Sign in to comment.

Accepted Answer

dpb
dpb on 6 Aug 2022
Edited: dpb on 7 Aug 2022
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
dpb
dpb on 7 Aug 2022
Both "Duplicate..." and 'Copy...' work just fine for me for what they are documented to do...

Sign in to comment.

More Answers (1)

dpb
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
dpb
dpb on 5 Aug 2022
Edited: dpb on 6 Aug 2022
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.

Sign in to comment.

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!