C# and Excel Object range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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;
}
}
 
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.
 
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.
 
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?
 
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=1093302&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.CurrentCulture;
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.msoLanguageIDInstall);
System.Threading.Thread.CurrentThread.CurrentCulture =
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,"","",false,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);
}
 
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.Item(i, Type.Missing, Type.Missing).Name);

}
 
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.
 
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.Name, 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;
}
}
 
Back
Top