清空筛选条件
清空筛选条件

Port Excel VBA "ActiveCel​l.SpecialC​ells(xlLas​tCell).Sel​ect" (ctrl-end) and "Range(Selection, ActiveCell​.SpecialCe​lls(xlLast​Cell)).Sel​ect" (ctrl-shift-end) to MATLAB using ActiveX Connection

33 次查看（过去 30 天）
Shawn
Shawn 2017-11-29
编辑： Shawn 2017-11-29
I'm try to port over two VBA commands. The following example assumes that you fill in file_directory, file_name, and sheet_name with strings for an actual Excel File.
%Open an ActiveX connection to Excel
try
h = actxGetRunningServer('excel.application');
catch
try
h = actxserver('excel.application');
catch
disp('MATLAB was unable to obtain an ActiveX connection to Excel.')
return
end
end
%Open a workbook and select sheet
wb=h.WorkBooks.Open(fullfile(file_directory,file_name));
wbs=h.ActiveWorkBook.Sheets;
wbs.Item(sheet_name).Select;
%Select cell A1
getA1 = h.Activesheet.get('Range','A1');
selA1 = getA1.Select;
Now that I am at cell A1, I want to be able to perform "ctrl-end" and "ctrl-shift-end" in Excel. The recorded VBA from a macro for these two keyboard commands are "ActiveCell.SpecialCells(xlLastCell).Select" and "Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select", respectively.
I have tried a few ideas to no avail such as
h.Selection.SpecialCells(1)
and
h.Selection.SpecialCells.Item('xlLastCell')
Any help would be greatly appreciated. Thanks.

请先登录，再回答此问题。

采纳的回答

Shawn
Shawn 2017-11-29
编辑：Shawn 2017-11-29
I was able to figure it out. Once I have an active sheet selected, I can then do
%get cell A1
getA1 = h.Activesheet.get('Range','A1');
%select cell A1
selA1 = getA1.Select;
%set cell A1 as the current cell
currentcell = h.Selection;
%apply ctrl-end and select that cell
currentcell.SpecialCells(11).Select % ctrl-end is special cells item 11
%get the address from the selected cell
selectedcell = strrep(h.Selection.Address,'$','');
%get the constructed range from ctrl-end to immitate ctrl-shift-end
getctrlshiftend = h.Activesheet.get('Range',['A1:' selectedcell]);
%select the cells as you would with ctrl-shift-end
selctrlshiftend = getctrlshiftend.Select;
  1 个评论
Shawn
Shawn 2017-11-29
The effect could also be constructed from
rownum = h.Activesheet.UsedRange.Rows.Count;
colnum = h.Activesheet.UsedRange.Columns.Count;
if you use a column number to column letter converter such as xlscol on the file exchange.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by