We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Named Ranges

Hi,

Please see the attached excel file

I am using Syncfusion.XlsIO.Web and Syncfusion.XlsIO.Base dll's to access excel file update values via VB.net application and saveit

1) In Sheet1, cell A2 references list values of the named range in Sheet2 A column which is called "Cities"
The names range formula (for sheet1,A2) is
=Sheet2!$A$2:$A$6 (ctrl+F3 to go to name manager window)

When i update values of cities i.e when i add more values for cities in asp.net application
I need to update excel file also with the updated named range.Currently in my excel file there are 5 cities.
After update let's say there are 7 cities.
Sheet 1 should reflect the updated range
The named range formula (for sheet1,A2) should be updated to =Sheet2!$A$2:$A$8

2) Also how can i delete each cell value using Syncfusion. Do you have any class targeting Excel cell?

I appreciate your help on this

Thanks,
Vidya Jayakumar



gbrange_e38b65a.zip

7 Replies

YG Yavanaarasi G Syncfusion Team July 16, 2008 05:27 AM UTC

Hi Vidhya,

Thank you for your interest in Syncfusion products.

Essential XlsIO provides various ways to manipulate the range. You can define the named ranges and also copy the range by using the below code snippet:


//Defining the Range
IName lname1 = sheet.Names.Add("cities");

//Another way to refer range of cells.
lname1.RefersToRange = sheet.Range[98,4,98,5];

// Copying a Range
IRange source = sheet.Range["D8:D9"];
IRange des = sheet.Range["E93"];
source.CopyTo(des,ExcelCopyRangeOptions.None|ExcelCopyRangeOptions.CopyStyles);


You can delete each cell value by using the below code snippet:

IRange source = sheet.Range["D8"];
// Clear Range
source.Clear(true);

Please refer our shipped browser samples to demonstrates the support for manipulating range of cells.
\\Syncfusion\EssentialStudio\6.3.0.30\Windows\XlsIO.Windows\Samples\2.0\DataManagement\RangeManipulation\CS

Please try this and let me know if this helps.

Regards,
G.Yavana



VJ Vidya Jayakumar July 16, 2008 05:34 PM UTC

Thank you, i will try this and will let you know

Regards,
Vidya

>Hi Vidhya,

Thank you for your interest in Syncfusion products.

Essential XlsIO provides various ways to manipulate the range. You can define the named ranges and also copy the range by using the below code snippet:


//Defining the Range
IName lname1 = sheet.Names.Add("cities");

//Another way to refer range of cells.
lname1.RefersToRange = sheet.Range[98,4,98,5];

// Copying a Range
IRange source = sheet.Range["D8:D9"];
IRange des = sheet.Range["E93"];
source.CopyTo(des,ExcelCopyRangeOptions.None|ExcelCopyRangeOptions.CopyStyles);


You can delete each cell value by using the below code snippet:

IRange source = sheet.Range["D8"];
// Clear Range
source.Clear(true);

Please refer our shipped browser samples to demonstrates the support for manipulating range of cells.
\\Syncfusion\EssentialStudio\6.3.0.30\Windows\XlsIO.Windows\Samples\2.0\DataManagement\RangeManipulation\CS

Please try this and let me know if this helps.

Regards,
G.Yavana





VJ Vidya Jayakumar July 21, 2008 03:24 AM UTC

Hi,

As i had mentioned i have 2 worksheets. I want to update the named ranges for in sheet1.
I tried the following:-
'clearing the range in sheet2
Dim range1 As IRange = heet2.UsedRange("oap")
range1.Clear()

'adding new values from database via this method.
sheet2.ImportDataTable(listdatatable, False, 2, 1)

'Adding new name to the newly added values in sheet2.
Dim oName As IName = sheet2.Names.Add("oap", range2)

'updating the formulas in sheet1.
sheet1.Range("A1").Formula = "="Sheet1!$A$2:$A$" & counttoaddonformula(counts the number of added values + the
previous values and assignes that number to complete the formula) like this "Sheet1!$A$2:$A$8"

