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
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 = "= " & "char(34)" & " & <" & oDocVar.Name & "> & char(34) "
sCSVHeaderVar = "= " & "char(34)" & " & " & oDocVar.Name & " & char(34) "
Else
sCSVVar = sCSVVar & " & " & """" & "," & """" & " & char(34) & <" & _
oDocVar.Name & "> & char(34)"
sCSVHeaderVar = sCSVHeaderVar & " & " & """" & "," & """" & " & char(34) & " & _
oDocVar.Name & " & char(34)"
End If
If oDocVar.Qualification = boDetail Or oDocVar.Qualification = boDimension Then
sDims = sDims & vbNewLine & " " & oDocVar.Name
End If
End If
End Sub
My basic idea is:
- Parse the report for the variable names used
- Create a single variable that is in effect a CSV record, ie. comma seperate the variables collected in step (1)
- Create a new report containing just the new CSV variable
- Export the new report as a text file
- Rename the text file to .csv
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… )
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 (BOB member since 2003-02-25)