C# and Excel Object range

[Maik]

Hello,

I've got a problem with access to special ranges. I renamed some cells (for
example "C4" in "CUSTOM.GENERAL .VPRIM"). This is necessary, because I have to
read out only these cells in active worksheet. The method
get_range("Rang eName", Missing.Value) is ok as far as I have an existing
RangeName.

My program compares the root of a customized XML file with the range of an
Excel file. But some root names don't exist in the Excel file. If I deliver a
wrong RangeName to the get_range() Method I get an Exception. So, I have to
check the existance of a RangeName at first.

Can anybody help me? Info: Excel 11.0 reference.

Many Thanks - Maik

[Dave]

Just put a Try...Catch around it. If the range isn't named, you can't use it, correct?

--
Dave Sexton
dave@www..jwaon line..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussio ns.microsoft.co m> wrote in message news:35******** *************** ***********@mic rosoft.com...

Hello,

I've got a problem with access to special ranges. I renamed some cells (for
example "C4" in "CUSTOM.GENERAL .VPRIM"). This is necessary, because I have to
read out only these cells in active worksheet. The method
get_range("Rang eName", Missing.Value) is ok as far as I have an existing
RangeName.

My program compares the root of a customized XML file with the range of an
Excel file. But some root names don't exist in the Excel file. If I deliver a
wrong RangeName to the get_range() Method I get an Exception. So, I have to
check the existance of a RangeName at first.

Can anybody help me? Info: Excel 11.0 reference.

Many Thanks - Maik

[Maik]

Hello Dave,

yes, it's correct, but the function delivers min. 20 non-existent ranges to
the get_range method. Is it possible to call the catch handler for 20 times?

[Dave]

You can place the code within a method and take in the parameters that the get_range method takes. I'm assuming that when you say,
"but the function delivers min. 20 non-existent ranges " that you have to check each range using the get_range function seperately
anyway:

public object GetRange(object data)
{
try
{
return excel.get_Range (data);
}
catch (System.Runtime .InteropService s.COMException)
{
return null;
}
}

--
Dave Sexton
dave@www..jwaon line..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussio ns.microsoft.co m> wrote in message news:70******** *************** ***********@mic rosoft.com...

Hello Dave,

yes, it's correct, but the function delivers min. 20 non-existent ranges to
the get_range method. Is it possible to call the catch handler for 20 times?

[Maik]

Hello Dave,

thanks for your code. The program runs. But is there any other alternative
to solve the problem. I've heard about a code with VB but the translation to
C# don't run with Office primary interop assemblies.

[Maik]

Hi,

I found a VBA code solving the problem.

Sub FindRangeName()
Dim n As Name
For Each n In ActiveWorkbook. Names
If n.Name = "CUSTOM.GENERAL " Then
MsgBox "Name " & n.Name & " exist, Refer to cell " & _
n.RefersToRange .Address
End If
Next
End Sub

But a translation to C# with Office XP PIA's don't work.

[Dave]

> But a translation to C# with Office XP PIA's don't work.

I've heard this before in a different thread. How is this possible?
A C# Project can't reference an Office XP PIA?

I don't get it. Can you please explain what the issue is?

--
Dave Sexton
dave@www..jwaon line..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussio ns.microsoft.co m> wrote in message news:26******** *************** ***********@mic rosoft.com...

Hi,

I found a VBA code solving the problem.

Sub FindRangeName()
Dim n As Name
For Each n In ActiveWorkbook. Names
If n.Name = "CUSTOM.GENERAL " Then
MsgBox "Name " & n.Name & " exist, Refer to cell " & _
n.RefersToRange .Address
End If
Next
End Sub

But a translation to C# with Office XP PIA's don't work.

[Maik]

Hello Dave,
here is a cutout of my code. I work with Office XP PIA at work and with
Office 2003 PIA at home. I will get an
"System.Runtime .InteropService s.COMException" exception if I trie the foreach
loop "foreach(Excel. Name name in workBook.Names) ".
I posted this message also to
<http://www.codeproject .com/csharp/csharp_excel.as p?df=100&forumi d=23997&select= 1093302&msg=109 3302>

I don't know why it isn't working.
Excel.Applicati on excelApp = null;
Excel.Workbook workBook = null;
System.Globaliz ation.CultureIn fo oldCI =
System.Threadin g.Thread.Curren tThread.Current Culture;
object missing = Missing.Value;

private bool StartExcel()
{
try
{
if(excelApp == null)
{
excelApp = new Excel.Applicati on();
excelApp.Visibl e = true;

// Workaround to prevent a known bug
int nId = excelApp.Langua geSettings.get_ LanguageID(
Microsoft.Offic e.Core.MsoAppLa nguageID.msoLan guageIDInstall) ;
System.Threadin g.Thread.Curren tThread.Current Culture =
new System.Globaliz ation.CultureIn fo(nId);
return true;
}
else
{
MessageBox.Show ("Excel is been started");
return false;
}
}
catch (Exception theException)
{
Debug.WriteLine (theException.M essage);
return false;
}
}

