CODEWITHSUNDEEP
vb.netexcelexceptioncopy-paste
Knyq
Knyq

Reputation: 25

vb.net "PasteSpecial method of Range class failed"

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]

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

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".

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

Thanks for any help and Best Regards!

Upvotes: 0

Views: 8630

Answers (1)

Chris
Chris

Reputation: 8647

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...

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

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

or

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

Hope this helps.

Upvotes: 1

Related Questions