I am trying to copy a table from Access over to Excel. I have used this code before with no issue.
The table consists of some 27,000 rows but the routine stops at 6,443 with the "copy from recordset
of object range failed". There are memo fields in the table.
Function Create_Template()
' step 1 declare the objects
' This function takes the results and formats it into the excel sheet
Dim db As dao.Database
Dim rs As dao.Recordset
Dim i As Integer
Dim strFileName As String
Dim FilePath As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Combined Quality Tracker")
FilePath = "\\Report\Pre - Post\Input Files\"
strFileName = FilePath & "Post-test.xlsm"
' This code fires the Excel from Access.
Dim xl As Excel.Application
Dim xlwkbk As Excel.Workbook
Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Open(strFileName)
xl.Visible = True
xl.Run "Delete_Sheet"
' paste active sheet
xl.Range("A2").CopyFromRecordset rs
db.Close
xlwkbk.Save
xlwkbk.Close (True)
xl.Quit
Set xl = Nothing
Set xlwkbk = Nothing
Set rs = Nothing
Set db = Nothing
End Function
The table consists of some 27,000 rows but the routine stops at 6,443 with the "copy from recordset
of object range failed". There are memo fields in the table.
Function Create_Template()
' step 1 declare the objects
' This function takes the results and formats it into the excel sheet
Dim db As dao.Database
Dim rs As dao.Recordset
Dim i As Integer
Dim strFileName As String
Dim FilePath As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Combined Quality Tracker")
FilePath = "\\Report\Pre - Post\Input Files\"
strFileName = FilePath & "Post-test.xlsm"
' This code fires the Excel from Access.
Dim xl As Excel.Application
Dim xlwkbk As Excel.Workbook
Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Open(strFileName)
xl.Visible = True
xl.Run "Delete_Sheet"
' paste active sheet
xl.Range("A2").CopyFromRecordset rs
db.Close
xlwkbk.Save
xlwkbk.Close (True)
xl.Quit
Set xl = Nothing
Set xlwkbk = Nothing
Set rs = Nothing
Set db = Nothing
End Function