private bool OpenWorkbook(st ring filename)
{
try
{
workBook =
excelApp.Workbo oks.Open(filena me,0,false,5,"" ,"",false,Excel .XlPlatform.xlW indows,
"",true,false,0 ,true,false,fal se);
return true;
}
catch
{
Debug.WriteLine ("Could not open Workbook!");
return false;
}
}
void ReadFromSheet()
{
Excel.Worksheet sheet;
Excel.Range range;
sheet = (Excel.Workshee t) workBook.Active Sheet;
string rangeName = "CUSTOM.GENERAL ";

try
{
foreach(Excel.N ame name in workBook.Names)
{
if(name.Name == "CUSTOM.GENERAL ")
{
MessageBox.Show ("Name" + name.Name + "exist, refers to cell" +
name.RefersToRa nge);
range = sheet.get_Range (rangeName,miss ing);
range.Select();
}
}
}
catch(System.Ru ntime.InteropSe rvices.COMExcep tion e)
{
Debug.WriteLine (e.Message);
}
catch(System.Nu llReferenceExce ption e)
{
Debug.WriteLine (e.Message);
}
catch(System.Ex ception e)
{
Debug.WriteLine (e.Message);
}

[Dave]

That's interesting; thank you for the response.

The code you have sent will work as-is when converted directly to VB.NET?

I got it to work for me by making some changes (using a more verbose method):
Excel._Workbook book = app.Application .ActiveWorkbook as Excel._Workbook ;

for (int i = 1; i < book.Names.Coun t; i++)

{

System.Windows. Forms.MessageBo x.Show(book.Nam es.Item(i, Type.Missing, Type.Missing).N ame);

}
--
Dave Sexton
dave@www..jwaon line..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussio ns.microsoft.co m> wrote in message news:F1******** *************** ***********@mic rosoft.com...

Hello Dave,
here is a cutout of my code. I work with Office XP PIA at work and with
Office 2003 PIA at home. I will get an
"System.Runtime .InteropService s.COMException" exception if I trie the foreach
loop "foreach(Excel. Name name in workBook.Names) ".
I posted this message also to
<http://www.codeproject .com/csharp/csharp_excel.as p?df=100&forumi d=23997&select= 1093302&msg=109 3302>

I don't know why it isn't working.
Excel.Applicati on excelApp = null;
Excel.Workbook workBook = null;
System.Globaliz ation.CultureIn fo oldCI =
System.Threadin g.Thread.Curren tThread.Current Culture;
object missing = Missing.Value;

private bool StartExcel()
{
try
{
if(excelApp == null)
{
excelApp = new Excel.Applicati on();
excelApp.Visibl e = true;

// Workaround to prevent a known bug
int nId = excelApp.Langua geSettings.get_ LanguageID(
Microsoft.Offic e.Core.MsoAppLa nguageID.msoLan guageIDInstall) ;
System.Threadin g.Thread.Curren tThread.Current Culture =
new System.Globaliz ation.CultureIn fo(nId);
return true;
}
else
{
MessageBox.Show ("Excel is been started");
return false;
}
}
catch (Exception theException)
{
Debug.WriteLine (theException.M essage);
return false;
}
}

private bool OpenWorkbook(st ring filename)
{
try
{
workBook =
excelApp.Workbo oks.Open(filena me,0,false,5,"" ,"",false,Excel .XlPlatform.xlW indows,
"",true,false,0 ,true,false,fal se);
return true;
}
catch
{
Debug.WriteLine ("Could not open Workbook!");
return false;
}
}
void ReadFromSheet()
{
Excel.Worksheet sheet;
Excel.Range range;
sheet = (Excel.Workshee t) workBook.Active Sheet;
string rangeName = "CUSTOM.GENERAL ";

try
{
foreach(Excel.N ame name in workBook.Names)
{
if(name.Name == "CUSTOM.GENERAL ")
{
MessageBox.Show ("Name" + name.Name + "exist, refers to cell" +
name.RefersToRa nge);
range = sheet.get_Range (rangeName,miss ing);
range.Select();
}
}
}
catch(System.Ru ntime.InteropSe rvices.COMExcep tion e)
{
Debug.WriteLine (e.Message);
}
catch(System.Nu llReferenceExce ption e)
{
Debug.WriteLine (e.Message);
}
catch(System.Ex ception e)
{
Debug.WriteLine (e.Message);
}

[Maik]

I haven't translated it to VB.NET. I only searched in other forums for code.
But all forums shows working code in VB. I translated this VB code for myself
in hope that it will be running. But it doesn't run.