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
Grande Pedro! Excelente artigo! Parabéns e mais uma vez obrigado pela contribuição.
ResponderExcluirEstá na hora do amigo ter um canal de vídeos ou escrever um livro sobre o tema!
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?
ResponderExcluirOlá, 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.
ExcluirParabé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!!!
ResponderExcluirOlá, Marcelo. Pode sugerir pautas sempre que quiser. Tudo vai depender do meu conhecimento :D
ExcluirVou ver se consigo fazer um artigo sobre tabelas num futuro próximo.
Muito bom, vou adquirir o projeto, é ótimo, não tinha visto aulas tão rentáveis no youtube.
ResponderExcluirGrato 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!
ExcluirMuito bom!
ResponderExcluirE para retornar os nomes dos campos?