Hi All
Do you know any method that can combine two ranges
Using following code I can't achieve this because COMException is raised with HRESULT 0x800A03EC:
using
Microsoft.Office.Interop.Excel;Range
r = sht.get_Range( "B2,C3", missing ); // faires COMException with HRESULT = 0x800A03EC:
How to combine two ranges?
R.Tutus
Hi Dmitriy,
I just wanted to follow up because your original example was on the right track and you shouldn't have to use Application.Union and it's 30 arguments. For example, the snippet below shows two methods of doing this (1 using VSTO worksheet, 1 using Interop worksheet) that work just fine for me. Try and declare r as Excel.Range instead of Range - you may unintentionally be declaring a System.Data.Range object.
Regards,
Steve
private void example()
{
try
{
// "this" is type: Microsoft.Office.Tools.Excel.Worksheet
// method 1 - works
Excel.Range r = this.Range["A5,B6,C7:D10", missing];
r.Value2 = 100;
// method 2 - works -
// when using Microsoft.Office.Interop.Excel.Worksheet
Excel.Range r2 = this.InnerObject.get_Range("B2,C3", missing);
r2.Value2 = 200;
// Application.Union also works but has 30 arguments
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Travis Ingram
Hi Dmitriy
Well, yes, you've posted in the VSTO forum, so generally we'd expect someone to be using the Tools and not the Interop. But Steve did also give an Interop example.
However, the syntax you both used in your Interop examples is confusing me. I don't find any method signatures that match what you've done. But this works just fine for me:
xl.Worksheet ws = wkb.Sheets[1] as xl.Worksheet;
xl.Range rng = ws.get_Range("B2", "C3");
rng.Value2 = "test";
If I try to use the syntax you have (get_Range("B2, C3", missing)) then I see the same error you report AND the correct syntax is indicated as part of the error message.
A range is defined by specifying the top-left and bottom-right cells of the range. If you want to assign only one cell to the range, then you give that cell as the first parameter and pass Type.Missing as the second. For more cells, the top-left cell is the first parameter and the bottom-right the second... And you do NOT pass Type.Missing.
Tom Lake - MSFT
Looks like I'm using Microsoft.Office.Interop.Excel and you are using Microsoft.Office.Tools.Excel. That is why the results differ.
Alexander Petukhov
In my locale(ru-RU) semicolon used as list separator. I've replaced comma with semicolon (get_Range("B2;E3", missing)) but the result have not changed.
How do you change culture Like following
Thread.CurrentThread.CurrentCulture = new CultureInfo( "en-US" );
I've discovered Office.Tools with Reflector. Office.Tools.Worksheet.Range uses Interop.Worksheet.get_Range().
Tecnomage
See my message of July 27, that supercedes the one you're quoting. I use the technique described in the article for which I provide a link. This technique is not providing the environment Excel seems to require...
fiddlesticks
Hi Cindy,
Actually there is a difference in the way Dmitriy and I were using it and your example. If you supply both arguments, you're specifying the top left and bottom right cells and the result is a range of contiguous cells. If you do not want contiguous cells, you need to use the other method. The difference is demonstrated in the following snippet (code part of Sheet1.cs).
Does the example method below work for you If not, we should compare environments because it works fine on my end.
If it doesn't work- DMitriy you should try this too - try the equivalent VBA and see if that works. I've added the equivalent VBA at the bottom.
private void example()
{
try
{
// this.InnerObject type = Microsoft.Office.Interop.Excel
// r2 = range consisting of two cells: B2 and E3
Excel.Range r2 = this.InnerObject.get_Range("B2,E3", missing);
r2.Value2 = 200;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void example2()
{
try
{
// this.InnerObject type = Microsoft.Office.Interop.Excel
// r2 = range consisting of eight cells
Excel.Range r2 = this.InnerObject.get_Range("B2", "E3");
r2.Value2 = 200;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
VBA to do the same thing:
Sub Test()
Dim rg as Range
Set rg = ThisWorkbook.Worksheets(1).Range("B2,E3")
rg.Value = 100
Set rg = Nothing
End Sub
Thanks,
Steve
sarathy
OK, I changed my windows Regional settings to en-us (which is what Excel wants to have) and indeed, the syntax now works.
So it is a problem with Culture Info, and apparently the approach described in the MSDN article doesn't cover it
http://msdn.microsoft.com/library/default.asp url=/library/en-us/dv_wrcore/html/wrconGlobalizingLocalizingOfficeSolutions.asp
Mike Batton
My compiler does not see neither Workseet.Range property nor Worksheet.InnerObject properties. Only Worksheet.get_Range method. I have Office 2003 (11.5612.5606), VS2005 Sp1 (8.0.50727.762), VSTO (8.0.50727.816).
And as I've said before get_Range generates COMException (HRESULT: 0x800A03EC) if I pass comma delimeted range.
'r' variable is declared correctly. I've extended global namespace with Microsoft.Office.Interop.Excel without pseudonyms. Otherwise compiler will not compile this code.
vakman
VBA analogue works fine.
Romel Evans
Hi Steve
1. Dmitriy (and therefore to test, I) are not using Office.Tools, so your C# code snippets are not pertinent.
2. I certainly did test get_Range("B2,E3", missing) on a worksheet object (as declared in my code snippet) and got the same error Dmitriy reports.
Note: This error number can also appear if the culture in a mixed language environment hasn't been set. However, my test code does explicitly set the culture, so I ruled that problem out.
FWIW I have run into this-and-that oddity not working correctly with Excel.Interop. Perhaps this is a problem in the PIAs that the VSTO team has smoothed out in the Office.Tools interface If that's the case, then Dmitriy you will need to use the Union method, as you mentioned in your first message. I suggest that you create one or more overloaded methods so that you don't have to enter all 30 parameters each time you want to use it (one method for each combination of parameters you use).
MWatts
Application.Union can do it.
PS. There is uncomfortable to pass all optional parameter to methods. Interop facade should overload such methods.
Alex Te.
Interesting. Perhaps it is something to do with the culture issue as even in a shared add-in I can use the technique without issue. For example, in a shared add-in (not using VSTO), I added a simple command bar button and hooked up the following event handler.
private void button_Click(Office.CommandBarButton cmdBarbutton, ref bool cancelButton)
{
try
{
Excel.Worksheet ws = (Excel.Worksheet)applicationObject.ActiveSheet;
Excel.Range rg = ws.get_Range("a2,e3", Type.Missing);
rg.Value2 = 100;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Thanks,
Steve