topic Re: need help to update macro code in QlikView

need help to update macro code

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: need help to update macro code

marcus_sommer — Thu, 21 Jul 2016 18:27:10 GMT

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)

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

- Marcus

Re: need help to update macro code

Thu, 21 Jul 2016 18:38:52 GMT

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)

Re: need help to update macro code

marcus_sommer — Fri, 22 Jul 2016 05:12:23 GMT

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

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

Re: need help to update macro code

Fri, 22 Jul 2016 12:45:25 GMT

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

Re: need help to update macro code

marcus_sommer — Fri, 22 Jul 2016 14:57:40 GMT

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

Re: need help to update macro code

Fri, 22 Jul 2016 15:21:57 GMT

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??

Re: need help to update macro code

Fri, 22 Jul 2016 16:23:14 GMT

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.

Re: need help to update macro code

marcus_sommer — Fri, 22 Jul 2016 17:48:11 GMT

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

Re: need help to update macro code

marcus_sommer — Fri, 22 Jul 2016 17:54:34 GMT

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