Read table field in BO and assign to string in VBA macro

sdk development
1

Hello

Somehow I’m unable. Since hours I google around and search through the BOB board, but I don’t find a solution which works completely. :hb:

Basically I let the user select a team name in a listbox. For each selected team exists a fix additional value. I would like to have that value now assigned to string in my macro, that I can filter a tab in BO based on that string.
Since my VBA knowlege is very limited I thought of applying a filter on a specific tab which will show me the team with its additional value. That works so far. However, how can I address this field and assign it to a string variable in VBA?

I tried with stuff like Application.CmdBars.Item(1).Controls.Item(1), …, since we use that in another report to copy a field over to an Excel application, but I can’t recode it to match it for my needs. I also tried stuff with ActiveReport.GeneralSectionStructure.Body.Item(1)… It seems I can count the bodies on a BO tab with latter apporach, but not get a specific field name like that…

Does anybody have a spontaneous idea how I could reach my goal? Any help highly appreciated!
Thanks alot!

Silverhawk (BOB member since 2008-03-05)

2

Ok, getting closer to the solution. Found some code in the web and changed it to the code below. I receive like that the variable name in the table header. Unfortunately I would like the variable content from the table body… :roll_eyes:

However, at least a part of the “solution”:

Public mystring As String
Dim Doc As busobj.Document
Dim Rep As busobj.Report
Dim SecStruct As busobj.SectionStructure
Dim I As Integer
Dim boDocVar As busobj.DocumentVariable
Dim boBlock As busobj.BlockStructure

Set Doc = Application.ActiveDocument
Set Rep = Doc.ActiveReport
Set SecStruct = Rep.GeneralSectionStructure

For I = 1 To SecStruct.Body.Count
Set boBlock = SecStruct.Body(I)
Select Case boBlock.Type

Case boTable

Set boDocVar = boBlock.Pivot.Body(1)
mystring = boDocVar.Name

End Select
Next I
Rep.ForceCompute

Silverhawk (BOB member since 2008-03-05)

3

Getting even closer:

Add:
Dim getit As Variant

Adjust few code lines:

Set boDocVar = boBlock.Pivot.Body(1)
getit = boDocVar.Values(boUniqueValues)
mystring = getit(1)

Now we have the first entry from the variable…but not the one I filtered to on the BO tab… :reallymad:

Silverhawk (BOB member since 2008-03-05)

4

…Seems one can’t access table content in BO…just variable names, header names, formulas, etc.

Variable content can only be accessed over the data provider, but there it is only the full monty (no limitation with filtering possible)…

I give up…trying around over 12 hours now… :hb:

Silverhawk (BOB member since 2008-03-05)

5

Hi, BO works only with DataProvider - not with representation (filters, etc). See my post 2 weeks ago:

With filters only you can save report to Excel whole document.
Sample:

Private Sub Document_AfterRefresh()
    Dim Var, Var2 As DocumentVariable
    Dim sprs As String
    Dim i As Long
    Dim uploadType As String
    Dim savePath As String

    savePath = ThisDocument.DocumentVariables("saveToPath").Formula

    If (UCase(ThisDocument.DocumentVariables("saveTo_XLS_PDF_N").Formula) = "XLS") Then
        uploadType = "XLS"
    End If
    If (UCase(ThisDocument.DocumentVariables("saveTo_XLS_PDF_N").Formula) = "PDF") Then
        uploadType = "PDF"
    End If
    If (UCase(ThisDocument.DocumentVariables("saveTo_XLS_PDF_N").Formula) <> "XLS" And UCase(ThisDocument.DocumentVariables("saveTo_XLS_PDF_N").Formula) <> "PDF") Then
        uploadType = "N"
    End If
    
    If (uploadType = "XLS" Or uploadType = "PDF") Then
        If (Dir(savePath, vbDirectory) = "") Then
            MkDir savePath
        End If
    End If

    If (uploadType = "XLS" Or uploadType = "PDF") Then
        Set Var = ThisDocument.DocumentVariables("Portfe&amp;#316;a nosaukums")
        If UBound(Var.Values(boUniqueValues)) > 0 Then
            For i = 1 To UBound(Var.Values(boUniqueValues))
                sprs = Var.Values(boUniqueValues)(i)
                Call ThisDocument.Reports(1).AddComplexFilter("Portfe&amp;#316;a nosaukums", "= <Portfe&amp;#316;a nosaukums> = """ &amp; sprs &amp; """")
                ThisDocument.Reports(1).ForceCompute

                If (uploadType = "XLS") Then
                    ThisDocument.SaveAs (savePath &amp; "\" &amp; sprs &amp; ".xls") 'Mid(sprs, 6, 8)
                End If
                If (uploadType = "PDF") Then
                    ThisDocument.ExportAsPDF (savePath &amp; "\" &amp; sprs &amp; ".pdf") 'Mid(sprs, 6, 8)
                End If
                
                Call ThisDocument.Reports(1).AddComplexFilter("Portfe&amp;#316;a nosaukums", "= <Portfe&amp;#316;a nosaukums> = <Portfe&amp;#316;a nosaukums>")
                ThisDocument.Reports(1).ForceCompute
            Next i
        End If
    End If
End Sub

WorldIsMine :latvia: (BOB member since 2009-01-19)