Important:

Quaisquer soluções e/ou desenvolvimento de aplicações pessoais, ou da empresa, que não constem neste Blog podem ser tratados como consultoria freelance.

Views

VBA Excel - Usando o MS Excel como banco de dados


Sim, às vezes nos é solicitado desenvolver uma solução no MS Excel que devia ser desenvolvida no MS Access. Precisamos desenvolver um formulários para dataentry, um ambiente para o armazenamento dos dados.

Imaginemos termos uma planilha com dados de Fornecedor e outra planilha de Produto. Como faríamos para trazer os produtos de um determinado fornecedor?


Todo o processo precisaria ser feito via código, um loop varrendo os produtos e identificando o fornecedor e copiando o resultado para outro lugar. Ou através do uso de fórmula, que dependendo da massa de dados pode se tornar inviável.

 

E se fosse possível fazer um SELECT com JOINFicaria bem mais fácil certo?

 

Se seguíssemos um modelo de desenvolvimento padrão, o nosso código ficaria mais organizado. Precisamos de um segundo arquivo MS Excel pra ser o nosso banco de dados. Nada é perfeito, só é possível executar SELECT e INSERT. Os comandos de UPDATE e DELETE a gente improvisa.

 

Segue abaixo algumas funções que auxiliam no trabalho com o Excel como banco de dados. E certamente para outras versões do Excel devemos alterar a string de conexão.

 

Function ConectaXL() As Boolean

'*****************************************

'Nome: ConectaXL

'Autor: Rafael Gomes dos Santos

'Data: 04/05/2010

'Descrição: Conexão ADO com planilha Excel (só consulta)

'Revisão: 04/05/2010

'*****************************************

 

Let ConectaXL = True

 

On Error GoTo erro1:

 

With cn

    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlDB & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

    .Open

End With

 

erro1:

 

If Err.Number <> 0 Then

   

    Let ConectaXL = False

   

End If

 

End Function

 

 

Function DesconectaXL() As Boolean

 

'*****************************************

'Nome: DesconectaXL

'Autor: Rafael Gomes dos Santos

'Data: 04/05/2010

'Descrição: Desconecta ADO com planilha Excel

'Revisão: 04/05/2010

'*****************************************

 

On Error GoTo erro1:

 

    Let DesconectaXL = True

 

    cn.Close

   

    Set cn = Nothing

   

erro1:

 

If Err.Number <> 0 Then

 

    Let DesconectaXL = False

 

End If

   

End Function

 

 

Function ConectaXLAtualizavel() As Boolean

 

'*****************************************

'Nome: ConectaXLAtualizavel

'Autor: Rafael Gomes dos Santos

'Data: 04/05/2010

'Descrição: Conexão ADO com planilha Excel (Permite INSERT)

'Revisão: 04/05/2010

'*****************************************

 

Let ConectaXLAtualizavel = True

 

On Error GoTo erro1:

 

With cn

    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlDB & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"

    .Open

End With

 

erro1:

 

If Err.Number <> 0 Then

   

    ConectaXLAtualizavel = False

   

End If

 

End Function

 

 

Function ExcluiRegistro( _

Tabela As String, _

Campo As String, _

Valor As String _

) As Boolean

 

 

    '*****************************************
    'Nome: ExcluiRegistro
    'Autor: Rafael Gomes dos Santos
    'Data: 04/05/2010
    'Descrição: Exclui registro de tabela na planilha banco de dados
    'Revisão: 04/05/2010

    '*****************************************
 
 
    Dim xl As New Excel.Application
    Dim wkb As Workbook
    Dim wsh As Worksheet
   
    Dim c As Integer
    Dim l As Integer
   
    Let ExcluiRegistro = False
   
    Set wkb = xl.Workbooks.Open(xlDB)
 
    Set wsh = wkb.Worksheets(Tabela)
   
    Let c = 1
   
    Do While wsh.Cells(1, c) <> ""
 
        If wsh.Cells(1, c) = Campo Then
 
            Exit Do
       
        End If
 
        Let c = c + 1
 
    Loop
   
    If wsh.Cells(1, c) <> "" Then
   
        Let l = 2
       
        Do Until wsh.Cells(l, c) = ""
   
            If wsh.Cells(l, c) = Valor Then
           
                wsh.Cells(l, c).EntireRow.Delete
                Let ExcluiRegistro = True
                Exit Do
               
            End If
   
            Let l = l + 1
       
        Loop
       
    End If
 
    Set wsh = Nothing
    wkb.Close True
    Set wkb = Nothing
    xl.Quit
    Set xl = Nothing

 

