Strange results on exporting an Access Form to XL (1 Viewer)

[cyd44]

I have a function to export the results of a query into am XL spreadsheet but am getting strange results. The export appears to take each row in the recordset (up to row 148) and then omits the remaining 10 rows? Instead, it duplicates the first few rows. I end up with the correct number of records but with duplicates as explained.

My Function is as follows:-

Public Function SendToSheet(frm As Form, strSheetName As String, strFilePath As String)
' frm is the name of the form used to query table
' strSheetName is the name of the sheet to copy data to in the XL workbook
' strFilePath is the spreadsheet to use
 
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim MacNm As String
    Dim ClearWSht As String
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
 
    MacNm = "Macro2"
    ClearWSht = "Macro1"
    On Error GoTo err_handler
 
 
    strPath = strFilePath
 
 
    Set rst = frm.RecordsetClone
 
 
    Set ApXL = CreateObject("Excel.Application")
 
 
    Set xlWBk = ApXL.Workbooks.Open(strPath)
 
 
    ApXL.Visible = True
 
    Set xlWSh = xlWBk.Worksheets(strSheetName)
 
     xlWSh.Activate
     xlWSh.Range("A1").Select
 
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
 
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst
 
    xlWSh.Range("1:1").Select
 
 
 
 
 
 
    ' Run Macro of Worksheet to produce graphs data
    xlWBk.Application.Run "'" & strPath & "'!'" & MacNm & "'"
 
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
 
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
 
    ' selects the first cell to unselect all cells
    xlWSh.Activate
    xlWSh.Range("A1").Select
 
    rst.Close
    Set rst = Nothing
    'Run Macro to Clear the import sheet (prevent any error when function is re-used)
     xlWBk.Application.Run "'" & strPath & "'!'" & ClearWSht & "'"
 
Exit_SendToSheet:
    Exit Function
 
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendToSheet
End Function

I have only just realised the problem when I reconciled the results on the spreadsheet to the results on the access form.

There are 157 records in my form and recordset. The XL sheet shows the correct number of records but appears to take the first 148 and then duplicates from record 1 to make up 157. The records are sorted in date order and records have been duplicated after 14/06/2012 (on row 148). There are records beyong 14/06 on my form .

I am totally confused and would appreciate any guidance on this.

 

[Rx_]

Did you figure this out? Will check back in a couple of hours and help you if needed.