Include calculated controls when using recordsets (1 Viewer)

[JamesMcS]

Morning campers!

I've got a bit of VBA that exports the recordset of the form it's called from to Excel. It works fine, but there are a few calculated controls that sum up the data on the form, which I'd also like to export. Any ideas?

Thanks!

 

[CBrighton]

Depends how the bit of VBA works.

Can you post the code you use for the export please?

 

[vbaInet]

DoCmd.OutputTo instead of DoCmd.TransferSpreadsheet.

Export the form, not the record source.

 

[JamesMcS]

Here it is:

Public Sub Export_To_Excel(Called_From As Form)
Dim Rst As DAO.Recordset
Dim XLApp, XLBook, XLSheet As Object
Dim RowNumber As Integer
Dim Report_Description As String
Set Rst = Called_From.RecordsetClone
Set XLApp = CreateObject("Excel.Application")
Set XLBook = XLApp.Workbooks.Add
Set XLSheet = XLBook.Worksheets("Sheet1")
RowNumber = 3
Report_Description = Right(Called_From.RecordSource, Len(Called_From.RecordSource) - InStr(Called_From.RecordSource, " "))
XLSheet.Name = Report_Description
XLSheet.range("A1").Value = Report_Description

'Create Header Row
XLSheet.range("A2").Select
For Each Field In Rst.Fields
    XLApp.activecell = Field.Name
    XLApp.activecell.offset(0, 1).Select
    
Next
'Append Data from recordset
XLSheet.range("A3").Select
Rst.MoveFirst
Do Until Rst.EOF
If Rst.Fields("Tagged") = -1 Then
    For n = 0 To Rst.Fields.Count - 1
    
        XLApp.activecell = Rst.Fields(n)
        XLApp.activecell.offset(0, 1).Select
        
    Next
    
        Rst.MoveNext
        RowNumber = RowNumber + 1
        XLSheet.Cells(RowNumber, 1).Select
        
Else
Rst.MoveNext
End If
Loop
XLApp.Visible = True
End Sub

I tried outputto and transferspreadsheet but they didn't quite give me what I wanted. I think I posted some threads a while back whilst writing this bit of code which explained why (I forget now)

 

[vbaInet]

If you're talking about summing the data at the bottom, then open the form, get the value from the form and paste it into the cell after reading all the values from the recordset.

Or

Use one of the functions in Excel to do so.

Records exported to excel via a Recordset must exist in the recordset. So you can always include the calculation in the record source of the form if they are calculated on each row.

 

[JamesMcS]

Ah OK - I'll have a play about with both of those and see which works best.

Thanks! Hope everyone's having as fun a day as me!

 

[vbaInet]

Thanks! Hope everyone's having as fun a day as me!

As always!

 

[JamesMcS]

No place for misery and bah-humbuggery with Access... it's all fun and games

 

[CBrighton]

include the calculation in the record source of the form

This is what I'd do, add the calc'd values as fields in a query bound to the form.