Importando e Exportando dados do Excel para o SQL Server com VBA

Aproveitando a pergunta de um colega, deixo aqui a resposta que se mostra uma das formas de recuperar e inserir dados do Excel para o SQL Server com VBA.

Para acompanhar o tópico, viste o link: http://www.juliobattisti.com.br/forum/forum_posts.asp?TID=15402

No SQL Server 2005 Express criei uma base de dados de nome “test_excel” com uma única tabela chamada “names” com os seguintes campos:

id – int – identity
name – nvarchar(50) – allownull = false

O código abaixo serve para extrair os dados no excel na planilha de nome names:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub RetrieveSQLServerData()
' Cria a conexão.
    Dim cntest_excel As ADODB.Connection
    Set cntest_excel = New ADODB.Connection
    ' Variável para armazenar a String de Conexão.
    Dim strConn As String
    'Informa o SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    'Conecta à base de dados Pubs no servidor local.
    strConn = strConn & "DATA SOURCE=WKS-DEVELOPER7\SQLEXPRESS;INITIAL CATALOG=test_excel;"
    'Usa autenticação integrada.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    'Abre a conexão.
    cntest_excel.Open strConn
    ' Cria o objeto Recordset.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    With rsPubs
        ' Associa a conexão.
        .ActiveConnection = cntest_excel
        ' Extrai os dados.
        .Open "SELECT * FROM names"
        ' Coloca os dados na planilha.
        Worksheets("names").Range("A1").CopyFromRecordset rsPubs
        ' Fecha a transação
        .Close
    End With
    ' Fecha conexão
    cntest_excel.Close
    Set rsPubs = Nothing
    Set cntest_excel = Nothing
End Sub

Para inserir dados na tabela, digite uma lista de nomes simples na coluna A da planilha nomes, por exemplo:

Julio
Robert
Edmilson
Felipe
Com isso, o seguinte código serve para inserir estes nomes na tabela names do SQL Server:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub InsertDataIntoSQLServer()
' Cria a conexão.
    Dim cntest_excel As ADODB.Connection
    Set cntest_excel = New ADODB.Connection
    ' Variável para controlar o percorrer das linhas da planilha.
    Dim i As Long
    ' Variável para armazenar a String de Conexão.
    Dim strConn As String
    'Informa o SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    'Conecta à base de dados Pubs no servidor local.
    strConn = strConn & "DATA SOURCE=WKS-DEVELOPER7\SQLEXPRESS;INITIAL CATALOG=test_excel;"
    'Usa autenticação integrada.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    'Abre a conexão.
    cntest_excel.Open strConn
    i = 1
    Do Until IsEmpty(Worksheets("names").Cells(i, 1))
        cntest_excel.Execute "INSERT INTO names (name) values ('" & Worksheets("names").Cells(i, 1).Value & "')"
        i = i + 1
    Loop
    ' Fecha conexão
    cntest_excel.Close
    Set rsPubs = Nothing
    Set cntest_excel = Nothing
End Sub

O código funciona para o SQL Server com minhas configurações. Verifique parâmetros como usuário e senha para fazer funcionar na sua máquina corretamente. Não esqueça também de adcionar a referência ao Microsoft Active Data Objects no projeto VBA.

Abraços

Tomás Vásquez

Comentários

comentários

2 thoughts on “Importando e Exportando dados do Excel para o SQL Server com VBA”

  1. Olá Prof. Tomás,
    Agradeço o tópico, acho que vai ser muito útil em minhas tarefas.
    Mas fico com uma dúvida: eu poderei programar as fórmulas do excel no SQL (isso eu já sei fazer) e ao exportar o excel interpretaria essa formula, nem que seja \=1 1\ .. ou mesmo \=A1 B1 C1\ … ou até mesmo (esse meu objetivo):
    \=MÉDIA(A1:A12)\ … Agradeço qualquer retorno.
    Abraço
    Novamente obrigado.
    Rogério

Comments are closed.