none
Update em tabela SQL através de macro com MS Excel RRS feed

  • Pergunta

  • Faço diversas consultas no SQL Server via ODBC usando o MS Excel, tudo OK!

    Preciso inserir numa tabela simples e sem vínculos ou índices o resultado de um cálculo feito no MS Excel...

    Como proceder ? Alguém conhece o caminho da pedras ?

    quarta-feira, 8 de abril de 2015 11:23

Respostas

  • Se alguém estiver com o mesmo problema, segue solução encontrada:

    Sub grava()
        'Referência ativada = Microsoft Activex Data Objects 6.1 Library
        Dim cn As ADODB.Connection
        Dim strSQL As String
        Dim lngRecsAff As Long
        Set cn = New ADODB.Connection
        cn.Open "Provider=SQLOLEDB;Data Source=<serv>;Initial Catalog=<banco_de_dados>;User ID=<user>;Password=<pswrd>"
        For IX = 2 To ActiveCell.SpecialCells(xlLastCell).Row 'ultima linha
            strSQL = "INSERT INTO E0200 VALUES ('" & Worksheets("Plan1").Cells(IX, 1).Value & "', '" & Worksheets("Plan1").Cells(IX, 2).Value & "',0)"
            Debug.Print strSQL
            cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        Next IX
        Debug.Print "Records affected: " & lngRecsAff
        cn.Close
        Set cn = Nothing
    End Sub

    • Marcado como Resposta MLRamos quinta-feira, 16 de abril de 2015 10:43
    quinta-feira, 16 de abril de 2015 10:43

Todas as Respostas

  • Deleted
    quarta-feira, 8 de abril de 2015 12:46
  • José,

    Respondendo sua pergunta: a tabela está previamente criada no SQL Server, preciso apenas "popular" a mesma a partir de uma tabela do MS Excel e gostaria de fazê-la a partir do VBA mesmo, ou seja, o artigo vai de encontro a minha solicitação...

    Li o artigo, mas confesso que fiquei confuso, rsrs

    Criei uma tabela qualquer, tipo cadastro de produtos com CODIGO e DESCRICAO, mas não consegui fazer funcionar.

    Agradeço desde já qualquer complemento!

    quarta-feira, 8 de abril de 2015 15:40
  • up!

    (ainda não consegui fazer funcionar!)

    =(

    sexta-feira, 10 de abril de 2015 11:56
  • Trazendo os scripts da leitura inicial para cá (abaixo) e já com algumas adaptações...

    Neste exemplo, ADO_e_SQLOLEDB a conexão (open) aparentemente ocorre de forma correta e o erro ocorre no comando EXECUTE.
    Estou usando o MS Excel 2010 com a Referência ativada = Microsoft Activex Data Objects 6.1 Library

    Sub ADO_e_SQLOLEDB()
        Dim cn As ADODB.Connection
        Dim strSQL As String
        Dim lngRecsAff As Long
        Set cn = New ADODB.Connection
        cn.Open "Driver={SQL Server Native Client 11.0};Server=SERVxx;Database=TESTE;Uid=user;Pwd=password"
        'Import by using OPENDATASOURCE.
        strSQL = "SELECT * INTO E0200 FROM " & _
            "OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', " & _
            "'Data Source=C:\Users\MICROxx\Documents\ADO_e_SQLOLEDB.xlsm;" & _
            "Extended Properties=Excel 14.0')...[Plan1]"
        Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        Debug.Print "Records affected: " & lngRecsAff

        'Import by using OPENROWSET and object name.
        strSQL = "SELECT * INTO E0200 FROM " & _
            "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
            "'Excel 8.0;Database=C:\Users\MICROxx\Documents\ADO_e_SQLOLEDB.xlsm', " & _
            "[Plan1])"
        Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        Debug.Print "Records affected: " & lngRecsAff
        'Import by using OPENROWSET and SELECT query.
        strSQL = "SELECT * INTO XLImport8 FROM " & _
            "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
            "'Excel 8.0;Database=C:\Users\MICROxx\Documents\ADO_e_SQLOLEDB.xlsm', " & _
            "'SELECT * FROM [Plan1]')"
        Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        Debug.Print "Records affected: " & lngRecsAff
        cn.Close
        Set cn = Nothing
    End Sub

    Neste exemplo aqui tirado do mesmo artigo testei com muitas outras referências ativadas. As indicadas são Microsoft ActiveX Data Objects 2.1 Library ou posterior e 'Microsoft ADO Ext. 2.1 for DDL e segurança ou versão posterior, mas o erro "Provedor não encontrado" ocorre já no comando open...

    Sub ADO_e_ProvedorJet()
        Dim cn As ADODB.Connection
        Dim strSQL As String
        Dim lngRecsAff As Long
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Users\MICROxx\Documents\ADO_e_SQLOLEDB.xlsm;" & _
            "Extended Properties=Excel 8.0"
        'Import by using Jet Provider.
        strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
            "Server=<server>;Database=<database>;" & _
            "UID=<user>;PWD=<password>].XLImport9 " & _
            "FROM [Customers$]"
        Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        Debug.Print "Records affected: " & lngRecsAff
        cn.Close
        Set cn = Nothing
    End Sub

    Alguém ?

    Grato


    • Editado MLRamos terça-feira, 14 de abril de 2015 14:11 Revisão e correção do texto
    segunda-feira, 13 de abril de 2015 11:49
  • Se alguém estiver com o mesmo problema, segue solução encontrada:

    Sub grava()
        'Referência ativada = Microsoft Activex Data Objects 6.1 Library
        Dim cn As ADODB.Connection
        Dim strSQL As String
        Dim lngRecsAff As Long
        Set cn = New ADODB.Connection
        cn.Open "Provider=SQLOLEDB;Data Source=<serv>;Initial Catalog=<banco_de_dados>;User ID=<user>;Password=<pswrd>"
        For IX = 2 To ActiveCell.SpecialCells(xlLastCell).Row 'ultima linha
            strSQL = "INSERT INTO E0200 VALUES ('" & Worksheets("Plan1").Cells(IX, 1).Value & "', '" & Worksheets("Plan1").Cells(IX, 2).Value & "',0)"
            Debug.Print strSQL
            cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        Next IX
        Debug.Print "Records affected: " & lngRecsAff
        cn.Close
        Set cn = Nothing
    End Sub

    • Marcado como Resposta MLRamos quinta-feira, 16 de abril de 2015 10:43
    quinta-feira, 16 de abril de 2015 10:43