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:

Procedimento armazenado.

Execute este script no TestDB para criar o procedimento armazenado:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 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
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and 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 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and 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.nn 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

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error 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" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If 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 'Windows authentication
     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, ele pode levar consigo a sua única cópia da planilha. Na maioria das vezes, o Excel não consegue recuperar planilhas danificadas; nesse caso, todo o trabalho realizado desde a criação da planilha pode ser perdido irremediavelmente, 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 reparo rar e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com