End Function

 

 

Function RegistroExiste( _

Tabela As String, _

Campo As String, _

Valor As String, _

Optional Tipo As String, _

Optional Campo2 As String, _

Optional Valor2 As String, _

Optional Tipo2 As String, _

Optional Campo3 As String, _

Optional Valor3 As String, _

Optional Tipo3 As String _

) As Boolean

 

    '*****************************************

    'Nome: RegistroExiste

    'Autor: Rafael Gomes dos Santos

    'Data: 04/05/2010

    'Descrição: Retorna TRUE se o registro existir na planilha banco de dados. Limitado a 3 parâmetros.

    'Revisão: 04/05/2010

    '*****************************************

 

 

    Dim rs As New ADODB.Recordset

 

    Dim strSQL As String

 

    RegistroExiste = False

 

    If ConectaXLAtualizavel = False Then

       

        MsgBox "Impossível conectar"

        Exit Function

   

    End If

   

    rs.ActiveConnection = cn

   

    Let strSQL = "SELECT * FROM [" & Tabela & "$] WHERE "

   

    If Tipo = "Number" Then

        strSQL = strSQL & Campo & " = " & Valor

    Else

        strSQL = strSQL & Campo & " = '" & Valor & "'"

    End If

   

    If Campo2 <> "" Then

        If Tipo2 = "Number" Then

            strSQL = strSQL & " " & Campo2 & " = " & Valor2

        Else

            strSQL = strSQL & " " & Campo2 & " = '" & Valor2 & "'"

        End If

    End If

   

    If Campo3 <> "" Then

        If Tipo3 = "Number" Then

            strSQL = strSQL & " " & Campo3 & " = " & Valor3

        Else

            strSQL = strSQL & " " & Campo3 & " = '" & Valor3 & "'"

        End If

    End If

 

    rs.Source = strSQL

 

    rs.LockType = adLockPessimistic

    rs.Open

 

    If Not rs.EOF Then

       

        RegistroExiste = True

        

    End If

 

    If DesconectaXL = False Then

       

        MsgBox "Impossível desconectar"

        Exit Function

   

    End If

 

End Function

 

 

Fazendo um SELECT com JOIN na planilha Excel.

 

    If ConectaXL = False Then

       

        MsgBox "Impossível conectar"

        Exit Sub

   

    End If

   

    Let rs.ActiveConnection = cn

   

      Let strsql = "SELECT [Jurado$].Nome,"

Let strsql = strsql & " [Jurado$].Cargo,"

Let strsql = strsql & " [Jurado$].Empresa,

Let strsql = strsql & " [Jurado$].CargoJuri"

Let strsql = strsql & " FROM [Jurado$]"

Let strsql = strsql & " INNER JOIN [CargoJuri$]"

Let strsql = strsql & " ON [Jurado$].CargoJuri = [CargoJuri$].Cargo"

Let strsql = strsql & " WHERE [Jurado$].RegiaoJuri = 'LESTE/OESTE'"

Let strsql = strsql & " ORDER BY [CargoJuri$].Ordem"

 

Let rs.Source = strsql

Let rs.LockType = adLockPessimistic

   

      rs.Open

 

 

    If ConectaXLAtualizavel = False Then

        MsgBox "Impossível conectar"

        Exit Sub

    End If

   

    Let rs.ActiveConnection = cn

   

    Let rs.Source = "SELECT * FROM [Inscritos$] WHERE" _

    & " Categoria = '" & Me.cmbCategoria & "'" _

    & " AND Regiao = '" & Me.cmdRegiao & "'" _

    & " AND Posicao = " & Me.txtPosicao

   

    Let rs.LockType = adLockPessimistic

    rs.Open

   

    If Not rs.EOF Then

    

        rs.MoveFirst

   

        Let rs("Categoria") = Me.cmbCategoria

        Let rs("Regiao") = Me.cmdRegiao

        Let rs("Duracao") = Me.txtDuracao

        Let rs("Posicao") = Me.txtPosicao

        Let rs("Titulo") = Me.txtTitulo

   

        rs.Update

        rs.Close

        Set rs = Nothing

   

    End If

 

    If DesconectaXL = False Then

        MsgBox "Não foi possível se desconectar do Banco de Dados. por favor reinicie o sistema."

        Exit Sub

    End If


Referência: SistemaEmVBA.com

Deixe os seus comentários! Envie este artigo, divulgue este link na sua rede social...

Tags
VBA, Excel, Icon, ícones, Conditional, Formatting, 