I know that i saw in your code samples formula works across worksheets. I tried to do the above. After doing so
i am getting a "#value" error on my sheet1 on cell "a1"
I can attach only 1 file now and the saved file is Testrangesavedlist.xls

If you open the Testrangesavedlist.xls on sheet1 you will find the following:-
1) "#value!" error
2) The formula in cell "a1" has changed from Sheet1!$A$2:$A$15 to Sheet1!$A$2:$A$19 but the new values are missing in that list,
though sheet2 is updated.

what is wrong here? Your help is greatly appreciated.

Thanks
Vidya


>Hi,

Please see the attached excel file

I am using Syncfusion.XlsIO.Web and Syncfusion.XlsIO.Base dll's to access excel file update values via VB.net application and saveit

1) In Sheet1, cell A2 references list values of the named range in Sheet2 A column which is called "Cities"
The names range formula (for sheet1,A2) is
=Sheet2!$A$2:$A$6 (ctrl+F3 to go to name manager window)

When i update values of cities i.e when i add more values for cities in asp.net application
I need to update excel file also with the updated named range.Currently in my excel file there are 5 cities.
After update let's say there are 7 cities.
Sheet 1 should reflect the updated range
The named range formula (for sheet1,A2) should be updated to =Sheet2!$A$2:$A$8

2) Also how can i delete each cell value using Syncfusion. Do you have any class targeting Excel cell?

I appreciate your help on this

Thanks,
Vidya Jayakumar



gbrange_e38b65a.zip



testrangesavedlist_d5ceb648.zip


VJ Vidya Jayakumar July 21, 2008 03:26 AM UTC

I am also attaching the original file which is saved as testrangesavedlist.xls here

Vidya

>Hi,

Please see the attached excel file

I am using Syncfusion.XlsIO.Web and Syncfusion.XlsIO.Base dll's to access excel file update values via VB.net application and saveit

1) In Sheet1, cell A2 references list values of the named range in Sheet2 A column which is called "Cities"
The names range formula (for sheet1,A2) is
=Sheet2!$A$2:$A$6 (ctrl+F3 to go to name manager window)

When i update values of cities i.e when i add more values for cities in asp.net application
I need to update excel file also with the updated named range.Currently in my excel file there are 5 cities.
After update let's say there are 7 cities.
Sheet 1 should reflect the updated range
The named range formula (for sheet1,A2) should be updated to =Sheet2!$A$2:$A$8

2) Also how can i delete each cell value using Syncfusion. Do you have any class targeting Excel cell?

I appreciate your help on this

Thanks,
Vidya Jayakumar



gbrange_e38b65a.zip



NR Test file_288b5831.zip


YG Yavanaarasi G Syncfusion Team July 21, 2008 01:00 PM UTC

Hi Vidhya,

Thank for your update and files.

Normally in MS Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error. Please refer the below link:

http://www.updatexp.com/excel-value-error.html

Also, could you please provide the formula that you have tried to update in sheet1?

Regards,
G.Yavana



VJ Vidya Jayakumar July 24, 2008 02:08 PM UTC

Hi,

We have decided to use offset formula in excel. So we dont have to assign any formula on sheet1 in .net application. When the values are added in sheet2 the sheet1 values automatically gets updated.

Thank you for your help on this.

Vidya

>Hi Vidhya,

Thank for your update and files.

Normally in MS Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error. Please refer the below link:

http://www.updatexp.com/excel-value-error.html

Also, could you please provide the formula that you have tried to update in sheet1?

Regards,
G.Yavana





YG Yavanaarasi G Syncfusion Team July 28, 2008 03:38 AM UTC

Hi Vidhya,

Thank you for sharing your ideas.

Please let me know if you have any other concerns.

Regards,
G.Yavana


Loader.
Live Chat Icon For mobile
Up arrow icon