Select Menu



por Alessandro Trovato

» » » » » » VBA - Artigo 026 - Conectando o Excel a um banco de dados
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Progredindo em VBA no Microsoft Excel

Conectando o Excel a um banco de dados

Muitas vezes as planilhas ficam muito extensas com muitos dados, tornando o carregamento da mesma muito demorado. Há outros casos em que a planilha precisa ser acessada por mais de uma pessoa ao mesmo tempo. Para esses e outros casos pode-se chegar à conclusão de que é melhor começar a guardar os dados em um banco de dados.

Há vários sistemas gerenciadores de bancos de dados (SGBDs) disponíveis no mercado, alguns pagos e outros gratuitos. Entre os pagos há versões gratuitas disponíveis, mas são limitadas e seu uso é mais para aprendizado. Aqui utilizarei o SQL Server Express, que é uma versão gratuita e limitada do SQL Server.

O primeiro passo é ir no editor de VBA, clicar no menu Ferramentas e escolher a opção Referências. Uma janela se abrirá e você deve habilitar a opção Microsoft ActiveX Data Objects 2.8 Library (biblioteca de objetos de dados ActiveX da Microsoft). Confira na imagem abaixo:


Sem essa referência não é possível conectar a nenhum banco de dados. Essa biblioteca contém os objetos necessários para estabelecer a conexão com o banco de dados. Feito isso, vamos passar para o código. Fazendo uma pesquisa na internet é possível encontrar vários links (inclusive no suporte da Microsoft) que mostram códigos muito similares ao código abaixo:

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
                  "Initial Catalog=MyDatabaseName;" & _
                  "Integrated Security=SSPI;"
   
' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM Table1;")
   
' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
   
End Sub

Esse código pode parecer desafiador ou mesmo assustador para quem está tendo um primeiro contato. Há dois objetos principais que são usados neste código e que você usará sempre que for fazer uma conexão com banco de dados:

- ADODB.Connection: É o objeto que trata da conexão ao banco de dados, criado no código acima como conn;
- ADODB.Recordset: É o objeto com o retorno do banco de dados (conjunto de registros), criado no código acima como rs.

Para o objeto de conexão conn conectar é preciso ter uma string de conexão, que no código acima recebeu o nome sConnString. Para cada SGBD é preciso usar uma string de conexão apropriada, o exemplo acima não deve funcionar em outros SGBDs. A melhor fonte de pesquisa é o site connectionstrings.com.

Essa string de conexão contém uma série de parâmetros que efetivam a conexão com o SGBD. Se você estiver utilizando o SQL Server Express será preciso editar dois parâmetros em relação ao exemplo acima. Data Source é a instância do banco de dados que você irá se conectar. Se você não sabe, entre no SQL Server Management Studio e veja o campo Nome do servidor na tela de conexão, é esse valor que será utilizado no código. Initial Catalog é o nome do banco de dados que contém as tabelas que você irá acessar.

Essa string de conexão é usada junto ao método open do objeto conn. Não há necessidade de se usar uma variável, poderia colocar a string diretamente com o open.
Assim como há o método open, há o close. Perceba que antes de fechar a conexão é feita uma checagem para ver se o objeto está aberto e isso é feito de uma forma que poucos devem compreender:

If CBool(conn.State And adStateOpen) Then conn.Close

CBool faz uma conversão para valor booleano e a comparação com operador And entre parênteses irá testar a nível de bit. Isso porque a propriedade State usa os valores da enumeração ObjectStateEnum:

- adStateClosed (0): Indica que o objeto está fechado;
- adStateOpen (1): indica que o objeto está aberto;
- adStateConnecting (2): indica que o objeto está conectando;
- adStateExecuting (4): indica que o objeto está executando um comando;
- adStateFetching (8): indica que o objeto está obtendo os registros solicitados.

Se você entende de flags binários e aritmética binária certamente vai entender o porquê da forma de comparação acima. Se não entende pode pesquisar na internet se tiver curiosidade. Uma outra forma de escrever essa verificação (e que eu prefiro) é a seguinte:

