MATLAB Answers

Paste values in excel with MATLAB COM activeX

44 ビュー (過去 30 日間)
Adriano
Adriano 2017 年 4 月 6 日
コメント済み: Adriano 2017 年 4 月 6 日
Hello I need to paste as values in Excel some copied cells. How Can I do? With my code I can copy as formulas and not as values! That's my code:
clc
clear all
excel = actxserver('Excel.Application');
excel.Visible = 1;
excel.DisplayAlerts = 0;
filename = 'C:\Dropbox\Auryn\ESTRATEGIAS\ibs multiactivo2.xlsm';
filename2 = 'C:\Dropbox\Auryn\ESTRATEGIAS\ZEN REAL2.xlsm';
filename3 = 'C:\Dropbox\Auryn\Ordenes\Recommendations2.xlsx';
ibs = excel.Workbooks.Open(filename);
zen = excel.Workbooks.Open(filename2);
rec = excel.Workbooks.Open(filename3);
pause(60)
zen.Activate
invoke(zen,'Save')
zen.Close
ibs.Activate
Sheets = excel.ActiveWorkBook.Sheets;
sheet2 = get(Sheets, 'Item', 5);
invoke(sheet2, 'Activate');
Activesheet = excel.Activesheet;
Activesheet.Range('B64:W104').Copy;
rec.Activate
wksheet = rec.Worksheets.Item('Ordenes');
wksheet.Paste(wksheet.Range('B10'));

  0 件のコメント

サインインしてコメントする。

採用された回答

Guillaume
Guillaume 2017 年 4 月 6 日
Use PasteSpecial with an XLPasteType of xlPasteValues (-4163) instead of Paste.
Note: I would recommend against using Active... particularly since you have the references to the original object. With Active... you always run the risk that something else gets activated in between your calls. Instead of
ibs.Activate
Sheets = excel.ActiveWorkBook.Sheets;
simply do
Sheets = ibs.Sheets; %Since ActiveWorkbook should be ibs.
So anyway:
excel = actxserver('Excel.Application');
excel.Visible = true;
excel.DisplayAlerts = false;
filename = 'C:\Dropbox\Auryn\ESTRATEGIAS\ibs multiactivo2.xlsm';
filename2 = 'C:\Dropbox\Auryn\ESTRATEGIAS\ZEN REAL2.xlsm';
filename3 = 'C:\Dropbox\Auryn\Ordenes\Recommendations2.xlsx';
ibs = excel.Workbooks.Open(filename);
zen = excel.Workbooks.Open(filename2);
rec = excel.Workbooks.Open(filename3);
pause(60)
zen.Save; %don't need invoke
zen.Close;
ibsheet = ibs.Sheets.Item(5); %no need for get(...)
ibsheet .Range('B64:W104').Copy;
recsheet = rec.Worksheets.Item('Ordenes');
recsheet.Range('B10').PasteSpecial(-4163); %-4163 for xlPasteValues

  1 件のコメント

Adriano
Adriano 2017 年 4 月 6 日
The code works...Many many many thanks Guillaume!!

サインインしてコメントする。

その他の回答 (0 件)

Community Treasure Hunt

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

Start Hunting!

Translated by