Access VBA Advanced - Usando o Poder do VBScript e do WMI - Using the Power of VBScripts and WMI in Microsoft Access VBA

Inline image 1

Muitos programadores da suíte MS Office, sequer imaginam que podem explorar as poderosas capacidades de Scripting e WMI (Windows Management Instrumentation - instrumentação de gerenciamento do Windows).

WMI (instrumentação de gerenciamento do Windows) é a implementação da Microsoft do WBEM, uma iniciativa da indústria que visa estabelecer padrões para acessar e compartilhar informações de gerenciamento por meio de uma rede empresarial. O WMI é compatível com WBEM e fornece suporte integrado ao modelo CIM (modelo de informação comum), o modelo de dados que descreve os objetos existentes em um ambiente de gerenciamento.

WMI inclui um repositório do objeto compatível com o CIM, que é o banco de dados das definições do objeto, e ogerenciador de objetos CIM, que lida com a coleta e a manipulação dos objetos no repositório e reúne as informações dos provedores WMI. Esses provedores agem como intermediários entre o WMI e os componentes do sistema operacional, aplicativos e outros sistemas. Por exemplo, o provedor do Registro extrai as informações do Registro, enquanto o provedorSNMP fornece os dados e eventos dos dispositivos SNMP. Os provedores dão informações sobre seus componentes e podem fornecer métodos para manipular os componentes, as propriedades que podem ser definidas ou os eventos que podem alertá-lo sobre as alterações nos componentes.

WMI pode ser usado pelas ferramentas de gerenciamento do computador, como o Microsoft Systems Management Server, para ajudá-lo a gerenciar seus computadores. Ele também é usado por outras tecnologias e ferramentas daMicrosoft, como o Microsoft Health Monitor e o Microsoft Operations Manager, e por outros fornecedores de sistemas de gerenciamento dos computadores. Você pode usar o WMI com sistemas de programação ou de scripts (como o host de scripts do Windows) para recuperar detalhes de configuração da maioria dos aspectos dos sistemas de computador, incluindo aplicativos de servidor, ou para gerar alterações nos sistemas.


De fato, é emocionante descobrir que é fácil convertermos VBScript WMI em código VBA deixando-os tão funcionais quanto os scripts.

Embora haja uma série de funções VBSript alguns não podem ser usados ​​no VBA, mas a maioria dos scripts pode ser facilmente modificado para funcionar a partir do MS Access ou outros aplicativos do Office.

Por que ficar animado?

Com VBA e WMI podemos fazer muitas coisas que não são possíveis usando o Windows API. O WMI é tão poderoso que é preciso ter cuidado para não fazermos alguns estragos sérios nos computadores sob a nossa responsabilidade quer localmente, quer num PC remoto ou mesmo numServidor Windows!

Por que o WMI é tão poderoso?

"O WMI é a principal tecnologia de gestão de habilitação embutido nos família de sistemas operacionais Windows 2000Windows XP eWindows Server 2003. Estão baseados em padrões supervisionados peloDistributed Management Task Force (DMTF), o WMI é a ferramenta e o encanamento através do qual todos, ou  quase todos, os recursos doWindows podem ser acessados, configurados, gerenciados e monitorados. "

A última frase acima estabelece o tamanho do poder de WMI, o qual você, sim você, tem na ponta dos dedos através do VBA.

Segue uma lista das coisas que podemos fazer localmente ou através de acesso remoto:

Gerenciar usuários e Políticas de Grupo
(Manage Users and Group Policies)

Ler e salvar logs de eventos em arquivos
(Read and save Event Logs to files)

Obter o tamanho de memória livre de um disco rígido

