Question Excel - Copy method of Range class failed

val81

New member
Joined
Sep 18, 2010
Messages
1
Programming Experience
3-5
Part ot my program is to simple copy a range of cells ~21 000 rows from one WB (oSH) and copy it to another WB (oSHOut).
I've red a lot of posts,tried a lot of variants, but nothing helps.
Strange here is that if I copy less than 8500 rows, the paste to the destination range is OK.
But if I copy the whole range ~21000 rows, vb.net trigers an error - "Copy method of Range class failed"
So I tried to do this in several parts - 1 row to 8000, 8001 to 16000 ect.
The first copy-paste works OK, on the secon - there's an error

Imports Microsoft.Office.Interop
Imports System.Data.Odbc
.
.
.

oRNG = Nothing
oRNGOut = Nothing
oSH = Nothing
oSHOut = Nothing


oAccess.DoCmd.RunMacro("queryo1s")

oExcel.Workbooks.Open("D:\NAL\_queryo1s.xls")

oExcel.Workbooks.Open("D:\NAL\_Katalog")

oSH = oExcel.Workbooks("_queryo1s").Worksheets("o1squery")
oSHOut = oExcel.Workbooks("_Katalog").Worksheets("ALL_O1S")

NumOfRows = oExcel.Application.WorksheetFunction.CountA(oSH.Range("A:A"))

If NumOfRows > 24000 Then MessageBox.Show("Sheet ALL_O1S съдържа повече от 24 000 реда - " & NumOfRows, "", MessageBoxButtons.OK, MessageBoxIcon.Information)

'----------------------------------------------------------------------------1
oRNG = oSH.Range("A2:E8000") ' & NumOfRows)
oRNGOut = oSHOut.Range("A3")

oRNG.Copy(oRNGOut)
'----------------------------------------------------------------------------2
oRNG = oSH.Range("A8001:E16000")
NumOfRows = oExcel.Application.WorksheetFunction.CountA(oSHOut.Range("A:A")) + 2
oRNGOut = oSHOut.Range("A" & NumOfRows)

oRNG.Copy(oRNGOut) ' <--- HERE VB TRIGERS AN ERROR (Second attempt of paste)
'----------------------------------------------------------------------------3
oRNG = oSH.Range("A16001:E" & NumOfRows)
NumOfRows=oExcel.Application.WorksheetFunction.CountA(oSHOut.Range("A:A")) + 2
oRNGOut = oSHOut.Range("A" & NumOfRows)

oRNG.Copy(oRNGOut)

NumOfRows = 0
oRNG = Nothing
oSH = Nothing
oSHOut = Nothing
Clipboard.Clear()
 
Top Bottom