If (conn.State And adStateOpen) = adStateOpen Then conn.Close

No código há também o método Execute no objeto conn. É esse método que irá enviar o comando SQL ao SGBD para ser processado. Note que o resultado dessa operação está sendo armazenada no objeto rs (RecordSet), que é o objeto que armazenará o retorno do SQL.

Entre os quatro comandos básicos de SQL (Insert, Select, Update e Delete), o único que retorna registros é o Select. Desta forma, não há necessidade de usar o objeto de registros com os outros comandos, pois estes serão executados sem devolver registros.

Voltando ao código de exemplo, após a execução do comando SQL é feita uma verificação da propriedade EOF (end of file). Essa propriedade booleana tem valor verdadeiro quando encontra o fim dos registros. Fazendo a verificação no começo como no exemplo, se EOF for verdadeiro significa que não há nenhum registro retornado. Se houver registros, o passo seguinte no exemplo é colar todos os registros a partir da célula A1 usando o método CopyFromRecordset e o objeto rs.

Se você for simplesmente colar na planilha o resultado da consulta, o código de exemplo acima basta. Mas é bem provável que você precise manipular os dados recebidos em variáveis e o que foi visto até aqui não basta, é preciso conhecer mais detalhes.

Nos exemplos de código daqui para a frente utilizarei o objeto Registros (no plural mesmo, recordset significa conjunto de registros) para ADODB.Recordset.

O objeto ADODB.Recordset possui métodos e propriedades que permitem trabalhar registro a registro, permitindo manipular os dados sem necessidade de jogar tudo em uma planilha. Vamos começar com os métodos de posicionamento dos registros:

- MoveFirst: Posiciona o ponteiro no primeiro registro;
- MoveLast: Leva o ponteiro até o último registro;
- MoveNext: Passa para o próximo registro;
- MovePrevious: Volta ao registro anterior.

Na maioria dos casos será utilizado uma estrutura de repetição desta forma:

Do Until Registros.EOF = True
    ' Processamento do registro
    ' ...
    Registros.MoveNext
Loop

Esse laço irá processar os registros do primeiro até o último, quando EOF será verdadeiro. Desta forma conseguimos processar cada registro individualmente. Falta agora ver como obter o valor de cada coluna retornada pelo SQL.

O objeto ADODB.Recordset possui um índice, assim como outros objetos do Excel. O objeto de planilha, por exemplo, pode ser referenciado como Sheets(1), Sheets(2), etc para cada objeto de planilha que houver no arquivo. Aqui é a mesma coisa, podemos referenciar Registros(0), Registros(1) etc. Note que aqui a contagem começa do 0, não do 1. E da mesma forma que acontecem com o objeto Sheets, podemos referenciar o objeto pelo nome da planilha, como Sheets("Produtos"). No caso o nome é o da coluna retornada pelo SQL. Assim, se a primeira coluna for Codigo, podemos usar Registros("Codigo"), o que facilita muito o entendimento do código. Também é possível usar a forma Registros.Fields("Codigo"), mas a forma anterior é mais curta. Editando o exemplo anterior e usando um objeto Type para Produto:

Do Until Registros.EOF = True
    Produto.Codigo = Registros("Codigo")
    Produto.Nome = Registros("Nome")
    Produto.Preco = Registros("Preco")
    ' Restante do processamento do registro
    ' ...
    Registros.MoveNext
Loop

O objeto ADODB.Recordset possui muitas outras propriedades e métodos. Cito alguns bons para debug:

