Does ExcelReference contain the workbook encoded or not?
Jiri Pik
Hello:
I am slightly confused about the ExcelReference and its relationship to a Range.
1. Are the below two methods correct?
public static ExcelReference RangeToExcelReference(Range range)
{
return new ExcelReference(range.Row - 1,
range.Row - 1 + range.Rows.Count - 1,
range.Column - 1,
range.Column - 1 + range.Columns.Count - 1,
range.Worksheet.Name);
}
public static Range ReferenceToRange(ExcelReference xlref)
{
var app = ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;
var refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true);
var range = app.Range[refText];
return range;
}
2. If so, var x = ReferenceToRange(rangeToExcelReference(target)); is usually failing at the line var refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true);
3.
Most importantly, however, the ExcelReference constructor takes just a Worksheet Name, not a Workbook Name. This should not be correct as two workbooks can have the same sheet, correct? Should the Constructor be changed???
4. If I take, however, an ExcelReference constructed from XlCall.Excel(XlCall.xlfCaller) as ExcelReference, the var refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true); contains Workbook name.
Jiri Pik
Actually, the (4) returns the format of [Book2]Sheet1!$A$1:$A$251
And even if I change the Sheet Name to this format
public static ExcelReference rangeToExcelReference(Range range)
{
return new ExcelReference(range.Row - 1,
range.Row - 1 + range.Rows.Count - 1,
range.Column - 1,
range.Column - 1 + range.Columns.Count - 1,
"[" + range.Parent.Parent.Name + "]" + range.Worksheet.Name);
}
The line var refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true); called from var x = ReferenceToRange(rangeToExcelReference(target)); I throws an exception
ExcelDna.Integration.XlCallException was unhandled by user code
HResult=-2146233088
Message=Exception of type 'ExcelDna.Integration.XlCallException' was thrown.
Source=ExcelDna.Integration
StackTrace:
at ExcelDna.Integration.XlCall.Excel(Int32 xlFunction, Object[] parameters)
What am I doing wrong, pls?
--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.
Jiri Pik
Govert:
Apologies, I forgot to run this in ExcelAsync.QueueAsMacro which explains the failure.
So the only remaining questions are (the others have been resolved):
1.
Are the below two functions correct and would they always work for as many workbooks as correct.
public static ExcelReference rangeToExcelReference(Range range)
{
return new ExcelReference(range.Row - 1,
range.Row - 1 + range.Rows.Count - 1,
range.Column - 1,
range.Column - 1 + range.Columns.Count - 1,
"[" + range.Parent.Parent.Name + "]" + range.Worksheet.Name);
}
public static Range ReferenceToRange(ExcelReference xlref)
{
var app = ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;
var refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true);
var range = app.Range[refText];
return range;
}
2.
If the rangeToExcelReference is without
"[" + range.Parent.Parent.Name + "]"
it uses the active workbook, correct?
public static ExcelReference rangeToExcelReference(Range range)
{
return new ExcelReference(range.Row - 1,
range.Row - 1 + range.Rows.Count - 1,
range.Column - 1,
range.Column - 1 + range.Columns.Count - 1,
range.Worksheet.Name);
}
3.
If so, would it be possible to add them to the standard ExcelDna library as I guess everybody would benefit from these.
4. The SheetId in the ExcelReference remains the same even if I rename the workbook and sheet, correct?
Govert van Drimmelen
Sent: 19 January 2014 07:56 PM
Govert van Drimmelen
Sent: 19 January 2014 08:14 PM
To: exce...@googlegroups.com
Subject: RE: Does ExcelReference contain the workbook encoded or not?
Jiri Pik
This is excellent, thank you.
Jiri Pik
Hi:
Thank you very much for your kind help.
I have started to play with the listeners to the changes in the excel sheet and want to keep some data structure, say a dictionary, of some “range definition” –vs- formulas they have inside, i.e. Dictionary<string,string>. Then, whenever you change the range’s contents, the data structure needs to be updated.
“Range Definition” needs to be a string, and to make it independent of the names, the ExcelReference’s SheetId plus Column plus Row is a perfect candidate.
private void SetUpListeners()
{
try
{
var app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
((AppEvents_Event)app).SheetChange += (sh, target) =>
{
var fullAddress = target.Parent.Parent.Name + "!" +target.Parent.Name + "!" + target.Address;
ExcelAsyncUtil.QueueAsMacro(() =>{
var x = ReferenceToRange(rangeToExcelReference(target));
Log.Trace("new " + x.Parent.Parent.Name + "!" + x.Parent.Name + "!" + x.Address);
});
};
}
catch (Exception e)
{
Debug.Print(e.Message);
}
}
All my best!