News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Custom report using multiple user-defined cells

Started by abssorb, August 05, 2011, 12:32:05 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

abssorb

I have a milestone chart, where each milestone has a number of user-defined cells relating to its status.

What I'd like to be able to do is have a report which does this:

Count every shape which is a milestone (user.milestone=1),
Of these, sub-total which are:

  • Status red (user.status=red)
  • Status green (user.status=green)
  • Approved (user.approval=1)
  • Not Approved (user.approval<>1)

I can make simple reports, totaling any one of these, and then assemble it all in excel, but that's a pain.  I'd like to learn how to build this into a single visio report is possible.

Any help appreciated. :)

Jumpy

Do it yourself in VBA?


Dim total as Integer, stRed as Integer, stGreen as Integer, Appr as Integer, NotAppr as Integer
Dim shp as Visio.Shape

For Each shp in ActivePage.Shapes
  If shp.CellExists("User.milestone",false) Then
    If shp.Cell("User.milestone").Result("") = 1 Then
      total = total +1
      If shp.Cell("User.Status").ResultStr("") = "red" Then stRed = stRed + 1
      If shp.Cell("User.Status").ResultStr("") = "green" Then stGreen = stGreen + 1
      If shp.Cell("User.approval").Result("") = 1 Then
        Appr = Appr + 1
      Else
        NotAppr = NotAppr + 1
      End If
    End If
  End If
Next

'Now push that all to Excel
'Don't know the exact Code from here on

  Dim e as Excel.Application
  Set e = CreateObject("Excel.Application")

  Dim w as Workbook
  Set w = e.Workbooks.Add 'or e.Workbooks.Open("MyWorkbook.xls")

  Dim ws as Worksheet
  Set ws = w.Worksheets(1)
 
  ws.Cells(2,2).Value = Total
  ws.Cells(3,2).Value = stRed
  ws.Cells(4,2).Value = stGreen
  ws.Cells(5,2).Value = Appr
  ws.Cells(6,2).Value = NotAppr

  'Save, Quit, Cleanup
  w.Save 'w.SaveAs(SomeArguments)
  Set ws = Nothing
  w.Close
  Set w = Nothing
  e.quit
  Set e = Nothing


As an idea for starters...

abssorb

Many thanks Jumpy.  I will investigate :)

The client has macro restrictions on their network though.

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 181 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1070KB.
Tokens: post-login.
Cache hits: 13: 0.00174s for 26,725 bytes (show)
Cache misses: 3: (show)
Queries used: 16.

[Show Queries]