ListObjects

[Chris Dault]

New to ExcelDNA and I'm converting/rewriting some old company macros into an addon and I cannot for the life of me figure out how to get ListObjects to work.

I created a new project and added a custom ribbon with a test button that calls:

using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;

public static void LoTest()
{
var xlApp = (Application)ExcelDnaUtil.Application;
xlApp.Workbooks.Add();
Worksheet sht = xlApp.Sheets.Add();
sht.Activate();
var src = new[] { "redacted connection string" };
ListObject lo = xlApp.ActiveSheet.ListObjects.Add(XlListObjectSourceType.xlSrcExternal, src, true, XlYesNoGuess.xlYes, xlApp.ActiveSheet.Range["A1"]);
}

The list objects.add call fails every time no matter what I do with:

System.ArgumentException: Value does not fall within the expected range.

Did I miss something super basic somewhere? The other things I've played with so far manipulating columns, cells, etc all seem to work fine. I can get it to create a query table from the same datasource as well, but I really need the listobject.

[Terry Aney]

Could you paste the currently working VBA/Macro code?

[Chris Dault]

This is what the old code is using, but even today, the macro recorder spits out the same if you do it manually.

With ActiveSheet.ListObjects.Add(SourceType:=xlSrcExternal, Source:=Array( _
"redacted connection string"), _
Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = sSQL
.ListObject.DisplayName = "Table_Inv"
.Refresh BackgroundQuery:=False
End With

Since this is using "with" to deal with the returned listobject's query table it's not exactly the same, but I can't get it to create the listobject in the first place.

[Terry Aney]

Tried digging around, but couldn't get any further than you.  Sorry.  Hopefully the smart kids come in the room.

[Chris Dault]

I didn't get a chance to test it with ExcelDNA yet, but I also tried VSTO and NetOffice versions and had the same/similar issues. I found a post on stack overflow about it and this solution did work for my NetOffice version. It appears even though MS docs say to use a string array and the vba macro recorder generates the conn string as an array, you need to send a pure string variable.

vb.net - Exception trying to add external data to Excel file via interop - Stack Overflow

[Terry Aney]

I tried that solution too, but I was still getting error (but my connection string wasn't 'real' so maybe that was issue).  If it works in all those, it'll work in DNA.  Good luck.