topic Re: Quotes in Excel Formula in QlikView

Quotes in Excel Formula

gerhardl — Tue, 18 Sep 2012 09:16:01 GMT

Hi there,

How can I include double quotes WITHIN an excel formula I use in a macro?

XLSheet1.Range("B1").Formula = "=INDIRECT("A" & INT(RAND()*COUNTA(A:A))+1)"

Here is some more detail:

Using a macro, I need to add this formula to Excel:

=INDIRECT("A" & INT(RAND()*COUNTA(A:A))+1)

So my macro has: XLSheet1.Range("B1").Formula = "=INDIRECT("A" & INT(RAND()*COUNTA(A:A))+1)"

But because of the double quotes around "A", it gives me Expected end of statement

That A is supposed to refer to column A - if I use that exact formula in excel it works perfectly, but I cannot use it in the macro.

If I change it to A:A then the macro runs, but the formula does not work.

Any ideas?

Re: Quotes in Excel Formula

Anonymous — Tue, 18 Sep 2012 09:17:55 GMT

Does it work if you exchange either the outer or inner double quotes with single quotes?

Re: Quotes in Excel Formula

gerhardl — Tue, 18 Sep 2012 09:24:51 GMT

No, replacing the inner quotes gives me an Unknown runtime error

Replacing the outer quotes will change the formula to a comment.

Re: Quotes in Excel Formula

Anonymous — Tue, 18 Sep 2012 09:31:06 GMT

How about replacing one set of quotes with: chr(34)

This is the double quote ascii code.

I do recall there being some way to escape vbscript characters.. either using two double quotes or three..

like: "" instead of ", or possibly """

Re: Quotes in Excel Formula

gerhardl — Tue, 18 Sep 2012 09:37:58 GMT

Ha - nice:

This worked:

XLSheet1.Range("B1").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

While I have your attention...

I am exporting account numbers to column A - they can be 19 or 20 characters long and they look like this:

10201128030000086054

But when I copy and paste them using my macro, they end up as 10201128030000000000.

Do you know how to fix that?

This doesn't work:

ActiveDocument.GetSheetObject("LB31").CopyTableToClipboard true

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

XLSheet1.Paste XLSheet1.Range("A1")

XLSheet1.Range("B1").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

Thank you.

Re: Quotes in Excel Formula

Anonymous — Tue, 18 Sep 2012 09:47:02 GMT

Are you forcing the value to be loaded as text in QlikView?

In that case it sounds like Excel is transforming it to a numerical value and it doesn't handle above 15 digits if I remember correctly.

Try using the Text() function when loading the value into QV and see if it works.

Re: Quotes in Excel Formula

gerhardl — Tue, 18 Sep 2012 10:13:13 GMT

No unfortunately that gives me the same result.