I currently have a table which controls the display of certain measures in a macro. Right now I am reading from a database via ADO to capture this table in my macro, but I was wondering if I could more easily read from the table line by line in a recordset directly from QV after loading the table in the script. This would be much more efficient and failsafe.
Here is my macro below:
Public Sub FetchRollups
selectedLevel = ActiveDocument.fields("Levels").GetSelectedValues.Item(0).Text
Dim conn
Dim rs
'Instantiate connection and recordset objects
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Open the connection and capture the P&L Rollup table
conn.ConnectionString = "DSN=CXAPPSVR03"
conn.Open
Set rs = conn.Execute("SELECT * FROM TEST.DBO.C_X_PLDEF WHERE LEVEL <= " & selectedLevel)
'Grab the relevant chart and associated properties
set chart = ActiveDocument.GetSheetObject("CH02")
prevcount = chart.GetProperties.Expressions.Count
'Add all expressions from recordset
Do While not rs.EOF
'Add expression and capture its ID
x = chart.AddExpression(rs("ExpressionValue").Value)
'Set expression properties such as label
Set prop = chart.GetProperties
prop.Expressions.Item(x).Item(0).Data.ExpressionVisual.Label.v = String(rs("Level")*3, " ") & rs("ExpressionText").Value
chart.SetProperties prop
'Continue Loop
rs.MoveNext
Loop
'Delete old pdimensions
for y=prevcount-1 to 0 Step -1
chart.RemoveExpression y
Next
'Cleanup chart
'chart.SetPixWidth 0,200
conn.Close
'chart.SetProperties p
End Sub
