need help to update macro code

[#1]

hello guys,

i have macro code to copy value (one specific value from a column and row i.e. cell) from chart into variable as -

Sub Get_contrib

Application.WaitForIdle

set cell=ActiveDocument.GetSheetObject("CH05").GetCell(1,3)

ActiveDocument.Variables("vValue").SetContent cell.text, True

set cell=nothing

End Sub

BUT my problem is i want to select full row with column. Can someone help.

thanks

[marcus_sommer]

You couldn't copy simply a column - you will need to read the chart cell for cell within a loop including the header and put them together like you need them, see here an example from APIGuide.qvw how you could access any cell within a table:

set table = ActiveDocument.GetSheetObject( "CH01" )

w = table.GetColumnCount

h = table.GetRowCount

set CellMatrix = table.GetCells2(0,0,w,h)

for RowIter=0 to h-1

    for ColIter=0 to w-1

        msgbox(CellMatrix(RowIter)(ColIter).Text)

    next

next

In your case you could fix the column and simply loop through the rows.

- Marcus

[#3]

thanks for ur reply marcus.

ok now i have the cels values , can i store dem in variable so i can use variable to calculate some formula like i want to store cloumn 1 in variable val1 and  cloumn 2 in variable val2 and den use it for applying some formula like xirr(val1,val2)

[marcus_sommer]

Yes, you could store these values in one or several variables, maybe:

set table = ActiveDocument.GetSheetObject( "CH01" )

w = table.GetColumnCount

h = table.GetRowCount

set CellMatrix = table.GetCells2(0,0,w,h)

for RowIter=0 to h-1

    'for ColIter=0 to w-1

        'msgbox(CellMatrix(RowIter)(ColIter).Text)

    'next

     vValue = vValue & "," & CellMatrix(RowIter)(2).Text

next

ActiveDocument.Variables("vValue").SetContent cell.text, True

and then you could for example count the number of values in this array-like string with substringcount() and access single values with subfield() - there are many ways how you could store and read these values - it will depend on your requirements which are the best/simplest way to get them.

- Marcus

[#5]

Thanks Marcus, its showing all the cells and now i want them to keep date cells in vValue1 and amount cells in vValue2 and then call these two variables in text box for xirr calc as xirr(vValue1,vValue2).

Can u see my attached file and help me out with this.

i appreciate ur help.

thanks

[marcus_sommer]

Here the macro is setting the variables to the values of the chart - whereby I have no idea from XIRR and how do you could use it within a textbox.

- Marcus

[#7]

thanks fro reply marcus,

the macro is making variables to values of chart means all values of column xirr_date is coming in variable vValue1? is this correct what i understood??

[#8]

marcus can i use the variable anywhere like in any sheet object? and does my variable is holding all chart values or specific colum with all rows ??

thanks.

[marcus_sommer]

In this case all available rows within the object will be stored into both variables - therefore it will be a matter of the selections which values are included.

- Marcus

[marcus_sommer]

After all I'm not sure if the transfer from the table-object to the macro and from there into gui-variables really useful - I think you could what you want to do better achieve if you do it directly within gui. You might need for this advanced aggregations with the aggr-function but this is better and easier then to handle array-string within variables.

- Marcus