PAX API: Multiple commit of all quick reports (or other ways?)

[Elessar]

Hello everybody!

The Commit button commits data only in active sheet, so I'm asked to make a button to commit the entire book.

Firstly, maybe there is an option to make the Commit button commit the entire book (like the "Refresh" one)?

Secondly, I've made a VBA script to do this. The idea is to loop through all "tm1\\_X_R" named ranges to point the rows of each report book and to commit it

Code: Select all

Option Explicit
Option Compare Text

Sub CommitAll()
Dim nm As Name
Dim sFilters As String
Dim sRange As String
sFilters = "_R"
    
    For Each nm In ActiveWorkbook.Names
'Locate "tm1\\_X_R" named range 
        If InStr(nm.Name, sFilters) > 1 Then
            sRange = nm.Name
'Print where we are
            Debug.Print (sRange)
            Reporting.GetCurrentReport(Range(sRange)).Commit True
            Reporting.Wait
        End If
    Next nm
End Sub

This VBA throws error: "Run-time error '91': Object variable or With block varible not set". The most strange thing is that the error is thrown on different quick reports each time I relaunch excel.

Appreciate any help on this.
Or, if somebody has working VBA for this - please share.

[gtonkin]

Hi Alexander, Still very much a hack at this but you could try something like:

Code: Select all

Sub CommitQuickReports()

Dim oReport As Variant
Dim iReportCount As Integer
Dim iReportIndex As Integer

'Get number of reports in the book
iReportCount = Reporting.getreports().Count

For iReportIndex = 0 To iReportCount - 1

    Set oReport = Reporting.GetReports().Item(iReportIndex)
    Debug.Print oReport.Name
    oReport.Commit True
    Reporting.Wait
Next

End Sub

HTH

[Adam]

We've been using the below in production for quite a while. Note the lack of .Wait

Code: Select all

Sub CommitAllQuickReports()

    Dim currentNamedRange As Name
    Dim qrIDNumberWIP As String
    Dim qrIDNumber As Long
	
    For Each currentNamedRange In ActiveWorkbook.Names
		
		If currentNamedRange.Name Like "tm1\\_*_C" Then
						
			qrIDNumberWIP = Mid(currentNamedRange.Name, 7)
			qrIDNumberWIP = Mid(qrIDNumberWIP, 1, InStr(1, qrIDNumberWIP, "_") - 1)
			qrIDNumber = CLng(qrIDNumberWIP)
			Reporting.QuickReports.Get(qrIDNumber).Commit
			
		End If
		
	Next
	
End Sub

[Wim Gielis]

Hi Adam, Alexander,

Just a small remark. Names have a Comment field, that is exposed through VBA. Maybe useful to use that one rather than the string manipulations. Not for speed but rather ease of use/maintainability/flexibility.

In general, this Comment field and also controls that expose a .Tag property, are useful candidates to store this kind of information.

Code: Select all

? Names("test_5_range").Comment
5

08.png (10.05 KiB) Viewed 4332 times

09.png (5.88 KiB) Viewed 4332 times

[Elessar]

Thanks George, Adam, Wim,

Geroge's variant looks the most elegant, and it works. And as Adam said, "Wait" is not needed. My final code is:

Code: Select all

Sub CommitAll()

Dim oReport As Variant
Dim iReportCount As Integer
Dim iReportIndex As Integer

'Get number of reports in the book
iReportCount = Reporting.GetReports().Count

For iReportIndex = 0 To iReportCount - 1
    Set oReport = Reporting.GetReports().Item(iReportIndex)
    oReport.Commit True
Next

End Sub