Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gerhardl
Not applicable

Quotes in Excel Formula

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?

Tags (1)
1 Solution

Accepted Solutions
jsn
Not applicable

Re: Quotes in Excel Formula

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

6 Replies
jsn
Not applicable

Re: Quotes in Excel Formula

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

gerhardl
Not applicable

Re: Quotes in Excel Formula

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

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

jsn
Not applicable

Re: Quotes in Excel Formula

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

gerhardl
Not applicable

Re: Quotes in Excel Formula

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.

jsn
Not applicable

Re: Quotes in Excel Formula

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.

gerhardl
Not applicable

Re: Quotes in Excel Formula

No unfortunately that gives me the same result.