New much improved solution: A DataSource is more than just the data. There is hidden default BindingSource that makes the ComboBoxes follow. To avoid this coupling and also the data replication in the first version of this answer, all you need to do is create a separate BindingSource for each ComboBox. These share the DataTable … Read more
The error code 0x800A03EC (or -2146827284) means NAME_NOT_FOUND; in other words, you’ve asked for something, and Excel can’t find it. This is a generic code, which can apply to lots of things it can’t find e.g. using properties which aren’t valid at that time like PivotItem.SourceNameStandard throws this when a PivotItem doesn’t have a filter … Read more
“Floating” is the key to the problem here. What’s never not a problem (occasionally responsible for odd things) is relying on the message pump in Excel to dispatch Windows messages, the messages that make these controls respond to input. This goes wrong in WPF as much as Winforms, they have their own dispatch loop that … Read more
(As an alternative to the approach described below you should consider using Excel-DNA. Excel-DNA allows you to build a registration-free RTD server. COM registration requires administrative privileges which may lead to installation headaches. That being said, the code below works fine.) To create a real-time Excel automation add-in in C# using RtdServer: 1) Create a … Read more
Use NuGet (VS 2013+): The easiest way in any recent version of Visual Studio is to just use the NuGet package manager. (Even VS2013, with the NuGet Package Manager for Visual Studio 2013 extension.) Right-click on “References” and choose “Manage NuGet Packages…”, then just search for Excel. VS 2012: Older versions of VS didn’t have … Read more
You need to have installed Microsoft Visual Studio Tools for Office (VSTO). VSTO can be selected in the Visual Studio installer under Workloads > Web & Cloud > Office/SharePoint Development. After that create a generic .NET project and add a reference to Microsoft.Office.Interop.Excel via ‘Add Reference… > Assemblies’ dialog. Application excel = new Application(); Workbook … Read more
I’ll take this as a challenge, and will bet the fastest way to shuffle your data between Excel and C# is to use Excel-DNA – http://excel-dna.net. (Disclaimer: I develop Excel-DNA. But it’s still true…) Because it uses the native .xll interface it skips all the COM integration overhead that you’d have with VSTO or another … Read more
When using C# or VB.Net to either get or set a range, figure out what the total size of the range is, and then get one large 2 dimensional object array… //get values object[,] objectArray = shtName.get_Range(“A1:Z100”).Value2; iFace = Convert.ToInt32(objectArray[1,1]); //set values object[,] objectArray = new object[3,1] {{“A”}{“B”}{“C”}}; rngName.Value2 = objectArray; Note that its important … Read more
Here’s a bit more detail on RobertG5’s solution (too long for a comment): The problem was that the add-in had been hard disabled by Outlook. As I’ve learned, that’s something different than the “usual” not-loading scenario. The key to realizing this was to notice that the add-in did not show up under Inactive Application Add-Ins, … Read more
This problem occurs if you are using a backwards compatible sheet (a .xls) instead of a .xlsx To allow sheets to be opened in pre office 2007 version it can’t contain more than 65k rows. You can check the number of rows in your sheet by using ctrl+arrowdown till you hit the bottom. If you … Read more