page number on Multiple Tabs

system · August 7, 2006, 6:13pm

I am trying to use this macro. have few doubts.

when i create a varible named same as the tab, what should be the formula i should be using?

I used

=Page()

After running the macro, i still get page numbered 1 across all the tabs.

i think since i am using page(), i am getting 1 across all tabs. What function/formula i should be using to get the running count of the tabs.

KhoushikTTT (BOB member since 2005-02-24)

system · August 7, 2006, 6:55pm

NEO,
Are you sure, you are calling the macro in after_refresh event.
The code will assign the value to the variables after refresh, for those variables which you said created same as tab names.

I would suggest assign zero(0) OR “”(double quotes) to the variables depending on the return type and try.

BO_Chief (BOB member since 2004-06-06)

system · August 7, 2006, 7:09pm

No, Could you guide me how to do this.
I dont know how to define the after_refresh event

Now i have just the above code in the editor.

KhoushikTTT (BOB member since 2005-02-24)

system · August 7, 2006, 7:59pm

@NEO:
Go to Tools–>Macro --> Click Visual Basic Editor --> In the project pane (on left) -->
double click ‘ThisDocument’.

Now in the right editor will appear, on the top you will see General/Document, Select
Document, just on the right select the event AfterRefresh.

A two line VBA code is generated for you automatically.
Paste the code which is in this thread BELOW the ‘End Sub’.

And Now you have to call this procedure.

Type PageNumberTabs in between the code which is generated for you automatically.

Hope that helps.

BO_Chief (BOB member since 2004-06-06)

system · August 7, 2006, 8:17pm

Searching for a word but, didnt get one. So, let my silence convey my feeling.

I will work on a sample data and post the report later this week.

KhoushikTTT (BOB member since 2005-02-24)

system · September 6, 2006, 6:00pm

Here is a sample report.

I created four tabs, as 1998, 1999, 2000 and All. Created four varibles named same as the Tabs

1998=Page()
1999=Page()
2000=Page()
   All=Page()

VB editor (Alt F11), I wrote this code

Private Sub Document_AfterRefresh()
Call PageNumberTabs
End Sub

Sub PageNumberTabs()

    PageNum = 0
    
    For i = 1 To ActiveDocument.Reports.Count
                
        If i <> 1 Then
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() + " &amp; PageNum &amp; ", ""#"")"
        Else
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() , ""#"")"
        End If
        
        ActiveReport.ForceCompute
        
        PageNum = PageNum + ActiveDocument.Reports(i).NumberOfPages
        
    Next i
End Sub

Now in the footer, I added these variables to the corresponding tabs.
Page Number Across Tabs.zip (20.0 KB)

KhoushikTTT (BOB member since 2005-02-24)

system · April 11, 2007, 9:25pm

This solution worked wonderfully. Thanks!
Can anyone suggest how to get the Page 1 of Page N in the same situation. Right now, its generating Page 1 to N , but i dont know how to get the total number of pages count and show it in all the reports.

san_gee (BOB member since 2006-11-01)

system · April 11, 2007, 9:40pm

=<Current tab object>&amp;" of "&amp;Right(<Last tab object)> ,2)

For Example

=<1998>&amp;" of "&amp;Right(<All)> ,2)

KhoushikTTT (BOB member since 2005-02-24)

system · April 11, 2007, 9:43pm

I did try that. But my last report has multiple sheets. eg. my last report has pages 18, 19 and 20
And this one gives me 18 as the total pages and not 20

san_gee (BOB member since 2006-11-01)

system · July 3, 2007, 8:01pm

You probably don’t need this answer anymore, but for anyone else with the same question, you can add a line of code to the macro and it will insert the total number of pages into another variable. I called mine LastPage. Then you will just need to add & "of " & to your formulas and all will be fine.

Sub PageNumberTabs()

…(same code as posted earlier)…

    PageNum = PageNum + ActiveDocument.Reports(i).NumberOfPages
   
Next i
'The following line is the one I added
DocumentVariables.Item("LastPage").Formula = PageNum

End Sub
[/b]

nrf-bob (BOB member since 2007-07-03)

system · November 8, 2007, 5:37pm

My VB is very very rusty…

I’ve added your amendment to get the “page x of y” but it doesn’t seem to give me the right numbers.
I have a 3 tab, 13 page report
Tab 1 -Notes - 1 page
Tab 2 -Directorate - 2 pages
Tab 3 -Buyer - 9 pages

The pages sequentially number perfectly (thanks) although the final part giving the total number of pages is having problems.
i.e the second tab numbers, Page 2 of 4, Page 5 of 5… the thrid tab also has problems.

I have the VB as you have above, I created variables just like you did in your sample report and I have done the page numbers as you suggest.

Hope someone can help - many thanks in advance

malc001 (BOB member since 2005-09-26)

system · November 8, 2007, 6:13pm

Well, maybe your math is a little rusty as well.

1+2+9=12, not 13.

MichaelWelter (BOB member since 2002-08-08)

system · November 9, 2007, 8:41am

Thanks for your constructive reply.

If you can’t help out then why do you bother posting? Or are you just trying to improve your post count?

If any one can offer some serious help I would be greatly appreciate it.

malc001 (BOB member since 2005-09-26)

system · November 9, 2007, 3:44pm

You said you were expecting a result of 13, but didn’t say what result you were getting. I was pointing out that you might be expecting the wrong result. My post was meant to be helpful. I appologize if you didn’t take it that way.

MichaelWelter (BOB member since 2002-08-08)

system · November 9, 2007, 3:50pm

Ok, sorry for my snappyness…

On the third tab I get Page 5 of 5, Page 6 of 6 etc… what I would expect is Page 5 of 12, Page 6 of 12…

many thanks

malc001 (BOB member since 2005-09-26)

system · June 8, 2009, 9:16pm

Hi:

I am trying to have the sequencial page number for the pdf files that comes from multiple webI reports in a PDF format using publication.
What are the options I have in WEBI.
I went through this post, I believe this solution is for DESKI.

if we have any possible solution for WebI, please let me know.

Thank You
Ramesh Vasudevan

rameshbalajiv (BOB member since 2008-03-25)

system · March 29, 2010, 7:09pm

Is there any way we can implement this in WebI? I am on WebI XI3.1 SP2. Any help would be appreciated. Thanks.

kc4906 (BOB member since 2009-03-28)

system · March 30, 2010, 8:26am

I believe this is limited to Deski reports only.

How about using a different logic. For ex:

For first data provider, use: “1.” + Page()
For second data provider, use: “2.” + Page()

So, you will have Page Numbers like 1.1, 1.2, 2.1, 2.2, 2.3 etc. Of course, this only makes sense when each data provider gives you a different sub report (of the main report).

Rajat

Rajat Sapru (BOB member since 2008-08-28)

system · April 6, 2010, 11:45am

Hi kc4906

I never managed to move this forward.

Fingers crossed for something in a later version but BO once again seem focused on expanding the product base (and renaming existing products) rather than strengthening what they have.

malc001 (BOB member since 2005-09-26)

system · April 14, 2010, 7:28am

Thanks a lot. i was searching for VBA code for my reports. This worked perfect…!!

Jessym (BOB member since 2009-11-02)