Excel VBA常用代码VSTO版20150425

21-1 使用工作表的名称this.Application.Worksheets["工作表2"].Activate();21-2 使用工作的索引号this.Application.Worksheets[2].Activate();21-3 使用工作表的代码名称MessageBox.Show(this.Application.ActiveSheet.CodeName);21-4 用ActiveSheet属性引用活动工作表this.Application.Worksheets[2].Select();MessageBox.Show( );22-1 选择工作表的方法this.Application.Worksheets[2].Select();this.Application.Worksheets[2].Activate();23-1 使用For遍历工作表int wkCount = this.Application.Worksheets.Count;string s = string.Empty;for (int i = 1; i <= wkCount; i++){s = s + this.Application.Worksheets[i].Name + "\n";}MessageBox.Show("工作簿中含有以下工作表:" + "\n" + s);23-2 使用ForEach语句string s = string.Empty;foreach (Excel.Worksheet wk in this.Application.Worksheets) {s = s + + "\n";}MessageBox.Show("工作簿中含有以下工作表:" + "\n" + s);24-1 在工作表中向下翻页Excel.Sheets shs=Globals.ThisWorkbook.Worksheets; Excel.Worksheet wkThis = shs.Application.ActiveSheet; Excel.Worksheet wkNext;int wkIndex = wkThis.Index;int wkCount = shs.Count;if (wkIndex < wkCount){wkNext = (Excel.Worksheet)wkThis.Next;wkNext.Select();}25-1 工作表的添加与删除Excel.Sheets wksThis = this.Application.Worksheets;Excel.Worksheet wsAdd = this.Application.Worksheets.Add(System.Type.Missing, wksThis[wksThis.Count]); = "数据";25-1 批量添加工作表Excel.Sheets wksThis = this.Application.Worksheets;Excel.Worksheet wksNew = null;if (wksThis.Count <= 3){for (int i = 1; i <= 10; i++){wksNew = wksThis.Add(System.Type.Missing, wksThis[wksThis.Count]); = "第" + i.ToString() + "个工作表";}}26-1 禁止删除指定工作表mandBarControl cmdCtl =mandBars[41].Controls[2];可以找到删除按钮,但是无法禁止,也无法加载单击事件,非常奇怪.而且在Office 2010里,也无法禁用某个按钮,但是整个菜单是可以的.27-1 自动建立工作表目录int i = this.Application.Worksheets.Count;for (int n = 1; n <= i; n++){this.Cells[n+1, 1].Value = this.Application.Worksheets[n].Name;}27-1 建立工作表链接int m = this.Application.Worksheets.Count;if (Target.Count == 1){if (Target.Column==1){if (Target.Row>1 && Target.Row<=(m+1)){this.Application.Sheets[Target.Value].Select();}}}28-1 工作表的深度隐藏this.Application.Sheets[2].Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;29-1 防止更改工作表的名称void ThisWorkbook_BeforeClose(ref bool Cancel){if (this.Sheets[1].Name != "Excel Home"){this.Sheets[1].Name = "Excel Home";}this.Save();}30-1 工作表中一次插入多行Excel.Range rng = this.Rows[3];rng.Resize[3].Insert();31-1 删除工作表中的空行Excel.Range rng = edRange;int rngEnd = this.Cells[rng.Rows.Count,rng.Columns.Count].End[Excel.XlDirection.xlUp].Row;for (int i = rngEnd; i >=1; i++){if (this.Application.WorksheetFunction.CountA(this.Rows[i]) == 0){this.Rows[i].Delete();}}32-1 删除工作表的重复行int rngEnd = this.Range["A65535"].End[Excel.XlDirection.xlUp].Row;for (int i = rngEnd; i>=1; i--){if (this.Application.WorksheetFunction.CountIf(this.Columns[1], this.Cells[i, 1]) > 1){this.Rows[i].Delete();}}33-1 定位删除特定内容所在的行(删除A列中包含”Excel”字符的行this.Application.DisplayAlerts = false;int rngEnd = this.Range["A65535"].End[Excel.XlDirection.xlUp].Row;string str = "Excel.*";for (int i = rngEnd; i >= 1; i--){Excel.Range rng = this.Cells[i, 1];if (Regex.IsMatch(rng.Text, str)){this.Rows[i].Delete();}}注:需引用using System.Text.RegularExpressions;34-1 判断是否选中整行int i = this.Columns.Count;Excel.Range rng = this.Application.Selection;if (rng.Columns.Count == i){MessageBox.Show("你选中了一整行");}else{MessageBox.Show("你没有选中了一整行");}35-1 限制工作表的滚动区域this.ScrollArea = "B4:H12";36-1 复制自动筛选后的数据区域this.Application.Worksheets[2].Cells.Clear();if (this.FilterMode){this.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy( this.Application.Worksheets[2].Cells[1, 1]);}37-1 使用高级筛选获得不重复记录Excel.Range rngSheet2 = this.Application.Worksheets[2].Cells;rngSheet2.Clear();this.Range["A1"].CurrentRegion.AdvancedFilter(Excel.XlFilterAction.xlFilterCopy,System.Type.Missing,this.Application.Worksheets[2].Cells[1, 1],true);38-1 工作表的保护与解除保护this.Unprotect("12345");this.Cells[1,1].Value=100;this.Protect("12345");39-1 奇偶页打印int pg = this.PageSetup.Pages.Count;for (int i = 1; i <= pg; i=i+2){this.PrintOutEx(1, i);}40-1 使用工作簿的名称string str = this.Application.Workbooks["工作簿的引用方法.xlsx"].Path;MessageBox.Show(str);40-3 使用ThisWorkbookthis.Application.ThisWorkbook.Close(false);40-4 使用ActiveWorkbookMessageBox.Show();41-1 新建工作簿Excel.Workbook Nowbook;string[] shName = new string[4] { "余额", "单价", "数量", "金额" };string[] arr = new string[12] { "01月", "02月", "03月", "04月", "05月", "06月", "07月", "08月", "09月", "10月", "11月", "12月" };this.Application.SheetsInNewWorkbook = 4;Nowbook = this.Application.Workbooks.Add();for (int i = 1; i <= 4; i++){Nowbook.Sheets[i].Name = shName[i - 1];Nowbook.Sheets[i].Range["B1"].Resize[1, arr.Length] = arr;Nowbook.Sheets[i].Range["B2"] = "品名";}Nowbook.SaveAs("C:\\" +"存货明细.xlsx");Nowbook.Close(true);42-1 打开指定的工作簿int wkCount = this.Application.Workbooks.Count;for (int i = 1; i <= wkCount; i++){if (this.Application.Workbooks[i].Name == "123.xlsx"){MessageBox.Show("123工作簿已经打开");}}this.Application.Workbooks.Open("C:\\" + "123.xlsx");。