A few General questions about Dashboards

[Michael Romano]

So I have taken my first stab at creating a metrics dashboard for my company. My workbook contains several spreadsheets that perform queries to an Oracle database. Those sheets drive several pivots tables which in turn generate a bunch of pivot charts.

Here are my questions...

1) Do pivot charts refresh when the table is refreshed?
2) Is there a way to automatically refresh the pivot tables and charts every 5 minutes?
3) Is it possible to have excel loop through the sheets containing the pivot charts based on a 30 second timer?

If someone can point me in the right direction to these answers I would appreciate it. my googlefu is broken......

 

[NARAYANK991]

Hi Michael ,

1. Yes.
2. Possible , but somewhat complicated if it involves a timer ; other ways of automatically refreshing pivot tables and charts are given here , along with a problem of chart formatting being lost :

http://www.pcreview.co.uk/forums/pivot-chart-automatic-refresh-t1787110.html
3. As mentioned earlier , anything involving a coded timer is somewhat complicated ; if the table / chart are based on external data , this is easy , as explained here :

http://office.microsoft.com/en-in/e...ottable-or-pivotchart-report-HP005201974.aspx

Narayan

 

[dan_l]

You can set up some really, really simple vba (I'm talking like total copy/paste stuff) to refresh pivots.

I've found refreshing pivot charts to be kind of difficult to manage long term. Better option: use pivot tables and set up regular charts using =getpivotdata().

 

[Michael Romano]

So I got the auto refresh of the pivot tables to happen, so thank you for that.

Both of those links reference updating pviot charts and tables. Question number 3 was to change sheets automatically.

I am wanting to put these charts up on a big screen. So when the work book is opned it will open on the first sheet, then 45 seconds later make sheet 2 active and so forth.

 

[NARAYANK991]

Hi Michael ,

I am not sure I have understood you ; when the workbook is opened , the first sheet will be displayed for 45 seconds , then the next sheet will be displayed , presumably for another 45 seconds , after which the 3rd sheet will be displayed and so on.

Do you only want the sheet to be displayed or will the user interact with Excel in any way , such as by scrolling through the active sheet , by entering / modifying data ,... ?

Narayan

 

[Michael Romano]

There will be no interaction with the data at all. I found this code

Sub StartSlideShow()
    Application.OnTime Now + TimeValue("00:00:45"), "ShowNextSheet"
End Sub

Sub ShowNextSheet()
    Dim lastIndex As Integer, nextShtIndex As Integer

    lastShtIndex = Worksheets.Count
    nextShtIndex = ActiveSheet.Index + 1

    If nextShtIndex <= lastShtIndex Then
        Worksheets(nextShtIndex).Select
        StartSlideShow
    Else
        MsgBox "End of slide show"
    End If
End Sub

The only problem with this one is it will loop through all. I just need it to loop through the chart sheets. There are 9 charts in total in my work book, but a total of 20 sheets containing the data that drives the charts.

 

[NARAYANK991]

Hi Michael ,

Try this :

Sub StartSlideShow()
    ShowNextSheet
    ThisWorkbook.Worksheets(1).Activate
    MsgBox "End of Slideshow"
End Sub
 
 
Sub ShowNextSheet()
    Static shtindex As Integer
    shtindex = shtindex + 1
    If shtindex <= Sheets.Count Then
      If (Sheets(shtindex).Type <> xlWorksheet) Then
          Sheets(shtindex).Select
          Application.Wait (Now + TimeValue("00:00:05"))
      End If
      ShowNextSheet
    End If
End Sub

I have made the time delay between charts only 5 seconds ; if you are satisfied with the way it works , increase the time delay to 45 seconds.

Narayan

 

[Michael Romano]

THis gets me 95% there. The last part of this is to no have it stop. Just continously keep going and going and going and going.....

 

[NARAYANK991]

Hi Michael ,

I don't know whether that is advisable ! Anyway try this :

Sub StartSlideShow()
Repeat:
    ShowNextSheet
    ThisWorkbook.Worksheets(1).Activate
    GoTo Repeat:
End Sub
 
 
Sub ShowNextSheet()
    Static shtindex As Integer
    shtindex = shtindex + 1
    If shtindex <= Sheets.Count Then
      If (Sheets(shtindex).Type <> xlWorksheet) Then
          Sheets(shtindex).Select
          Application.Wait (Now + TimeValue("00:00:05"))
      End If
      ShowNextSheet
    End If
    shtindex = 0
End Sub

Narayan

 

[Michael Romano]

I tried it and it works. Now I just gotta find a good way to stop it. But thank you very much. This does what I need it to do.

 

[Brij Arora]

Here are my questions...

2) Is there a way to automatically refresh the pivot tables and charts every 5 minutes?

You can do this by choosing Data Tab > Connections >Properties > Connection Properties >Usage

there is second option where you choose refresh every <n> minute

screen shot attached
Brij A