Bob Larsons Send to Excel

[Dazzy]

Hey all

We have using Bob Larson's excellent SendtoExcel module in our project http://www.btabdevelopment.com/ts/default.aspx?PageId=47 and are overall very happy.

I however would like to stop it exporting the AuditTrail field (memo type).

Can anyone one help?

Thanks

 

[pr2-eugin]

In the Code Bob uses

Set rst = frm.RecordsetClone

You can have an SELECT Query there instead, this way you can specify what fields you want to export. Instead of Passing the whole Form to the function you can pass just the ID of the current record or something this way you only export what you want.

Set rst = CurrentDB.OpenRecordset("SELECT tableName.IDField, tableName.TextField, tableName.DateField, " & _
                                  "tableName.CurrencyField FROM tableName " & _
                                  "WHERE tableName.IDField = " & frmID)

 

[Dazzy]

Hi

Thanks for your reply.

I am not sure if that will work for our situation, we use this in many places throughout the database and each time its called it calls a different recordset.

Maybe an easier way would be exclude all memo type fields? We only use a couple of these Notes and audittrail and neither are really needed when exported.

Thanks again

 

[pr2-eugin]

In that case, I know it might not always be ideal the code could be modified as,

Public Function Send2Excel(frm As Form, [COLOR=Red][B]Optional changeSource As Boolean, Optional newSource As String,[/B][/COLOR] _
                            Optional strSheetName As String)
[COLOR=Green]' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to[/COLOR]

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107

    On Error GoTo err_handler
    [COLOR=Red][B]If changeSource And Len(newSource) > 0 Then
        Set rst = newSource
    Else[/B][/COLOR]
        Set rst = frm.RecordsetClone
   [COLOR=Red][B] End If[/B][/COLOR]
    
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
       
    Set xlWSh = xlWBk.Worksheets("Sheet1")
    If Len(strSheetName) > 0 Then
        xlWSh.Name = Left(strSheetName, 34)
    End If
    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
    [COLOR=Green]' This is included to show some of what you can do about formatting. 
    'You can comment out or delete
    ' any of this below that you don't want to
    'use in your own export.[/COLOR]
    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
    [COLOR=Green]' selects all of the cells[/COLOR]
    ApXL.ActiveSheet.Cells.Select
    [COLOR=Green]' does the "autofit" for all columns[/COLOR]
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    [COLOR=Green]' selects the first cell to unselect all cells[/COLOR]
    xlWSh.Range("A1").Select

    rst.Close
    Set rst = Nothing

    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function
End Function

You can then call the function as you wish, for the normal export just use Send2Excel Me for something where you only wish to export few info you cna use.

Send2Excel Me, True, "SELECT someFields FROM table WHERE ID = " & Me.ID

Again, you do not have to, but it could still be an option

 

[vbaInet]

Maybe an easier way would be exclude all memo type fields?

If you don't know how to string together a good SQL or write VBA then yes, exlcude the memo field from the source. You can still have the memo field as an unbound control or in a subform.