使用 Vb.Net Com 插件将多行插入到 Excel 工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15581152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting multiple rows into an Excel Sheet with Vb.Net Com Add-In
提问by Atl LED
After determining a row index for an Excel Worksheet, InsertRow, I would like to insert 16 rows after it using VB.net. I am building a COM add-in if that changes anything. In VBA I would do this via:
在确定 Excel 工作表的行索引 InsertRow 后，我想使用 VB.net 在它之后插入 16 行。如果有任何改变，我正在构建一个 COM 加载项。在 VBA 中，我将通过以下方式执行此操作：
...
Dim InsertRow as String
...
Dim ContractForm As Worksheet
Set ContractForm = Sheets("Lab Contracts")
ContractForm.Select
ContractForm.Rows(InsertRow & ":" & InsertRow).Select
Range(Selection, Selection.Offset(8, 0)).EntireRow.Insert
I can't seem to find a way to do this in VB.net. I've tried: [for all instances of (InsertRow ":" InsertRow) I've also tried ("47:47") and (InsertRow) in case that's not the correct syntax (vice versa).
我似乎无法在 VB.net 中找到一种方法来做到这一点。我试过： [对于 (InsertRow ":" InsertRow) 的所有实例，我也试过 ("47:47") 和 (InsertRow) 以防语法不正确（反之亦然）。
...
ContractSheet.Rows(InsertRow ":" InsertRow).Select()
ContractSheet.Rows.Insert()
...
and ContractSheet.Rows("47:47").Select() ContractSheet.Range("47:47").EntireRow.Insert() and ContractSheet.Rows(InsertRow).Select() For n = 1 To 16 ContractSheet.Range(InsertRow & ":" & InsertRow).rows.insert(xlDown) Next and ContractSheet.Rows(InsertRow).Select() For n = 1 To 16 ContractSheet.Selection.Insert() Next
和 ContractSheet.Rows("47:47").Select() ContractSheet.Range("47:47").EntireRow.Insert() 和 ContractSheet.Rows(InsertRow).Select() 对于 n = 1 到 16 ContractSheet。 Range(InsertRow & ":" & InsertRow).rows.insert(xlDown) Next and ContractSheet.Rows(InsertRow).Select() For n = 1 to 16 ContractSheet.Selection.Insert() Next
and I could go on, but I don't think it would be helpful (as I've always been trying for more than an hour on one issue before asking here). Depending on how it goes I get one of the following errors:
我可以继续，但我认为这不会有帮助（因为在问这里之前，我一直在一个问题上尝试了一个多小时）。根据情况，我会收到以下错误之一：
HResult: 0x800A03EC; or
HResult: 0x800A03EC; 或者
HResult 0x80020005; or
HResult 0x80020005; 或者
Cannot sift Objects off sheet.
无法筛选出工作表外的对象。
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Doug Glancy
It's best to avoid Select statementsunless you have to and you don't have to here. So, just to re-lay the groundwork, in VBA you should do:
最好避免使用 Select 语句，除非您必须这样做，也不必在此处使用。所以，只是为了重新奠定基础，在 VBA 中你应该这样做：
Sub test()
Dim InsertRow As Long
Dim ContractForm As Worksheet
InsertRow = "5"
Set ContractForm = Sheets("Lab Contracts")
ContractForm.Rows(InsertRow).Resize(8).Insert xlShiftDown
End Sub
In VB.Net it's almost the same. (And I guess the xlShiftDown argument is unneeded in either VB or VBA):
在 VB.Net 中几乎是一样的。（我猜在 VB 或 VBA 中不需要 xlShiftDown 参数）：
Imports Microsoft.Office.Interop
Sub test()
Dim ContractForm As Excel.Worksheet
Dim InsertRow As Int32
ContractForm = Application.ActiveWorkbook.Worksheets("Lab Contracts")
InsertRow = 5
ContractForm.Rows(InsertRow).Resize(8).insert() '(Excel.XlInsertShiftDirection.xlShiftDown)
End Sub
