Question (ADV?) Excel VSTO - Creating Connections

[MikeSel]

Hello

I've come up with a bit of an issue when trying to programatically create workbook connections via an Excel 2007 Add-in created in Visual Studio 2008 (VB.net)

My current code is as follows:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Microsoft.Office.Interop.Excel[/SIZE]
[SIZE=2][COLOR=#0000ff]
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myForm[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Workbook [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Excel.Workbook = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](Globals.ThisAddIn.Application.ActiveWorkbook, Excel.Workbook)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sheet [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Excel.Worksheet = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet)[/SIZE]
 
[SIZE=2][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] CommArr()[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] commandarray[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] connectionstring[/SIZE]
 
[SIZE=2]commandarray = txtSelect.Text[/SIZE]
[SIZE=2]connectionstring = _[/SIZE]
[SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Initial Catalog="[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & cmbDB.Text & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]";"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Data Source="[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & cmbServer.Text & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]";"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Use Procedure for Prepare=1;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Auto Translate=True;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Packet Size=4096;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Workstation ID=MIKEHUDSON;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Use Encryption for Data=False;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Tag with column collation when possible=False"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]Workbook.Connections.Add(txtName.Text, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"SQL Commander Connection"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], connectionstring, commandarray, 2)[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]' Workbook.Connections.Item(1).OLEDBConnection.Connection.ToString()[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]sheet = Workbook.ActiveSheet[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sheet.QueryTables.Add(Workbook.Connections.Item(1).ODBCConnection.ToString, sheet.Range([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"A1"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]), txtSelect.Text)[/SIZE]
[SIZE=2].Name = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Data"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].FieldNames = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].RowNumbers = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].FillAdjacentFormulas = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].PreserveFormatting = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].RefreshOnFileOpen = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].BackgroundQuery = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells[/SIZE]
[SIZE=2].SavePassword = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].SaveData = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].AdjustColumnWidth = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].RefreshPeriod = 0[/SIZE]
[SIZE=2].PreserveColumnInfo = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2].Refresh(BackgroundQuery:=[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]sheet.Name = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Worksheet"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]
[/COLOR][/SIZE][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/SIZE][SIZE=3]

[/SIZE]

(This is not the complete code obviously - with the variables being populated from a user form)

Upon running this code throws an error:

The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))

Exception Details:

System.ArgumentException was unhandled by user code
  Message="The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))"
  Source=""
  StackTrace:
     Server stack trace:    Exception rethrown at [0]:     at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)    at Microsoft.Office.Interop.Excel.Connections.Add(String Name, String Description, Object ConnectionString, Object CommandText, Object lCmdtype)    at SQLCommander.myForm.CommArr() in form.vb:line 32    at SQLCommander.myForm.OK_Button_Click(Object sender, EventArgs e) in form.vb:line 10    at System.Windows.Forms.Control.*******(EventArgs e)    at System.Windows.Forms.Button.*******(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  InnerException:

Any help would be appreciated.

Thanks
Mike