How to insert a new column, then write data to it?

[James .]

Hi All,

I've only been introduced to Excel-DNA the past 2 days and still coming to grips in using it.

I am converting all of our VBA (Excel2003) to .Net using Excel-DNA.

Here's one of those macros that I need to migrate to .Net.

What I'm trying to do is:

1. Have the user select a column (having text of a suburb/city e.g., "Kilbirnie Wellington")

2. Insert a new column to the right of the selection ([other column] [suburb/city] [new column])

3. Re-format the text from "Kilbirnie Wellington" to "Kilbirnie | Wellington" to cell on the new column next to the cell that has "Kilbirnie Wellington" 

I am at a loss on how to do #2 and #3 above.

Help!?

TYVM,

James

[Govert van Drimmelen]

Hi James,

Most of your VBA should work as VB.NET with Excel-DNA without significant changes.

Do you have a current VBA routine for your column manipulation, and what does it look like?

Are you using Visual Studio with VB.NET?

Then it helps to add a reference to the Microsoft.Office.Interop.Excel interop assembly - that will give you intellisense for the Excel object model.

-Govert

[James .]

Yes, we currently are using MS Office 2003 (Excel) and utilise macros written in VBA (ALT-F11).

I am stuck to using VS2008 for now (upgrading to VS2013 is still in the pipeline).

We will be upgrading to MS Office 365 in couple of months so there's a need to port the current VBA macros .Net.

I've tested several macros (loaded the .xla into Excel 2013) and those still work. But most VBA macros that process

huge data tend to be slow and we think that by using managed code the performance would increase significantly.

I could use VB but I'm more of a C# guy. Is there a code conversion tool that converts VBA into VB.Net? Or is

this the same tool that converts VB6 (and prior) code to VB.Net? (or VB to C#?)

I would've thought that by using ExcelDNA we can now do without the interop assemblies. Will using these 

affect performance?

I've also figured out how to do what I wanted.

Here's a snippet:

ExcelReference xlSelection = XlCall.Excel(XlCall.xlfSelection) as ExcelReference;

                string selectionAddress = (string)XlCall.Excel(XlCall.xlfReftext, xlSelection, true);

                ExcelReference sel = new ExcelReference(xlSelection.RowFirst, xlSelection.RowLast-1, xlSelection.ColumnFirst, xlSelection.ColumnLast);

                object[,] xlContents = sel.GetValue() as object[,];

                List<object> colContents = xlContents.Cast<object>().ToList();

                colContents.RemoveAll(c => c == ExcelDna.Integration.ExcelEmpty.Value);

                if (xlSelection == null)

                    MessageBox.Show("No current selection");

                int currentCol = Convert.ToInt16((XlCall.Excel(XlCall.xlfColumn) as object[,])[0, 0]);

                int newColNum = xlSelection.ColumnLast + 1;

                int firstRow = xlSelection.RowFirst;

                int lastRow = colContents.Count - 1;

                ExcelReference newSelection = new ExcelReference(firstRow, lastRow, newColNum, newColNum);

                XlCall.Excel(XlCall.xlcSelect, newSelection);

                XlCall.Excel(XlCall.xlcInsert, 4);

                List<string> subcity = new List<string>();

                // get the DB table to compare the selection with

                using (myEntities db = new myEntities()) {

                    var suburbs = db.OLDPcodes_SuburbList;

                    foreach (var cont in xlContents) {

                        string content = string.Empty;

                        if (cont != ExcelDna.Integration.ExcelEmpty.Value)

                            content = cont as string;

                        else

                            continue;

                        var found = suburbs.Where(s => ((s.Place + " " + s.District) == content)).FirstOrDefault();

                        string newColValue = string.Empty;

                        if (found != null) {

                            newColValue = found.Place + " | " + found.District;

                            subcity.Add(newColValue);

                            Debug.WriteLine(newColValue);

                        }

                    }

                }

                string[,] arr = new string[subcity.ToArray().Length, 1];

                for (int r = 0; r <= subcity.ToArray().Length-1; r++) {

                    arr[r, 0] = subcity[r];

                }

                // output the list to the new column

                newSelection.SetValue(arr);

[Govert van Drimmelen]

Hi James,

Most VBA code will work as VB.NET with only minor changes. A good start is this guide by Patrick O'Beirne: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

You can also mix VB.NET and C# assemblies in your add-in, so it might be possible to move some stuff to VB.NET and rewrite others in C#.

I would not expect managed code to perform better (or worse) than VBA when using the Excel COM object model. However, I understand that using the C# 'dynamic' keyword is slower than either late-binding in VB.NET (via the Object type) or using the PIA assemblies in either. This is due to the fact that the late-binding signature lookup is done per-object for the C# dynamic case, and per-class for VB.NET late-binding. So if you're talking to the Excel COM object model from C#, and need the best performance, I would recommend referencing either the official Primary Interop Assemblies, or using the version-independent assemblies of the NetOffice project. When using the PIA assemblies in .NET 4, there is the option to embed the interop types when compiling, so you need not redistribute anything extra.

When using NuGet, you can easily add the PIA assemblies using the "Excel-DNA.Interop" package.

On the other hand, from an Excel-DNA add-in you can use the Excel C API, which is what the code you post is doing (via the XlCall class). I can't recall any comparisons of whether this would perform better than the Excel COM interfaces for the kind of macro you show, so it would be worth profiling. One disadvantage of the C API is that it does cover as much of the Excel functionality as the COM Interfaces do. For the kind of sheet manipulation macros of your example, it's pretty much got stuck with the features that Excel '97 had. So you might be forced to use the COM interfaces to access some of the functionality anyway. But it should mostly be safe to mix and match

Anyway, good luck with your conversion to Excel-DNA, and please write back if you have any further questions or comments.

Regards,

Govert