CopyFromRecordset not exporting all rows from Access to Excel

[jgelpi16]

Hello,
I'm using the below code to attempt to export data from Access to an Excel template (Access & Excel 2010). The export works fine with one exception: Only the first ~150-200 records export when I'm expecting to export over 1,000. Any ideas what I'm doing wrong? I have had others in my office look at it and we just can't seem to figure it out.

    
    Dim db1                      As DAO.Database    
    Dim rs1                      As DAO.Recordset
    
    Dim mySQL1                   As String
    Dim strSheet1                As String
    Dim strFolder1               As String
    Dim strFolderCopy1           As String
    Dim strFileTemp1             As String
    Dim strFile1                 As String
    Dim strRange1                As String
    
    Dim xlApp                   As Object
    Dim xlSheet                 As Object
    Dim fso1                     As New FileSystemObject
    
    Dim ExcelRunning            As Boolean
    
    Set db1 = CurrentDb
    
    ExcelRunning = IsExcelRunning()
    
    If ExcelRunning Then
        Set xlApp = GetObject(, "Excel.Application")
        ExcelRunning = True
    Else
        Set xlApp = CreateObject("Excel.Application")
        ExcelRunning = False
    End If
    
    mySQL1 = "SELECT * from QRY_SEL_SCORECARD_DATA_EXPORT_RGN;"
    Set rs1 = db1.OpenRecordset(mySQL1, dbOpenDynaset)
    rs1.MoveLast
    rs1.MoveFirst
    
    strSheet1 = "PerfData"
    strFolder1 = "\\Ncmain\users\COMMON_HR\#HRIS\Reports\Performance_Scorecard\Backend_Data\"
    strFolderCopy1 = "\\Ncmain\users\COMMON_HR\#HRIS\Reports\Performance_Scorecard\Scorecard_Export\"
    strFileTemp1 = "PerformanceScorecard_TEMPLATE.xlsx"
    strFile1 = "PerformanceScorecard_Region_" & strReg & "_" & Format(Date, "yyyymmdd") & ".xlsx"
    'strFile = "PerformanceScorecard_" & Format(Date, "yyyymmdd") & ".xlsx"
    strRange1 = "B4:Y4"
    
    fso1.CopyFile strFolder1 & strFileTemp1, strFolderCopy1 & strFile1, True
    
'    Set objMyWorkbook = objApp.Workbooks.Open(strFolder & strFile)
    Set xlSheet = xlApp.Workbooks.Open(strFolderCopy1 & strFile1)
    xlSheet.Sheets(strSheet1).Range(strRange1).CopyFromRecordset rs1
    rs1.Close
    
    With xlApp.Application
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
    End With
    
    If Not ExcelRunning Then
        xlApp.Quit
    End If

[ranman256]

Just a note :
Set rs1 = db1.OpenRecordset(mySQL1, dbOpenDynaset)
rs1.MoveLast
rs1.MoveFirst
THE
rs1.MoveLast
rs1.MoveFirst
is not needed.


BUT.. you have a limiter I think...strRange1 = "B4:Y4"
Just put it in 1 cell like:

ActiveWorkbook.Worksheets("Sheet1").Range("B4").Co pyFromRecordset


Let the recordset decide how many cells it wants to use.

[June7]

Cannot replicate the issue, even with range limit. Would have to examine and test with your data.

[jgelpi16]

Hmmmm....Is there any limitation/timeout that you all are aware of when it comes to DAO export using CopyFromRecordset? My source data is simply a query that is limited by Region (returning roughly 1,200 in my test scenario). As you can see I'm not doing anything special with the data. I'm even stopping before I get to the formatting of the template.

[June7]

Not that I am aware of. I had no problem exporting 960 (unfiltered) records.