(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] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myForm
[/SIZE][SIZE=2][/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] 
[SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/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

commandarray = txtSelect.Text
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][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][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Use Procedure for Prepare=1;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Auto Translate=True;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Packet Size=4096;"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Workstation ID=MIKEHUDSON;"[/COLOR][/SIZE][/COLOR][/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][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)
.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
.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
.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] [/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][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][/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=#000000] [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE][/COLOR][/SIZE][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] 
[/COLOR][/SIZE][/COLOR][/SIZE][/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

 

[Carly]

Was wondering if anyone had ideas on this post or if there is a better place to post it? Any help would be greatly recieved.

Kind Regards

Carly

 

[Colin Legg]

Your connection string is wrong... I posted a reply on your thread at ExcelForum.

http://www.excelforum.com/excel-programming/765643-excel-vsto-creating-connections.html

 

[MikeSel]

Thanks Colin.

Looks like we are one forward, 2 back.

Just need to diagnose the next Exception, now.. I will post back if I resolve it.

System.NotImplementedException was unhandled by user code
Message="Not implemented (Exception from HRESULT: 0x80004001 (E_NOTIMPL))"
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.WorkbookConnection.get_ODBCConnection() at SQLCommander.myForm.CommArr() in form.vb:line 35 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:

 

[MikeSel]

Which was staring me in the face.........

I dispair with myself sometimes!

With sheet.QueryTables.Add(Workbook.Connections.Item(1).OLEDBConnection.Connection, sheet.Range("A1"), txtSelect.Text)

All now working as expected!

Thanks
For your help!

Mike