How can I use activex to copy an Excel spreadsheet within the same workbook?
20 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
MathWorks Support Team
le 28 Jan 2019
Réponse apportée : Pruthvi G
le 30 Sep 2019
I have an Excel workbook with 4 worksheets. The third worksheet is called 'csvTemplate'. I would like to copy 'csvTemplate' to make a fifth worksheet called 'gsData_01'.
Please advise on the correct MATLAB syntax to do this.
Current Code:
Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open('Example1.xlsx');
set(Excel, 'Visible', 1);
% need the code that implements the spreadsheet copy
Workbook.Save;
Excel.Workbook.Close;
invoke(Excel, 'Quit');
delete(Excel)
Réponse acceptée
MathWorks Support Team
le 28 Jan 2019
The underlying API behind "actxserver" is Microsoft's COM interface. MATLAB simply leverages the COM interface to make calls using Microsoft's APIs. For questions on using the COM interface and syntax please consult Microsoft Documentation and Microsoft Technical Support if necessary.
For copying sheets, please refer to the following documentation page.
Using this, you can use the Example script attached. The crux of the example is at:
a = e.ActiveSheet;
invoke(a,'Copy',[], a)
e.ActiveSheet.Name = "gsData_01";
The first step is to get the active sheet, which in this case is the sheet to be copied.
Next we use the "invoke" function to call the sheet copy method documented in the link above. We are using the optional argument "After" (as noted in the documentation) with the active sheet again in order to place the new sheet after the copied sheet.
Finally, the newly copied sheet is set as the active sheet and thus we can rename it by renaming the ActiveSheet.
Alternatively, you could manually copy a sheet using by first reading the original sheet into MATLAB and then writing it back to Excel as a new sheet. This is discussed in more detail at:
0 commentaires
Plus de réponses (1)
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!