Saul_l
asked on
Excel Connection SQL in VSTO
Hello!
I am developing in VS an Addi in for excel.
I want to insert a table from SQl made some changes and update it. In excel is very easy only using the exteral data tool. I recorded the code in VBA and i use it in VS but i have some trubles the code is it:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Rib bon.Ribbon ControlEve ntArgs) Handles Button1.Click
Dim Sheet = CType(Globals.ThisAddIn.Ap plication. ActiveWork book.Works heets(1), _
Excel.Worksheet).GetVstoOb ject()
With Sheet.ListObjects.Add(Sour ceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1 ;Integrate d Security=SSPI;Persist Security Info=True;Data Source=slr;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SLR;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Avi2"), Destination:=Range("$A$1") ).QueryTab le
.CommandType = excel.XlCmdType.xlCmdTable
.CommandText = Array("""Avi2"".""dbo""."" Clientes"" ")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = excel.XlCellInsertionMode. xlInsertDe leteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\Saul\Documents\M is archivos de origen de datos\slr Avi2 Clientes.odc"
.ListObject.DisplayName = "Tabla_slr_Avi2_Clientes"
.Refresh(BackgroundQuery:= False)
End With
End Sub
the "Source:=Array" and the range word in the destination are underlined with blu line and when i delete it and run it i get this error
El parámetro no es correcto. (Excepción de HRESULT: 0x80070057 (E_INVALIDARG))
in english the parameter is not correct
any idea to solve this thanks a lot!!
I am developing in VS an Addi in for excel.
I want to insert a table from SQl made some changes and update it. In excel is very easy only using the exteral data tool. I recorded the code in VBA and i use it in VS but i have some trubles the code is it:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Rib
Dim Sheet = CType(Globals.ThisAddIn.Ap
Excel.Worksheet).GetVstoOb
With Sheet.ListObjects.Add(Sour
"OLEDB;Provider=SQLOLEDB.1
.CommandType = excel.XlCmdType.xlCmdTable
.CommandText = Array("""Avi2"".""dbo"".""
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = excel.XlCellInsertionMode.
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\Saul\Documents\M
.ListObject.DisplayName = "Tabla_slr_Avi2_Clientes"
.Refresh(BackgroundQuery:=
End With
End Sub
the "Source:=Array" and the range word in the destination are underlined with blu line and when i delete it and run it i get this error
El parámetro no es correcto. (Excepción de HRESULT: 0x80070057 (E_INVALIDARG))
in english the parameter is not correct
any idea to solve this thanks a lot!!
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
Dim Sheet = CType(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1), _
Excel.Worksheet).GetVstoObject()
With Sheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=slr;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SLR;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Avi2"), Destination:=Range("$A$1")).QueryTable
.CommandType = excel.XlCmdType.xlCmdTable
.CommandText = Array("""Avi2"".""dbo"".""Clientes""")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = excel.XlCellInsertionMode.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\Saul\Documents\Mis archivos de origen de datos\slr Avi2 Clientes.odc"
.ListObject.DisplayName = "Tabla_slr_Avi2_Clientes"
.Refresh(BackgroundQuery:=False)
End With
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.