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?