MATLAB Answers

Paste values in excel with MATLAB COM activeX

47 views (last 30 days)
Adriano
Adriano on 6 Apr 2017
Commented: Adriano on 6 Apr 2017
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 Comments

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 6 Apr 2017
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 Comment

Adriano
Adriano on 6 Apr 2017
The code works...Many many many thanks Guillaume!!

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!