Como ligar para um SQL Server Procedimento armazenado do Excel VBA

Os dados em um servidor podem ser modificados examinando os registros 'do lado do cliente' no Excel VBA, alterando-os conforme necessário e salvando-os de volta no servidor.
Uma maneira mais eficiente de fazer isso, principalmente se o banco de dados estiver em um local remoto e houver muito tráfego envolvido, é fazer o trabalho 'do lado do servidor'. Este exercício chama um procedimento armazenado do Excel para categorizar os funcionários em faixas etárias de acordo com suas datas de nascimento (ou seja, 18 a 25 anos, 26 a 35 anos etc.), sem uma grande troca de dados entre o servidor e o Excel.

Este artigo pressupõe que o leitor tenha a faixa Desenvolvedor exibida e esteja familiarizado com o Editor VBA. Se não, por favor, Google “Excel Developer Tab” ou “Excel Code Window”.

Existem três elementos para o exercício:

• Uma tabela de dados tblPessoal dentro de um banco de dados TestDB;
• Um procedimento armazenado spAgeRange;
• Um xlsm do Excel, que chamaremos xlsm. Um exemplo de arquivo do Excel pode ser encontrado aqui

Tabela de dados

Criar um banco de dados em SQL Server chamado DBTest.

Configurar as seguintes colunas para uma tabela tblPessoal.

Copie o seguinte na tabela:

2017/05/25	1	Brown	J	1946/12/02			M
2017/05/25	2	Gestão Inteligente	A	1976/03/26			F
2017/05/25	3	Cruzeiro	T	1962/07/03			M
2017/05/25	4	Lohan	L	1986/07/02			F
2017/05/25	5	Fredricksen	F	1964/03/15			M
2017/05/25	6	Snyder	L	1968/07/05			F
2017/05/25	7	Lipnicki	J	1983/11/25			M
2017/05/25	8	Aspirador	S	2002/12/08			F
2017/05/25	9	Watson	E	1990/04/15			F

Procedimento armazenado.

Execute este script no TestDB para criar o procedimento armazenado:

USE [TestDB] GO /****** Objeto: StoredProcedure [dbo].[spAgeRange] Data do script: 2017/05/10 12:16:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spAgeRange] @PayrollDate varchar(50) AS BEGIN SET NOCOUNT ON; UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) WHERE tblStaff.PayrollDate = @PayrollDate Atualize tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate Update tblStaff set AgeRange = '46 to 55' onde Age >= 46 and Age < 56 e PayrollDate = PayrollDate Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate Update tblStaff set AgeRange = '18 a 24' onde Idade >= 18 e Idade < 25 e PayrollDate = @PayrollDate Update tblStaff set AgeRange = '<18' onde Idade < 18 e PayrollDate = @PayrollDate END

O procedimento armazenado será salvo em “Programabilidade” no banco de dados.

Excel VBA

Tudo o que resta é chamar o procedimento armazenado do Excel, fornecendo o Data da folha de pagamento parâmetro de “2017/05/25”. Você notará que eu simplesmente digitei dados  Data da folha de pagamento como uma string em vez de lutar com vários formatos de data. É bastante simples converter uma string em uma data usando o método Converter função se Data da folha de pagamento deve ser usado para fins aritméticos.

Crie uma nova pasta de trabalho. Abra a janela de código VBA e insira um módulo.

No menu Ferramentas da janela de código, faça referência ao Biblioteca Active X 2.nnrary para facilitar o uso de objetos de dados.

Cole o código a seguir na janela Código. Isso, uma vez ativado, se conectará a SQL Server, de acordo com o subprocedimento ConnectDatabase

'Todo "público" caso o código esteja espalhado por vários módulos. Public connDB como novo ADODB.Connection público rs como novo ADODB.Recordset público strSQL como string público strConnectionstring como string público strServer como string público strDBase como string público strUser como string público strPwd como string público PayrollDate como string Sub WriteStoredProcedure() PayrollDate = "2017 /05/25" Chamar ConnectDatabase em caso de erro GoTo errSP strSQL = "EXEC spAgeRange '" & PayrollDate & "'" connDB.Execute (strSQL) Exit Sub errSP: MsgBox Err.Description End Sub Sub ConnectDatabase() If connDB.State = 1 Then connDB.Close On Error GoTo ErrConnect strServer = "SERVERNAME" 'O nome ou endereço IP do SQL Server
     strDBase = "TestDB" strUser = "" 'deixe em branco para autenticação do Windows strPwd = "" Se strPwd > "" Then strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;" Else strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Autenticação do Windows End If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Exit Sub ErrConnect: MsgBox Err.Description End Sub

Adicione um botão à Sheet1 e atribua-o ao subprocedimento “Procedimento WriteStored"

Os resultados

Pressione o botão e examine tblStaff, que deve estar atualizado com idades e faixas etárias. O processamento ocorreu no lado do servidor.

Recuperando pastas de trabalho corrompidas

Se o Excel travar, pode levar sua única cópia da pasta de trabalho com ele. Em uma boa porcentagem do tempo, o Excel geralmente não consegue recuperar pastas de trabalho danificadas; nesse caso, todo o trabalho feito desde a criação da pasta de trabalho pode ser irrevogávelcabLiost, a menos que você tenha uma ferramenta para reparar Excel arquivos xlsx ou xlsm.

Introdução do autor:

Felix Hooker é um especialista em recuperação de dados em DataNumen, Inc., líder mundial em tecnologias de recuperação de dados, incluindo rar reparar e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com