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.
Unfortunately, activation email could not send to your email. Please try again.

Named Ranges

Thread ID:

Created:

Updated:

Platform:

Replies:

75187 Jul 15,2008 10:31 AM Jul 27,2008 11:38 PM ASP.NET Web Forms (Classic) 7
loading
Tags: XlsIO
Vidya Jayakumar
Asked On July 15, 2008 10:31 AM

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

Yavanaarasi G [Syncfusion]
Replied On July 16, 2008 01:27 AM

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


Vidya Jayakumar
Replied On July 16, 2008 01:34 PM

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




Vidya Jayakumar
Replied On July 20, 2008 11:24 PM

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

Vidya Jayakumar
Replied On July 20, 2008 11:26 PM

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

Yavanaarasi G [Syncfusion]
Replied On July 21, 2008 09:00 AM

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


Vidya Jayakumar
Replied On July 24, 2008 10:08 AM

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




Yavanaarasi G [Syncfusion]
Replied On July 27, 2008 11:38 PM

Hi Vidhya,

Thank you for sharing your ideas.

Please let me know if you have any other concerns.

Regards,
G.Yavana


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;