ListFillRange in ActiveX combo not updating

Aspose.Cells Product Family
1

Hi,

I have an existing workbook with an ActiveX combo. We are in the process of replacing our excel code with aspose and I have come across several issues relating to the behaviour of the ListFillRange of the ActiveX combo.

Firstly, the worksheet is initially set up with a list fill range of “C$4:C$10”. When I copy the sheet via Aspose to either a new workbook (thanks for the recent fix that makes this now work) or within the workbook the range is set to “C4:C10”. The $ symbols are lost.

Secondly, as I manipulate the sheet by inserting rows and deleting columns the ListFillRange remains unchanged. I thought it might have been because I was not using methods that included updateReference but it seemed to make no difference when I changed .InsertRow(8) to .InsertRows(8,1,True) etc…

Below is the code I used to replicate:

    Dim loFromWB As Aspose.Cells.Workbook
    loFromStr = New System.IO.FileStream("C:\Temp\FromWB.xlsx", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.Read)
    loFromWB = New Aspose.Cells.Workbook(loFromStr)
    loFromStr.Close()
    loFromWB.Worksheets.AddCopy(0)
    loFromWB.Worksheets(1).Name = "Copy"
    loFromWB.Worksheets(0).Cells.InsertRows(8, 1, True)
    loFromWB.Worksheets(0).Cells(8, 0).Value = "8"
    loFromWB.Worksheets(0).Cells(8, 1).Value = "Eight"
    loFromWB.Worksheets(0).Cells(8, 2).Value = "Eight - 8"
    loFromWB.Worksheets(0).Cells.DeleteColumn(0, True)
    loFromWB.Save(fileName:="C:\Temp\ToWB.xlsx", saveFormat:=Aspose.Cells.FileFormatType.Xlsx)
    loFromWB = Nothing

In the output the sheet “Copy” demonstrates how the $ has been removed from the ListFillRange. The “Report” sheet should now be pointing to B$4:B$11 but instead points to C4:C10 (has also lost the $ without a copy) which is the original values despite the row and column manipulations.

SampleFiles.zip (23.1 KB)

Any assistance you can offer would be greatly appreciated.

Thanks, Julie

2

@t1jsw

Thanks for using Aspose APIs.

We were able to observe this issue as per your description and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-46003 - ListFillRange in ActiveX ComboBox not updating
3

@t1jsw,

This is to inform you that we have fixed your issue “CELLSNET-46003” now. We will soon provide the fix after performing QA and including other enhancements and fixes.

4

@t1jsw,

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

5

Thanks for the amazingly quick response.

I can confirm that when the sheet is copied the range stays as “C$4:C$10”. Also when a column is deleted the range changes to B as expected. However, the insertion of rows does not seem to be updating the range.

The initial combo contains 7 items. The six rows plus one blank row at the end. I have changed the code below to insert two new rows in the middle. An 8 and a 9 row. The combo still only has 7 items after this. The last item and the blank row are no longer included even though they should be. The final range should be “B$4:B$12”.

    Dim loFromStr As System.IO.Stream
    Dim loFromWB As Aspose.Cells.Workbook
    loFromStr = New System.IO.FileStream("C:\Temp\FromWB.xlsx", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.Read)
    loFromWB = New Aspose.Cells.Workbook(loFromStr)
    loFromStr.Close()
    loFromWB.Worksheets(0).Cells.InsertRows(8, 1, True)
    loFromWB.Worksheets(0).Cells(8, 0).Value = "8"
    loFromWB.Worksheets(0).Cells(8, 1).Value = "Eight"
    loFromWB.Worksheets(0).Cells(8, 2).Value = "Eight - 8"
    loFromWB.Worksheets(0).Cells.InsertRows(9, 1, True)
    loFromWB.Worksheets(0).Cells(9, 0).Value = "9"
    loFromWB.Worksheets(0).Cells(9, 1).Value = "Nine"
    loFromWB.Worksheets(0).Cells(9, 2).Value = "Nine - 9"
    loFromWB.Worksheets(0).Cells.DeleteColumn(0, True)
    loFromWB.Save(fileName:="C:\Temp\ToWB.xlsx", saveFormat:=Aspose.Cells.FileFormatType.Xlsx)
    loFromWB = Nothing

Also, attached are the original and output files from the code above.

RowInsert.zip (20.4 KB)

Any assistance would be appreciated.

Thanks, Julie

6

@t1jsw

Thanks for using Aspose APIs.

We were able to observe this issue as per your description and logged it in our database against this issue. Once, it is fixed or we have some other news for you, we will share it with you asap.

7

@t1jsw,

We have fixed your issue completely (i.e., we now care about inserting/deleting rows and update range(s) accordingly). Hopefully we can provide you the fix in the next 2-3 days or so.

8

@t1jsw

Please download and try the following fix and let us know your feedback.

9

Thanks Shakeel.

Any chance of getting the .NET 2.0 that is not AuthenticodeSigned?

10

@t1jsw

Can you please use .NET 4.0 fix instead?

i.e.

11

@shakeel.faiz

Ideally I want to test the change against a complicated workbook and set of operations (I just replicated using a simple scenario) and to do that I need to update the version that we reference in code. Unfortunately it has to be .NET 2.0 for me to be able to do that.

Cheers, Julie

12

@t1jsw

Thanks for using Aspose APIs.

We will check if we could provide .NET 2.0 version that is not AuthenticodeSigned. Once, we will have some news for you, we will share it with you asap.

13

@t1jsw

Thanks for using Aspose APIs.

Please download and try the following fix. It is a Normal Version and not AuthenticodeSigned.

14

Thank you Shakeel.

I can confirm that this version fixes the reported issue and also works for much more complicated scenarios.

Thanks again for all your help with resolving this issue.

Cheers, Julie

15

@t1jsw

Thanks for your feedback and using Aspose APIs.

It is good to know that your issue is resolved now with the provided fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

16

The issues you have found earlier (filed as CELLSNET-46003) have been fixed in Aspose.Cells for .NET 18.4. Please also check the document/article for your reference: Install Aspose Cells through NuGet|Documentation