Usando Excel VBA para Consultar um Banco de Dados SQL Server

Embora possamos criar Sets de Dados Externos no Excel que puxam informações de um Banco de Dados SQL Server, há momentos em que você quer verificar um valor ou consultar um valor de um banco de dados SQL Server subjacente diretamente de uma fórmula do Excel.

Veremos um exemplo que nos permite verificar o valor total da receita de um cliente a partir de uma fórmula do Excel.

O VBA Para Consultar um Banco de Dados SQL Server

A seguinte função VBA pode ser usada a partir de um Projeto VBA do Excel. Você precisará, entretanto, adicionar primeiro a biblioteca ADODB ao projeto.

Public Function LookupAWCustomerRevenue(intID As Long) As Currency Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnString As String If intID = 0 Then LookupAWCustomerRevenue = 0 Else strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open strConnString Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID) If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0 Else LookupAWCustomerRevenue = rs.Fields("CustRev").Value rs.Close End If End If End Function

Neste exemplo usamos uma conexão ADO para conectar a uma instância do SQL Server (DBSRV\SQL2014):

 strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"

• O parâmetro Provedor indica que uma conexão OLDEB será estabelecida e o parâmetro Fonte de Dados aponta para a Instância do SQL Server.
• O parâmetro Catálogo Inicial identifica o banco de dados a ser consultado (AdventureWorks2014)
• O parâmetro Segurança Integrada indica que o Windows Authemtication será usado para autenticar com o SQL Server.

Um objeto RecordSet (rs) é usado para criar um conjunto de registros a partir de uma instrução SELECT:

 Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

A instrução SELECT é construída a partir de uma string literal e o valor para a variável intID que é passada para a função quando ela é chamada.

O comando If no início verifica um valor intID de 0. Variáveis inteiras com valor zero se não forem inicializadas (em outras palavras, se nenhum valor for fornecido quando a função é chamada). Se nenhum valor for passado para a função, um valor 0 é retornado como valor de receita.

 If intID = 0 Then LookupAWCustomerRevenue = 0

O segundo comando If testa um valor não numérico sendo retornado pelo comando SELECT. Se um clientID passado para a função for válido, mas eles não tiverem pedido a expressão SUM(TotalDue) retornará um valor NULL. Se isto acontecer então a função retornará um valor 0 em vez de 0.

 If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0

O cenário de valor NULL pode ser visto na seguinte tela.

Pus um ponto de quebra na linha de código VBA contendo o comando If e abri a Janela Locals para que eu pudesse ver todos os valores das variáveis naquele ponto de execução.

Testei a função executando-a da Janela Immediate no editor VBA:

 ?LookupAWCustomerRevenue(1)

Com um ponto de parada definido a execução do código pára automaticamente na linha marcada e nos permite ver o ambiente naquele ponto de execução.

A Janela Locals na captura de tela acima mostra a variável de objeto Recordset rs, e especificamente o valor para o primeiro campo de rs, “CustRev”. Podemos ver que está definido como Null. Isto porque um Cliente com valor de CustomerID 1 não fez nenhum pedido e, portanto, não tem valor de receita resultante.

O seguinte screnn shot mostra que a consulta retorna NULL quando executada diretamente na instância do SQL Server:

Assumindo que um CustomerID válido é passado para a função e um valor não NULL é retornado pelo comando SELECT, a função retornará a receita total de vendas para aquele cliente como um valor em moeda.

 LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Chamar a função VBA a partir de uma fórmula do Excel

Chamar uma função VBA a partir de uma fórmula do Excel é simples. Quando você constrói uma Fórmula do Excel, digitando-a em uma célula intellisense exibirá funções VBA correspondentes, bem como funções incorporadas. A seguinte captura de tela mostra isto:

Você pode ver que há duas funções VBA listadas acima que começam com Lo: LookupAWCustomerRevenue e LookupPersonName.

O exemplo a seguir mostra a função LookupAWCustomerRevemue sendo usada em uma célula para calvular a receita total para o valor do CustomerID sepcified na coluna A da planilha:

Sumário

Neste artigo vimos um pouco de codificação VBA, algumas técnicas de Tratamento de Erros e Depuração VBA, e vimos como podemos chamar funções VBA a partir de uma fórmula do Excel. Sinta-se à vontade para nos enviar um e-mail para [email protected] se você tiver alguma dúvida relacionada a este artigo. Há também uma versão Access deste artigo.

Você pode descobrir que esses cursos aparecem em uma oferta de Disponibilidade Atrasada de vez em quando, oferecendo uma economia de 30%.