VBA - Is it possible to add a section from the code?

Hi all,

I’m trying to write an addin that will let our users export a report as CSV. To be clear, I mean a table in a report, and not simply a data provider.

I’ve done lots of reading on BOB, without which I would have given up ages ago, but now have enough info to be tantelisingly close to getting something working :lol:

Excellent code examples like this one have really helped. This is my code so far:
(It’s not very pretty, and doesn’t cater for much out of the ordinary like blank documents)

Option Explicit

Dim oCSVRep As busobj.Report

Dim oRep As busobj.Report
Dim boSection As busobj.SectionStructure
Dim boItem As busobj.ReportStructureItem
Dim boBlock As busobj.BlockStructure

Dim sCSVVar As String
Dim sCSVHeaderVar As String
Dim sDims As String
Public Sub Export()

On Error Resume Next
ActiveDocument.Reports("ExportCSV").Delete
ActiveDocument.DocumentVariables("ExportCSV").Delete
On Error GoTo 0

Set oCSVRep = ActiveDocument.Reports.Add
oCSVRep.Name = "ExportCSV"

BuildCSVFromReport

ActiveDocument.DocumentVariables.Add sCSVVar, "ExportCSV"

MsgBox "Drag the ExportCSV variable into the ExportCSV " & vbNewLine & _
       "blank report." & vbNewLine & _
       "Then create a section in the Report ExportCSV for each " & vbNewLine & _
       "of the following variables. Then delete the master " & vbNewLine & _
       "cell of each section, but retain the section. " & vbNewLine & _
       sDims & vbNewLine & vbNewLine & _
       "You can now save the report as a text file and voila, " & vbNewLine & _
       "one csv file!" _
      , vbInformation _
      , "Instructions"

'BuildReport
'ExportText
'RenameFile

End Sub

Private Sub BuildCSVFromReport()

Dim ix As Integer
Dim boDocVar As DocumentVariable

sCSVVar = ""
sCSVHeaderVar = ""

Set oRep = ActiveReport
Set boSection = oRep.GeneralSectionStructure


Do Until boSection.IsTerminal = True
    For Each boItem In boSection.Header
        If boItem.Type = boCell Then
            If boItem.ValueType = boDocumentVariable Then
                BuildCSV boItem.Variable
            End If
        End If
    Next boItem
    Set boSection = boSection.SubSectionStructure
Loop

For Each boItem In boSection.Body
    Select Case boItem.Type
    Case boTable
        If boItem.Pivot.RowsCount > 1 _
        Or boItem.Pivot.ColumnsCount > 1 _
        Or boItem.Pivot.BodyCount < 1 Then
            MsgBox "Cannot parse this table for variables. Either RowsCount > 1, ColumnsCount > 1 or BodyCount < 1", vbExclamation
        Else
            For ix = 1 To boItem.Pivot.BodyCount
                Set boDocVar = boItem.Pivot.Body(ix)
                BuildCSV boDocVar
            Next ix
        End If
    Case boCell
        If boItem.ValueType = boDocumentVariable Then
            BuildCSV boItem.Variable
        End If
    End Select
Next boItem

End Sub


Private Sub BuildCSV(oDocVar As busobj.DocumentVariable)
If Len(oDocVar.Name) > 0 Then
    If Len(sCSVVar) = 0 Then
        sCSVVar = "= " &amp; "char(34)" &amp; " &amp; <" &amp; oDocVar.Name &amp; "> &amp; char(34) "
        sCSVHeaderVar = "= " &amp; "char(34)" &amp; " &amp; " &amp; oDocVar.Name &amp; " &amp; char(34) "
    Else
        sCSVVar = sCSVVar &amp; " &amp; " &amp; """" &amp; "," &amp; """" &amp; " &amp; char(34) &amp; <" &amp; _
                oDocVar.Name &amp; "> &amp; char(34)"
        sCSVHeaderVar = sCSVHeaderVar &amp; " &amp; " &amp; """" &amp; "," &amp; """" &amp; " &amp; char(34) &amp; " &amp; _
                oDocVar.Name &amp; " &amp; char(34)"
    End If
    If oDocVar.Qualification = boDetail Or oDocVar.Qualification = boDimension Then
        sDims = sDims &amp; vbNewLine &amp; "    " &amp; oDocVar.Name
    End If
End If
End Sub

:idea: My basic idea is:

  1. Parse the report for the variable names used
  2. Create a single variable that is in effect a CSV record, ie. comma seperate the variables collected in step (1)
  3. Create a new report containing just the new CSV variable
  4. Export the new report as a text file
  5. Rename the text file to .csv

:arrow_forward: My code does 1,2 and kind of 3 so far, but I am stuck on getting the variable to display correctly in the report. :?
With a bit of trial and error, I came up with the instructions that are displayed in the MsgBox, ie. for every dimension/detail in the source report, create a Section in the new report. Put the CSV variable then in the body, and it works. You then delete the master cell, while retaining the section, otherwise the master cell gets exported too.

(and finally we get to the question… :slight_smile: )
:?: Using VBA, can I automate creating the sections in the new report? I can’t find any method that will add a section.
I’ll admit that I don’t comprehend 100% the BusObj object model w.r.t. Sections etc, so would be more than happy if someone can point out something I’ve overlooked/misunderstood.

Thanks, Robin.


RobinM :uk: (BOB member since 2003-02-25)

Looks like some excellent work, but I hope your solution doesn’t rely on that functionality. It does not seem possible to add a section using VBA :nonod: .


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)