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.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
 
 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

Open in new window

Saul_lAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
13598Connect With a Mentor Commented:
If you hover over the blue line wht does it tell you? My guess is that it is taking Range as a variable and it doesn't know what to do with it.
Can you qualify Range? Instead of Range use something like  Sheet.Range as in:
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:=Sheet.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
0
All Courses

From novice to tech pro — start learning today.