Spreadbuilder Question

Posted by: itiasreo on 4 August 2017, 2:55 pm EST

  • Posted 4 August 2017, 2:55 pm EST


    I have a decent amount of reports that run different query strings. I have added an Export to Excel button onto the viewer toolbar. When I export to excel the entire report is exported; header, footer, etc. I am currently using the XLSExport feature. Is there a way to export just the SQL select field names and the data associated with the fields? If possible could you please provide code.

    I understand how to use the spreadbuilder and <SPAN>statically add the field names and data, but I would like to make this dynamic so that when a different report loads different information is populated into excel according to the SQL query strings.</SPAN>

    <SPAN>Thank You,</SPAN>


  • Replied 4 August 2017, 2:55 pm EST

    Hello ,

    You can create the excel file by spread builder in the report's code.

    Here is a sample of code:

    'Code of main program

    <FONT size=2>Dim ar As New MyReport
    ar.SpreadBuilderEnabled = True

    'Report's code

    <FONT size=2>Public SpreadBuilderEnabled As Boolean
    Dim SB As ActiveReportsExcelExport.SpreadBuilder
    Dim j</FONT>

    <FONT size=2>Private Sub ActiveReport_DataInitialize()
        j = 2
    End Sub</FONT>

    <FONT size=2>Private Sub ActiveReport_FetchData(EOF As Boolean)
    If SpreadBuilderEnabled Then
    On Error Resume Next
        If EOF = False Then
            For i = 0 To Fields.Count - 1
                SB.Sheets("Sheet1").Cell(j, i).Value = CStr(Fields(i).Value)
                If Err.Number = 5017 Then
                    SB.Sheets("Sheet1").Cell(j, i).Type = SBBlank
                    SB.Sheets("Sheet1").Cell(j, i).Value = ""
                End If
            j = j + 1
            SB.Save ("c:\temp.xls")
            Set SB = Nothing
            MsgBox "c:\temp.xls"
        End If
    End If
    End Sub</FONT>

    <FONT size=2>Private Sub ActiveReport_ReportStart()
    If SpreadBuilderEnabled Then
        Set SB = New SpreadBuilder
        SB.Sheets.Add ("Sheet1")
        For i = 0 To Fields.Count - 1
            SB.Sheets("Sheet1").Cell(0, i).Value = Fields(i).Name
            SB.Sheets("Sheet1").Cell(0, i).BorderBottomStyle = 1
        Next i
    End If
    End Sub</FONT>

    Sergey Romanov.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels