topic Re: Macro Advice in QlikView

Macro Advice

Wed, 06 May 2015 18:24:29 GMT

Hi guys,

I am writing a macro for the first time and having trouble with it, Please advice

Macro
Sub Test set mytext = ActiveDocument.GetSheetObject("A").GetCell(0,0) ActiveDocument.Variables("vTest").SetContent mytext.text, true set myvar = ActiveDocument.GetSheetObject("B").GetCell(vTest,0) ActiveDocument.Variables("Test").SetContent mytext2.text, true End Sub

Macro

Sub Test

set mytext = ActiveDocument.GetSheetObject("A").GetCell(0,0)

ActiveDocument.Variables("vTest").SetContent mytext.text, true

set myvar = ActiveDocument.GetSheetObject("B").GetCell(vTest,0)

ActiveDocument.Variables("Test").SetContent mytext2.text, true

End Sub

I am trying to pull a value from sheet object A , store it in a variable vTest and then pass it as a parameter for pulling a value from Sheet object B. The macro above is what i have so far. Please advise,

Can i use a set x = vTest statement of some sort and create a temp variable and use that to get the value from Object B.

Also,

I know what i am trying to maybe easier with a peek function, but i want to be able to keep it dynamic as i have a huge access database linked to my application. Please advice,

Thank you for your time and effort,

Best,

Nik

Re: Macro Advice

Anonymous — Wed, 06 May 2015 18:54:50 GMT

A few minor fixes:

Sub Test

set mytext = ActiveDocument.GetSheetObject("A").GetCell(1,0)
ActiveDocument.Variables("vTest").SetContent mytext.text, true

vt=ActiveDocument.Variables("vTest").GetContent.string
set mytext2 = ActiveDocument.GetSheetObject("B").GetCell(vt,0)
ActiveDocument.Variables("Test").SetContent mytext2.text, true

End Sub

Re: Macro Advice

Wed, 06 May 2015 19:21:45 GMT

Thank you Michael for the response.

Best,

Nik

Re: Macro Advice

Wed, 06 May 2015 19:49:43 GMT

I tried the macro. It works but only upto a specific rowNo after which it gives me an error. Is there a limit on the number the variable can take? (for example: 347 returns an error), My column certainly does have more than 347 rows. Please advice,

Thanks,

Nik

Re: Macro Advice

Anonymous — Wed, 06 May 2015 19:55:33 GMT

Variable can take anything - number or text. Wondering if GetCell() has any limits. Which would be rather strange

Re: Macro Advice

Wed, 06 May 2015 19:57:55 GMT

That is the exact same reaction i had, get cell is giving me an exception. Do you think its a better idea to use the peek function? Can it be written to dynamically give different values?

Re: Macro Advice

Anonymous — Wed, 06 May 2015 20:02:46 GMT

I think it is not about limit. More likely the number you're getting from the object A points you to the row in the object B which doesn't exist.

Re: Macro Advice

Wed, 06 May 2015 20:05:57 GMT

I will double check the total number of rows for object B. Thanks again.

Re: Macro Advice

Anonymous — Wed, 06 May 2015 20:09:30 GMT

No Problem.

BTW, I just checked in my sample - it gives error with row 26 in A because it points to row 19 in B, but B has only 18 rows.

On the other hand, it works fine if I read row 1700 from A - it points to row 18 (or 17) in B.

So, it's not the limit.

Re: Macro Advice

Wed, 06 May 2015 20:18:07 GMT

I understand now, What if i use something like

if(GetSelectedCount(A)>0 , concat(Row No, ','). will it give me the RowNo from B or just A?

Re: Macro Advice

Anonymous — Wed, 06 May 2015 20:27:04 GMT

No, it's not right. I'm thinking that you can add a line

on error resume next

In this case there will be no error message. But also there will be no a desired result, as if the macro didn't run.

A better way to handle it is to produce a message for user. But I'm not fluent enough in VBScript to tell how exactly to implement this. Maybe it makes sense for you to start a new discussion with this exact question.

Re: Macro Advice

Wed, 06 May 2015 20:30:13 GMT

Thanks, I Will have to think of a work around. Appreciate your time.

Best,

Nik