如何以编程方式在Excel中组合（移位选择）工作表 [英] How to programmatically group (shift select) sheets in Excel

问题描述

此主题涉及：什么对象类型是Excel中的多个选定的工作表？

为了概括说明问题，我运行一些在Excel中更改我的选择的代码，我想返回选择它原来是什么（我称之为原始选择）。在大多数情况下，您只需调用originalSelection上的Select（）方法。

To give an overview of the problem, I run some code that changes my selection in Excel, and I want to return the selection to what it originally was (I call this "originalSelection"). In most instances, you can just invoke the Select() method on the originalSelection.

var originalSelection = ExcelApp.Selection;
originalSelection.GetType().InvokeMember("Select", System.Reflection.BindingFlags.InvokeMethod, null, originalSelection , null);

选择多个工作表时，选择类型始终为范围（这是Excel默认设置）。但是，如果选择了多个工作表，则尝试再次调用选择时可能会遇到错误。您需要做一些跳舞才能让工作起来。

With multiple sheets selected, the selection type is always of a range (it's what Excel defaults to). However, if you have multiple sheets selected, you can run into errors when trying to invoke Select again. You need to do some dancing around to get things to work.

如果选择了多张表格，但不是所有的表格，您可以执行以下操作：

If multiple sheets are selected, but not all the sheets, you can do the follow:

selectedSheets.Select();
activeSheet.Activate();
originalSelection.Select(); //this was casted to an Excel.Range

但是，如果选择了所有工作表， code> activeSheet.Activate（）行将取消选择所有其他选定的工作表。如果您使用UI本身尝试，也会发生这种情况。

However, if all the sheets are selected, the activeSheet.Activate() line deselects all the other selected sheets. This also happens if you try it natively using the UI.

我想知道是否有办法通过代码逐个模拟移位选择页面？我找到的最近的东西是范围分组的东西，但没有任何表格。

I was wondering if there is a way to pragmatically mimic shift-selecting sheets one by one through the code? Closest stuff I've found is stuff with range groupings, but nothing for sheets.

我试图保持我的概述，但如果你需要更多的澄清，请问。

I tried to keep my overview brief, but if you need more clarification on what I'm doing, just ask.

推荐答案

所以我想出了一种以程序方式选择工作表的方式。

So I figured out a way programatically select sheets.

您可以创建一个名称的字符串数组，并使用数组的顺序来获取工作表的集合。选择此集合，您应该选择所有指定的工作表。

You can create a string array of names, and use the ordering of the array to get a collection of the sheets. Select this collection, and you should have all the specified sheets selected.

String[] sheetsToBeSelected = {"Sheet3","Sheet1","Sheet2"}; //Note, Sheet3 is the first item in this array
excel.Workbook workbook = ExcelApp.ActiveWorkbook; //get your Excel application however you want
excel.Sheets worksheets = workbook.Worksheets; //get all the sheets in this workbook

//This gets a collection of the sheets specified and ordered by the array of names passed in. 
//Just call select on this collection, and the first sheet in the collection becomes the active sheet!
((excel.Sheets)worksheets.get_Item(sheetsToBeSelected)).Select(); 

这篇关于如何以编程方式在Excel中组合（移位选择）工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！