(Get the size of a hard drive's free Memory)


Obter a versão do Microsoft Office
(Get the Version of Microsoft Office)

Obter os atributos das Impressoras
(Get the attributes of Printers)

Obter a versão do OSSP
(Get the Operating System Service Pack Version (CSDVersion–ServicePackLast))

Obter o nome do SO
(Get the Operating System Name)

Obter a versão do SO
(Get the Operating System Version Number)

Obter a versão do compilação do SO
(Get the Operating System Build Number)

Obter o número de série do produto Windows OS
(Windows Operating System Product/Serial Number)

Ter ciência dos Jobs agendadas
(Get Scheduled Jobs)

Conte processos em execução
(Count Running Processes)

Lista dos processos em execução
(List Running Processes)

Encerrar processos
(Terminate Processes)

Deletar softwares

Instalar softwares

Atualizar software

Lista de todos os software Windows instalado
(List all Windows installed software)

Lista todas as IDs dos produtos Microsoft instalados
(List Microsoft software Product IDs)

Retorna o nome do Usuário Registado
Get the Registered User

Nem tudo são flores

A maior dificuldade com o uso de WMI é que existem milhares de propriedades e métodos que estão disponíveis para acessarmos. O WMI tem classes, propriedades e métodos e um dialeto próprio de query VBScriptchamado WQL.


WMI usa o Common Information Model (CIM) para acessar informações e executar ações. O CIM nos dá acesso a quase tudo sobre um PC. Podemos aprender mais sobre o CIM aqui.


Finalmente, o código:


Para que você não morra de curiosidade, segue um exemplo de código VBAusando WMI

Este código obtém os IDs dos Processores de um computador local, o que pode ser muito útil. 



Este código pode ser usado como um template de como acessar e usar grande parte da informação que queremos usar com o WMI.


Function ProcessorIDs() As String

    ' Usando late binding para evitar um problema de referência - late binding

    Dim strComputer As String

    Dim objWMIService As Object
    Dim colItems As Object
    Dim objItem As Object
    Dim strProcessorIDs As String

    Let strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")

    Set colItems = objWMIService.ExecQuery( _
        "SELECT * FROM Win32_Processor", , 48)
        
    For Each objItem In colItems
        Let strProcessorIDs = strProcessorIDs & ";" & Nz(objItem.ProcessorID, "")
    Next
    
    ' Remove the leading ; from the string  
    Do While Left(strProcessorIDs, 1) = ";"
        Let strProcessorIDs = Right(strProcessorIDs, (Len(strProcessorIDs) - 1))
    Loop
   
    Let ProcessorIDs = strProcessorIDs

    Set objItem = Nothing
    Set colItems = Nothing
    Set objWMIService = Nothing
End Function

Como funciona o código?

Após declararmos algumas variáveis, criamos uma instância do objeto WMIusando GetObject ("winmgmts:")

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _
    & strComputer & "\root\cimv2")

Usamos winmgmts, o CIM Object Manager, para acessar o "root" ("\ root \ cimv2") da biblioteca CIM. Se estiver acessando um computador remoto "{ImpersonationLevel = personificar}! \ \" Concede-nos o acesso se tivermos as credenciais de segurança adequadas.

A variável strComputer passa um valor de "." Quando estamos acessando o computador local. Se quiser acessar um computador remoto, em vez de usar nós, precisamos usar o caminho e o nome do computador remoto ".":GetObject ("winmgmts: \ TargetComputer") ou GetObject ("winmgmts: \ DomainName TargetComputer \").

Em seguida, instanciamos um conjunto de propriedades da classeWin32_Processor WMI.

Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_Processor", , 48)

Nós usamos uma consulta WQL para selecionar todas as propriedades da classe. E podemos aprender mais sobre WQL aqui.

Finalmente temos o 
número de série de identificação do processador numastring.

For Each objItem In colItems
    ProcessorID = Nz (objItem.ProcessorID, 0)
Next

Termos os ProcessorIDs pode nos ajudar a ter algum nível de segurança, permitindo-nos saber se o banco de dados foi copiado para um computador diferente. Se já salvamos pelo menos um dos ProcessorIDs num banco de dados personalizado, podemos verificar se o valor foi alterado usando o código a seguir, que pode ser chamado por uma Macro AutoExec ou num formulário Splash. Atenção: Faça backup de seus arquivos de banco de dados antes de usar o seguinte código.

Function StartSafe() As Boolean
    Dim strProcessorID As String

    ' Get the stored ProcessorIDs from
    ' the custom Database property
    strProcessorID = GetDBPropValue("dbPrpProcessorID")

    If InStr(1, ProcessorIDs, strProcessorID) > 0 Then
        StartSafe= True
    Else
        StartSafe= False
        ' The database has been copied to an unauthorized computer!
        ' Delete the Back End Database to protect your data
        ' Uncomment the next line of code to delete the back end database
        'Kill (CurrentProject.Path & "\BackEndDB.mdb")
        ' If this is the back end add code to Delete all tables
        ' Add code to Delete all Modules
        DoCmd.Quit
    End If
End Function

Divirtam-se!


Seguem alguns livros para ampliarmos a visão sobre WMI






Deixe os seus comentários! Envie este artigo, divulgue este link na sua rede social...

Tags
VBA, Access, VBScripts, WMI, Microsoft Access, scripting, advanced, WMI, vbscript, Windows API, WQL, 


LinkWithinBrazilVBAAccessSpecialist

Related Posts Plugin for WordPress, Blogger...

Vitrine