topic Strange problem with export in excel.. in QlikView

Strange problem with export in excel..

fabio — Mon, 29 Mar 2010 08:11:41 GMT

i export in excel a pivot table with this code:

vSheetObj.CopyTableToClipboard true

set XLSheet = XLDoc.Worksheets(iNr)

XLSheet.Paste XLSheet.Range("A"&iRigaDettaglio)

it works, but if a field contains a data like "++something" (look at the "+" char), the resulting excel is broken.

all the numeric cell formatted in "currency" are tagged with the error: "the cell contains text value" (i.e. the currency "€" character).

if i exclude with a selection all the "++something" values it works perfectly!

please, help me! i offer a beer [:)]

bye

Strange problem with export in excel..

Mon, 29 Mar 2010 10:03:56 GMT

Its because Excel is seeing '++something' as an expression. You either need to remove them (purge), replace them with another character (replace) or wrap them with a single quotation (chr(39)).

Hope that helps. Can I claim my beer?

Regards,

Gordon

Strange problem with export in excel..

fabio — Mon, 29 Mar 2010 10:25:46 GMT

>remove them (purge)

the field is a article-code, i need it

>replace them with another character (replace)

see above, the field is a article-code. I use this field in excel for searching &co

>wrap them with a single quotation (chr(39))

i try this but incredibly don't work! in the excel cell i found now '++something (with the single quote) but the problem is the same!

magic: if i manually select the excel cell, i press F2 to edit it and i confirm with enter... it works!

thank a lot for your answer. do you like guinness?

Strange problem with export in excel..

justinasp — Mon, 29 Mar 2010 11:16:54 GMT

Hi Fabio,

you coul try to write your expression inside the function =TEXT(). This way Excel will interpret the result as a text.

Good luck,

Justinas

Strange problem with export in excel..

fabio — Mon, 29 Mar 2010 11:29:42 GMT

I tried but not working [:'(]

thank you

note: with excel 2007 it's ok! maybe it's a excel 2003 bug?