O Excel pode fazer praticamente qualquer coisa; se deve ser feito para fazer tudo é outra questão. Embora a planilha seja muito poderosa na manipulação de dados, ela não é muito boa para armazenar dados normalizados. Aproveitando o Excel para um banco de dados relacional como SQL Server aumenta o poder do aplicativo.
Para começar, você precisará do MS Access ou de uma versão mais estável e gratuita – SQL Server Expressar. Supõe-se que o leitor tenha a faixa Excel Developer exibida e esteja familiarizado com o Editor VBA e a Linguagem de Consulta Estruturada (SQL). Este artigo usa SQL Server cadeias de conexão. Para MS-Access, consulte o Google.
Embora o Excel tenha suas próprias rotinas internas para obter informações de SQL Server em (digamos) uma tabela dinâmica, nosso exemplo dará mais flexibilidade na seleção de dados.
String de conexão
Estarei usando um banco de dados privado; insira suas próprias informações de driver no lugar das minhas na sub-rotina ConnectDatabase. Nós então usamos connDB como um canal de comunicação para nosso banco de dados – no meu caso, para retornar resultados de um procedimento armazenado. Você pode usar instruções SQL mais padrão, como “Selecionar * de…”
Ordem do Dia
Primeiro, carregaremos as opções da caixa de combinação de SQL Server Ao abrir a pasta de trabalho, utiliza-se uma macro Auto_open e o conteúdo é salvo na planilha “ComboData”. Independentemente de o servidor estar na nuvem ou localmente, não haverá atraso perceptível na inicialização do Excel, desde que o banco de dados esteja acessível a partir da estação de trabalho.
Em seguida, vamos extrair os dados filtrados do banco de dados e soltá-los no Excel, colunas F a K.
A interface
O meu tem caixas suspensas para filtrar informações do banco de dados. O Tipo caixa de combinação aciona uma pesquisa para preencher a tabela à direita.
Renomeie “Sheet1” como “Main”. Adicione pelo menos uma caixa de combinação.
O código
Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer
Sub auto_Open()
Call PopulateComboData 'kicks off the first process on Open
End Sub
Sub PopulateComboData()
Sheets("ComboData").Range("A3:C100").ClearContents
Call ConnectDatabase 'use the ConnectDatabase routine
strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
Set rs = connDB.Execute(strSQL)
ActiveSheet.Range("A3").CopyFromRecordset rs 'copies the recordset in bulk
End Sub
Sub ReadData()
intRole = Sheets("main").Range("D7")
Sheets("Main").Range("F4:L100").ClearContents
Call ConnectDatabase
strSQL = "EXEC DBTest " & intRole 'calls a stored proc with parameter
Set rs = connDB.Execute(strSQL)
ActiveSheet.Range("F4").CopyFromRecordset rs 'copies the recordset in bulk
End Sub
Sub ConnectDatabase()
On Error GoTo ErrConnect
If connDB.State = 1 Then connDB.Close 'closes connection if already open
strServer = "197.200.28.164"
strDBase = "Qcrew_sql"
strUser = "joesoap_sql"
strPWD = "frU6ra!@"
If strPWD > "" Then
strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
";Connection Timeout=30;"
Else 'Use windows authentication
strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
";Trusted_Connection=yes;DATABASE=" & strDBase
End If
connDB.Open strConnectionstring
Exit Sub
ErrConnect:
MsgBox Err.Description
End Sub
Formate o controle da caixa de combinação para ler as planilhas “ComboData”. Em seguida, clique com o botão direito do mouse na caixa de combinação para atribuir o subprocedimento ReadData a ela. Quando um item for selecionado no combobox, escreva sua chave na planilha “Principal”, célula D7. O código VBA usará essa chave como um filtro (consulte intRole, acima).
Referências à biblioteca dll
Na janela de código, utilize Ferramentas > Referências para referenciar a biblioteca Microsoft Active X Data Objects. Isso permitirá que o Excel utilize os objetos ADODB declarados no código.
A sub-rotina ReadData acima usa uma estrutura de dados relacional, mostrada abaixo, que é difícil de conseguir apenas no Excel.
Outras alterações de dados podem acionar um write-back no banco de dados, com a instrução SQL Update apropriada seguida por connDB.execute(strSQL).
Finalmente, proteja seu código de ser visualizado ou alterado: Ferramentas>Propriedades>Proteção.
Lidar com problemas do Excel:
De vez em quando, especialmente quando contém programas complexos, o Excel pode travar e não conseguir recuperar o arquivo corretamente. No caso de um danificado xlsx Ter um arquivo compactado e uma ferramenta de recuperação eficaz à mão resolverá a maioria dos problemas.
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 reparar rar erro e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com