Worksheets.AddCopy mess up formula


#1

Hello,

I get some problems when using worksheets.addcopy. Sometimes formula's complete get messed up. I attached my input and output file.

As you see on sheet 'demo' the formula in Cell C23 ( and others ) gets corrupted. And the formula from A7 get removed. In my project also the formula's in cells A5 and A6 ( and A7) completely get corrupted but I can't reproduce it in my testproject. In my project sheets get copied,moved and /or deleted. Maybe that makes the problem worse, but it has no effect in the testproject.

Code : ( VB.Net on Asp.net 2.0 )

Dim sFile As String
Dim cXl As Aspose.Cells.Excel
Dim iIndexNew As Integer
Dim iSIndexOrig As Integer

cXl = New Aspose.Cells.Excel
sFile = Me.Server.MapPath("Test3.xlt")
cXl.Open(sFile, FileFormatType.Excel2000)
sFile = Me.Server.MapPath("TestOut.xls")
iSIndexOrig = cXl.Worksheets("No Segments").Index
iIndexNew = cXl.Worksheets.AddCopy(iSIndexOrig)
cXl.Worksheets(iIndexNew).Name = "Demo"
cXl.Save(sFile, FileFormatType.Excel2000)

In the original sheet there is no problem, only in the copy.

I'm using version 3.7.2.0

Luc


#2

Please try this attached fix.


#3

The strings are ok now, but the formula's in A7 - F8 still disappear. And I still got similar errors in my real project ;

(DK42*0.01)/4 changes to (DK42*'0,01'())/4

(DK42*(-0.02))/4 (DK42*(-'0,02'()))/4

which in turn gives errors.

Maybe to mention ; I'am here in Belgium, with local settings Dutch (Belgium), but my decimal symbol setting is overriden with '.' in stead of ','. ( Because I see that 0.02 changes to 0,02 )

But there is only a problem in the copy. The original sheet is ok.

Luc


#4

Hi Luc,

This problem is caused by the local setting. Please try this attached fix.

And please change formula "=(A1*-0,02)/4" to "=(A1*(-0,02))/4". Thanks.


#5

Yes, everything works fine now !

By the way ; I tried changing "=(A1*-0,02)/4" to "=(A1*(-0,02))/4" but it had no effect without the fix.

Thanks for the very fast and great support ! ( as usual )

Luc


#6

Still a problem ( only in the copy ) ; formula's " = IF(C27<>0; C26/C27;"") "or " =IF(D5<>"";D6+D5;"") " just disappear.

Luc


#7

Hello,

no solution yet for the problem mentioned above ? Just to make sure you didn't miss my message ( because I'm used to getting very fast response from you ). And in fact I'm waiting for it.

Luc


#8

Hi Luc,

Please try this attached fix.


#9

Everything seems ok now, as far as I see. But I got an other very strange problem with formula's, even without addcopy. I make a new topic ....

Luc


#10

Hello Laurence,

I am affraid I've found another problem concercing formula's and addcopy. And this is in fact a big problem, because it means we can't trust any of our formula's anymore in the copy.

I attached the template, and the problem is with the formula in cells B7 and C7 : it seems that the formula ' =(B1)/100/4 ' is faulty calculated as '=(B1/100)*4 ' ( I know it's not the best way to write B1/100/4 but this was an existing template and I can't check every cell on formula's when upgrading the aplication ). The big problem is that everything seems allright , no message, no invalid formula or so, but the calculation is wrong. When you check the formula in excel, everything looks perfect. The cell value changes as other cells change ( in this case B1 ), but the result is wrong. Only when you edit the formula in excel again, ( without even changing a symbol) the calculation becomes correct. And the problem is only in the copy. The original sheet works without any problem.

For my test I only open the template, add a copy of the sheet, and save again.

I'm still using version 3.7.2.3, VB.Net, Asp.net 2.0

Luc


#11

This problem is not caused by AddCopy method but formula settings. I fixed this bug in this attached version. Please try it.


#12

First test looks ok.

But when testing with a combobox on the sheet ( because of my combobox problem), ms excel crashes when opening the file. I guess combobox and addcopy doesn't work together ... But not an issue for me now, at the moment we don't use addcopy and controls at the same time.

Luc

( I attached the file, with combobox once again )


#13

I will check this AddCopy issue with ComboBox.