- GetString: Este método retorna uma string, sendo os campos separados por uma tabulação e cada registro em uma linha separada. É excelente quando está criando uma consulta e quer saber se foi bem sucedida sem esperar o código todo ficar pronto para executar, bastando usar com um debug.print para ver o resultado;
- Save: Permite gravar o resultado da consulta em um arquivo. Precisa de dois parâmetros, sendo o primeiro o nome do arquivo (com caminho) onde será gravado e o segundo precisa ser um valor da enumeração PersistFormatEnum. Há dois valores possíveis: adPersistADTG (formato ADTG - Advanced Data TableGram) e adPersistXML (formato XML - eXtensible Markup Language, mais fácil de visualizar em um editor de texto);
- Source: Esta propriedade retorna o comando SQL que foi utilizado no momento, útil para certificar de que está correto;
- State: Assim como o ADODB.Connection, o ADODB.Recordset também tem a propriedade State, que tem o mesmo comportamento.

Dentro do ADODB.Recordset há a coleção Fields, que foi vista acima. Ele não é necessário se quiser obter o valor (propriedade Value), mas há muitas outras propriedades bem úteis:

- Name: Fornece o nome do campo da consulta SQL;
- Type: Fornece o valor do tipo de dado da coluna. Para comparar é preciso usar a enumeração DataTypeEnum, que vem com a biblioteca ADODB. Há valores como adDate, adNumeric, adVarChar entre outros;
- DefineSize: Fornece o tamanho do campo (útil para Char e VarChar);
- ActualSize: Fornece o tamanho atual do campo;
- NumericScale: Fornece o número de casas decimais permitidas para um campo numérico;
- Precision: Fornece o número máximo de dígitos permitidos para um campo numérico.

Lembre-se que Fields é uma coleção e, portanto, deve ser utilizada com o índice (iniciando em zero), exceto quando usar o método Count, que retorna a quantidade de campos.

Com este conhecimento dá para fazer muita coisa com o Excel e o banco de dados que aquele exemplo do começo do artigo não permite. No começo será normal passar por alguns problemas e dores de cabeça, mas é um desafio que vale a pena.

Espero que este artigo seja de grande valia. Até o próximo!

Pedro Martins


Pós-graduando em Business Intelligence e Big Data pela Faculdade Impacta de Tecnologia. Formado em Tecnologia em Eletrônica Digital com Ênfase em Microprocessadores

Autor Pedro Martins

Esta é uma breve descrição no bloco de autor sobre o autor. Você edita-lo, no html
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

7 comentários

  1. Grande Pedro! Excelente artigo! Parabéns e mais uma vez obrigado pela contribuição.
    Está na hora do amigo ter um canal de vídeos ou escrever um livro sobre o tema!

    ResponderExcluir
  2. Antes de mais nada.. parabens pelo artigo, muito legal mesmo..eu uso o excel com access e uso a opcao "obter dados do access" na aba "dados" ela funciona bem para fazer select na tabela, sera que existe alguma forma de fazer update, insert e delete de forma tao simplificada como esta que eu uso?

    ResponderExcluir
    Respostas
    1. Olá, Antonio. Se sua conexão com o Access for via VBA, é possível incluir, apagar e alterar dados. Use o método Execute do objeto ADODB.Connection para executar comandos SQL. Da forma de obter dados do Access desconheço como efetuar alterações, acredito que não tenha.

      Excluir
  3. Parabéns Pedro!!! Excelente artigo assim como os demais que acompanho... Podemos pedir artigos aqui? Se me permite, peço um artigo sobre converter intervalo em Tabelas, criar, manipular, vantagens, etc... Abraço e muito obrigado por compartilhar conhecimento!!!

    ResponderExcluir
    Respostas
    1. Olá, Marcelo. Pode sugerir pautas sempre que quiser. Tudo vai depender do meu conhecimento :D
      Vou ver se consigo fazer um artigo sobre tabelas num futuro próximo.

      Excluir
  4. Muito bom, vou adquirir o projeto, é ótimo, não tinha visto aulas tão rentáveis no youtube.

    ResponderExcluir
    Respostas
    1. Grato pela mensagem José Wilson e por também compartilhar a postagem no Facebook. As aulas no Youtube são feitas com muito cuidado e os artigos do Pedro para o blog são espetaculares! Abraços, bons estudos e sucesso!

      Excluir