I have a form which contains filtered information from a query. My form shows 157 records. I have placed a button on the form which calls a function to export the form data to a macro enabled workbook. The export takes place and poulates the specified worksheet with 157 records. However, the data is not as expected.
The export appears to take the first 148 rows of the form and export them OK. It then duplicates the first few rows to make up the 157 rows of the form.
My export function is:-
I was quite happy with this at first but when I tried to reconcile spreadsheet with form data I noticed the problem with the data.
I am not an expert in VBA and am obviously doing something wrong but I cannot fathom out the logic of why it is only copying 148 rows of my form and then duplicates the first few to make up the difference?
I have posted a similar thread but have not received any answer.
Could anyone please advise me
The export appears to take the first 148 rows of the form and export them OK. It then duplicates the first few rows to make up the 157 rows of the form.
My export function is:-
Code:
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("A1").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' Formatting
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' 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 FunctionI was quite happy with this at first but when I tried to reconcile spreadsheet with form data I noticed the problem with the data.
I am not an expert in VBA and am obviously doing something wrong but I cannot fathom out the logic of why it is only copying 148 rows of my form and then duplicates the first few to make up the difference?
I have posted a similar thread but have not received any answer.
Could anyone please advise me
