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("RangeName", 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..jwaonline..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussions.microsoft.com> wrote in message news:35**********************************@microsof t.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("RangeName", 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.InteropServices.COMException)
{
return null;
}
}

--
Dave Sexton
dave@www..jwaonline..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussions.microsoft.com> wrote in message news:70**********************************@microsof t.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..jwaonline..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussions.microsoft.com> wrote in message news:26**********************************@microsof t.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.InteropServices.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.asp?df=100&forumid=23997&select=10933 02&msg=1093302>

I don't know why it isn't working.
Excel.Application excelApp = null;
Excel.Workbook workBook = null;
System.Globalization.CultureInfo oldCI =
System.Threading.Thread.CurrentThread.CurrentCultu re;
object missing = Missing.Value;

private bool StartExcel()
{
try
{
if(excelApp == null)
{
excelApp = new Excel.Application();
excelApp.Visible = true;

// Workaround to prevent a known bug
int nId = excelApp.LanguageSettings.get_LanguageID(
Microsoft.Office.Core.MsoAppLanguageID.msoLanguage IDInstall);
System.Threading.Thread.CurrentThread.CurrentCultu re =
new System.Globalization.CultureInfo(nId);
return true;
}
else
{
MessageBox.Show("Excel is been started");
return false;
}
}
catch (Exception theException)
{
Debug.WriteLine(theException.Message);
return false;
}
}

private bool OpenWorkbook(string filename)
{
try
{
workBook =
excelApp.Workbooks.Open(filename,0,false,5,"","",f alse,Excel.XlPlatform.xlWindows,
"",true,false,0,true,false,false);
return true;
}
catch
{
Debug.WriteLine("Could not open Workbook!");
return false;
}
}
void ReadFromSheet()
{
Excel.Worksheet sheet;
Excel.Range range;
sheet = (Excel.Worksheet) workBook.ActiveSheet;
string rangeName = "CUSTOM.GENERAL";

try
{
foreach(Excel.Name name in workBook.Names)
{
if(name.Name == "CUSTOM.GENERAL")
{
MessageBox.Show("Name" + name.Name + "exist, refers to cell" +
name.RefersToRange);
range = sheet.get_Range(rangeName,missing);
range.Select();
}
}
}
catch(System.Runtime.InteropServices.COMException e)
{
Debug.WriteLine(e.Message);
}
catch(System.NullReferenceException e)
{
Debug.WriteLine(e.Message);
}
catch(System.Exception 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.Count; i++)

{

System.Windows.Forms.MessageBox.Show(book.Names.It em(i, Type.Missing, Type.Missing).Name);

}
--
Dave Sexton
dave@www..jwaonline..com
-----------------------------------------------------------------------
"Maik" <Ma**@discussions.microsoft.com> wrote in message news:F1**********************************@microsof t.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.InteropServices.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.asp?df=100&forumid=23997&select=10933 02&msg=1093302>

I don't know why it isn't working.
Excel.Application excelApp = null;
Excel.Workbook workBook = null;
System.Globalization.CultureInfo oldCI =
System.Threading.Thread.CurrentThread.CurrentCultu re;
object missing = Missing.Value;

private bool StartExcel()
{
try
{
if(excelApp == null)
{
excelApp = new Excel.Application();
excelApp.Visible = true;

// Workaround to prevent a known bug
int nId = excelApp.LanguageSettings.get_LanguageID(
Microsoft.Office.Core.MsoAppLanguageID.msoLanguage IDInstall);
System.Threading.Thread.CurrentThread.CurrentCultu re =
new System.Globalization.CultureInfo(nId);
return true;
}
else
{
MessageBox.Show("Excel is been started");
return false;
}
}
catch (Exception theException)
{
Debug.WriteLine(theException.Message);
return false;
}
}

private bool OpenWorkbook(string filename)
{
try
{
workBook =
excelApp.Workbooks.Open(filename,0,false,5,"","",f alse,Excel.XlPlatform.xlWindows,
"",true,false,0,true,false,false);
return true;
}
catch
{
Debug.WriteLine("Could not open Workbook!");
return false;
}
}
void ReadFromSheet()
{
Excel.Worksheet sheet;
Excel.Range range;
sheet = (Excel.Worksheet) workBook.ActiveSheet;
string rangeName = "CUSTOM.GENERAL";

try
{
foreach(Excel.Name name in workBook.Names)
{
if(name.Name == "CUSTOM.GENERAL")
{
MessageBox.Show("Name" + name.Name + "exist, refers to cell" +
name.RefersToRange);
range = sheet.get_Range(rangeName,missing);
range.Select();
}
}
}
catch(System.Runtime.InteropServices.COMException e)
{
Debug.WriteLine(e.Message);
}
catch(System.NullReferenceException e)
{
Debug.WriteLine(e.Message);
}
catch(System.Exception 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.

[Maik]

Hello Dave,
the code is running now. The snipped shows the readout of only one worksheet
for range names. The name and value will be saved in a hashtable.

private void getRangeValue()
{
Excel.Name name;
this.myParameters = new Hashtable();

try
{
//search for range names in selected worksheet
for(int i = 0; i< sheet.Names.Count ; i++)
{
name = sheet.Names.Item(i+1,missing,missing);
//only range names refering to one cell and featuring a value will be
added to hashtable
if(sheet.get_Range(name.Name, missing).Value2 != null)
{
if(sheet.get_Range(name.Name, missing).Value2.ToString() !=
"System.Object[,]")
{
//save parameters in global hashtable
string key = name.Name.Replace("'", "");
this.myParameters.Add(key,sheet.get_Range(name.Nam e, missing).Value2);
} //end if
} //end if
} //end for
} //end try

catch(System.Runtime.InteropServices.COMException caught)
{
MessageBox.Show("Fehler beim Zugriff auf benannte Zellbereiche im
aktuellen " +
"Tabellenblatt. Folgende Fehlermeldung wurde generiert:" +
Environment.NewLine +
Environment.NewLine + caught.Message, "Zugriffsfehler",
MessageBoxButtons.OK,
MessageBoxIcon.Hand);
}

catch(Exception caught)
{
MessageBox.Show("Es ist ein Fehler aufgetreten. Folgende Fehlermeldung
wurde " +
"generiert" + Environment.NewLine + Environment.NewLine +
caught.Message,
"Fehler", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}

finally
{
// clean up
name = null;
}
}