VBA - Artigo 026 - Conectando o Excel a um banco de dados

Postado Por: Unknown Data: 06:00 / comentario : 8

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