CODEWITHSUNDEEP
vb.netexcelexceptioninterop
amonroejj
amonroejj

Reputation: 633

Exception trying to add external data to Excel file via interop

I'm trying to mimic the behavior of Excel's "Data/From Other Sources/From SQL Server" functionality via automation. That is: Generate a blank spreadsheet, fire the SQL Server import wizard, save the resulting spreadsheet, exit.

I recorded a macro of me importing data. The resulting macro seemed fairly simple [paraphrased]:

With ActiveSheet.ListObjects.Add(SourceType  := blah, 
                                 Source      := Array(blah), 
                                 Destination := blah         ).QueryTable
    [set a bunch of properties]
End With

I wrote a skeleton program that was able to successfully start Excel, write "test" to A1, save the file, and quit Excel, so I know I was good to go there.

When I add the ActiveSheet.ListObjects.Add() call to my program it consistently bombs out with an ArgumentException. For the sake of comparison, I added a ListObjects.Add call that generates a local, not external, table and that DOES work.

Dim lo                      As Microsoft.Office.Interop.Excel.ListObject
'this works
lo = ws.ListObjects.Add(SourceType:=Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange,    Source:=ws.Range("$A$1"),     Destination:=ws.Range("$D$1"))
'this nearly identical code does not
lo = ws.ListObjects.Add(SourceType:=Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcExternal, Source:=arrConnectionString,  Destination:=ws.Range("$D$1"))

arrConnectionString is a three element string array copied verbatim from the macro. It's just one long ordinary-looking connection string that was chopped into three pieces:

{"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=BLAHBLAH\SQLEXPRESS;Use Procedure for Prepa" , _
 "re=1;Auto Translate=True;Packet Size=4096;Workstation ID=BLAHBLAH;Use Encryption for Data=False;Tag with column collation when ", _
 "possible=False;Initial Catalog=dummy"}

The exact error is: The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)). Any ideas?

Initially I considered using EPPlus, but it constructs the entire spreadsheet in memory. It would always run out of memory due to the size of data I need to export (120K rows x 300 columns).

Upvotes: 1

Views: 1210

Answers (1)

amonroejj
amonroejj

Reputation: 633

Per Ben Black's suggestion: Changing the Source parameter from string array to string got it working.

Seems to be a flaw in the docs at http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobjects.add%28v=office.14%29.aspx , which claim that Source should be an array of strings when SourceType is xlSrcExternal.

Upvotes: 1

Related Questions