topic Export a formula to excel - is it possible? in QlikView

Export a formula to excel - is it possible?

Thu, 22 Nov 2012 10:55:17 GMT

Hi,

Is it possible to export (via VBS) a formula to excel? We export now row by row (A, B, C) the cell values via a macro, and in a certain row (e.g. D) we want to export a formula (e.g. =Ci/Bi*F1), so that it is preprogrammed for the end user who can type a different value in the cell F1 and see the changed result in column D.

What is the VBS function for formula export if it is possible?

Thanks,

Re: Export a formula to excel - is it possible?

Gysbert_Wassenaar — Thu, 22 Nov 2012 11:47:22 GMT

That should be possible.

set xl = createobject("excel.application")

set wb = xl.workbooks.add

set ws = wb.worksheets(1)

xl.visible = true

ws.range("D1:D100").formula = "C1/B1*F$1"

this should result in D2 having the formula C2/B2*F$1 and not C1/B1*F$1

Re: Export a formula to excel - is it possible?

Thu, 22 Nov 2012 12:22:52 GMT

Thank you, I'll test it.

Re: Export a formula to excel - is it possible?

Clever_Anjos — Thu, 22 Nov 2012 12:26:58 GMT

You can create a column at your table that contains your formula

='=C'&rowno()&'B'&rowno()&'*F$1'

When this table is copied to excel, the formula will be translated.

Important: When you export, this trick does not work, only when you copy/paste