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()
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()