vb.net“Range 类的 PasteSpecial 方法失败”

vb.net "PasteSpecial method of Range class failed"

提问by Knyq

I'm trying to write an application in VB.net that assembles an Excel Workbook by wisely coping cells from another opened Workbook. [Note: as for now, the two workbooks are opened within the same Excel application - Originally I was using two different Excel instances, but only later I realized that the PasteSpecial between two Instances behaves differently]

我正在尝试在 VB.net 中编写一个应用程序，通过明智地处理另一个打开的工作簿中的单元格来组装 Excel 工作簿。[注意：至于现在，这两个工作簿是在同一个 Excel 应用程序中打开的 - 最初我使用的是两个不同的 Excel 实例，但直到后来我才意识到两个实例之间的 PasteSpecial 表现不同]

I'm using Visual Studio 2012, Excel 2007 and I'm including Microsoft Excel 12.0 Object Library in the project references

我使用的是 Visual Studio 2012、Excel 2007，并且在项目引用中包含了 Microsoft Excel 12.0 对象库

The code is something like that:

代码是这样的：

Dim appXL As Excel.Application
Dim wbXLsource As Excel.Workbook
Dim wbXLtarget As Excel.Workbook

''with two different buttonclick event handlers 
''I assign wbXLsource and wbXLtarget
''the full code is omitted
...
wbXLsource = appXL.Workbooks.Open(strFileNameAndPath)
...
...
wbXLtarget = appXL.Workbooks.Add
...


''I use a third button handler for the 
''Copy and PasteSpecial Operations
    Private Sub btnAppendWorksheet_Click(sender As Object, e As EventArgs) _
    Handles btnAppendWorksheet.Click

    Dim shXLtar As Excel.Worksheet
    Dim shXLsou As Excel.Worksheet

    shXLtar = wbXLtarget.ActiveSheet
    shXLtar.Cells.Clear()
    shXLsou = wbXLsource.ActiveSheet

    shXLsou.Range("A1:H433").Copy()
    Try
        shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll, False, False) ''Paste special Format:=
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

The PasteSpecial method throws the exception "PasteSpecial method of Range class failed".

PasteSpecial 方法抛出异常“Range 类的 PasteSpecial 方法失败”。

What is strange is that the same code originally worked within two workbooks that run in different Excel instances [At that time I had appXLtarget and appXLsource]. Needless to say that I tried all the possible combinations of "Selection", "Activate" in any part of the code: eg between Copy and PasteSpecial etc etc. Probably there is something really coarse that I'm missing <- I'm new of VB.net

奇怪的是，相同的代码最初在运行在不同 Excel 实例中的两个工作簿中工作[当时我有 appXLtarget 和 appXLsource]。不用说，我在代码的任何部分尝试了“选择”、“激活”的所有可能组合：例如在 Copy 和 PasteSpecial 等之间。可能有一些我遗漏的非常粗糙的东西 <- 我是新手VB.net的

Thanks for any help and Best Regards!

感谢您的帮助和最好的问候！

回答by Chris

If you are new in VB.Net, you should first do research about OptionStrict. With optionStrict set to ON, VS won't compile your code...

如果您是 VB.Net 的新手，您应该首先研究 OptionStrict。将 optionStrict 设置为 ON，VS 将不会编译您的代码...

Replace shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll, False, False)

代替 shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll, False, False)

With shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll,Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,False, False)

和 shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll,Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,False, False)

or

或者

shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll)

shXLtar.Range("A1:H433").PasteSpecial(Excel.XlPasteType.xlPasteAll)

Hope this helps.

希望这可以帮助。