VBA Excel - Ordena worksheets da planilha - Sort All Sheets in a Workbook

Inline image 1

Podemos ordenar todas as Sheets duma seqüência alfabética.

Sub SrtShs()
Dim iSheet As Long, iBefore As Long 
For iSheet = 1 To ActiveWorkbook.Sheets.Count     
    Let Sheets(iSheet).Visible = True 
    For iBefore = 1 To iSheet - 1       
        If UCase(Sheets(iBefore).Name) & UCase(Sheets(iSheet).Name) Then  
                      ActiveWorkbook.Sheets(iSheet).Move 
          Before:=ActiveWorkbook.Sheets(iBefore)         
          Exit For       
        End If     
    Next iBefore   
Next iSheet 
End Sub







Reference:

Inspiration:

TagsVBA, Excel, wrap, sheet, sheets, ws, insert, sort, ordena


VBA Excel - Loop por todas as worksheets

Sim, ter a capacidade de passar por todas as planilhas do Workbook é simples, mas necessário.

Talvez precise proteger, configurar, encriptar, estabelecer privilégios ou mesmo atualizar tabelas em todas as Sheets. Essa SUB lhe permitirá isso:

  Sub SheetsWrap()
    Dim iSheet as Long

    Let Application.ScreenUpdating = False

    For iSheet = 1 To ActiveWorkbook.WorkSheets.Count

          Let WorkSheets(iSheet).cells(1,1) = "'" & WorkSheets(iSheet).name

    Next iSheet

    Let Application.ScreenUpdating = True
  End Sub

Reference:

Inspiration:

TagsVBA, Excel, loop, laço, wrap, sheet, sheets, ws


VBA Access - Manipulando importações salvas

Inline image 1

Para nos beneficiarmos da rapidez do MS Office 2007, podemos [e devemos] gravar as importações que efetuamos 'na mão'. E posteriormente quando precisarmos dessas, podemos evocá-las das entranhas do MS Access.


Using a Saved Import in Access 2007 via a macro or VBA
Saved Import Access 2007

Não envergonhece-se de utilizar o comando banal, mas útil, abaixo:

DoCmd.RunSavedImportExport "imp_bernardes_Invoices"


E que tal ampliar os seus conhecimentos sobre as entranhas do MS Access, fuçando em:

Office button

Access Options

Current Database

Navigation Options

Show System Objects

É interessante ver que o MS Access grava internamente um nome diferente do que utilizamos externamente.

Reference:


TagsVBA, Access, import, export, automation, automação, importação, exportação, 

VBA Access - Comparação de tipos de dados.

Inline image 1

Usar o tipo certo de definição de dados é importantíssimo, nada melhor do que detectar o tipo antes de desenvolver nossas soluções. Seguem referências da própria Microsoft:

Comparação de tipos de dados
O mecanismo de banco de dados do MS Access reconhece diversos conjuntos de tipos de dados que se sobrepõem. No MS Access, existem quatro contextos diferentes nos quais talvez seja necessário especificar um tipo de dados: no modo de design da tabela (modo Design: uma janela que mostra o design destes objetos de banco de dados: tabelas, consultas, formulários, relatórios, macros e páginas de acesso a dados. No modo Design, você pode criar novos objetos de banco de dados e modificar o design dos existentes.), na caixa de diálogo Parâmetros da Consulta, no Visual Basic e no modo SQL em uma consulta.

A tabela a seguir compara os cinco conjuntos de tipos de dados correspondentes a cada contexto. A primeira coluna lista as configurações da propriedade Type disponíveis no modo de design da tabela e as cinco configurações da propriedade FieldSize para o tipo de dados Número (tipo de dados Número: em um banco de dados do MS Access, um tipo de dados de campo para dados numéricos que serão usados em cálculos matemáticos. 

Use o tipo de dados Moeda, entretanto, para exibir valores de unidade monetária.). A segunda coluna lista os tipos de dados de parâmetro de consulta disponíveis para estruturar consultas parâmetro (consulta parâmetro: uma consulta na qual um usuário especifica de maneira interativa um ou mais valores de critérios. Uma consulta parâmetro não é um tipo separado de consulta; na verdade, aumenta a flexibilidade de uma consulta.) na caixa de diálogo Parâmetros da consulta

A terceira coluna lista os tipos de dados correspondentes do Visual Basic. A quarta coluna lista tipos de dados do objeto Field (DAO). A quinta coluna lista os tipos de dados SQL do mecanismo de banco de dados do  MS Access  correspondentes definidos por esse mecanismo, juntamente com os seus sinônimos válidos.
Campos de tabela Parâmetros da consultaVisual BasicConstantes da propriedade Tipo de dados ADO SQL do mecanismo de banco de dados do Microsoft Access e sinônimos
Sem suporteBinárioSem suporte adBinaryBINARY (consulte Observações) (Sinônimo: VARBINARY)
Sim/NãoSim/NãoBoolean (Booleano: uma expressão que pode ser avaliada como verdadeira (diferente de zero) ou falsa (0). Você pode usar as palavras-chave Verdadeiro e Falso para fornecer os valores de -1 e 0, respectivamente. O tipo de dados de campo Sim/Não é booleano e tem o valor de -1 para Sim.) adBooleanBOOLEAN (Sinônimos: BIT, LOGICAL, LOGICAL1, YESNO)
Número (Tamanho do campo = Byte) ByteByte (tipo de dados Byte: um tipo de dados do banco de dados do Access usado para armazenar números inteiros positivos pequenos no intervalo de 0 a 255.) adUnsignedTinyIntBYTE (Sinônimo: INTEGER1)
AutoNumeração (Tamanho do campo=
Inteiro longo)
Inteiro longoLong (tipo de dados Longo: um tipo de dados básico que contém inteiros grandes. A variável Long é armazenada como um número de 32 bits no intervalo de - 2.147.483.648 a 2.147.483.647.) adIntegerCOUNTER (Sinônimo: AUTOINCREMENT)
Moeda MoedaCurrency (tipo de dados Unidade Monetária: em um banco de dados do Microsoft Access, um tipo de dados útil para cálculos que envolvam moeda ou para cálculos de ponto fixo nos quais a precisão é extremamente importante.) adCurrencyCURRENCY (Sinônimo: MONEY)
Data/hora Data/horaDate (tipo de dados Data/Hora: tipo de dados do banco de dados do Access usado para armazenar informações de data e hora.) adDateDATETIME (Sinônimos: DATE, TIME, TIMESTAMP)
Número (Tamanho do campo = Duplo) DuploDouble (tipo de dados Duplo: um tipo de dados básico que contém números de ponto flutuante de dupla precisão. É armazenado como um número de 64 bits e seu intervalo de valores vai de aproximadamente -1,797E308 a -4,940E-324 (negativo), de 4,94E-324 a 1,797E308 (positivo) e 0.) adDoubleDOUBLE (Sinônimos: FLOAT, FLOAT8, IEEEDOUBLE, NUMBER, NUMERIC)
AutoNumeração/GUID
(Tamanho do campo= Código de replicação)
Código de replicaçãoSem suporteadGUID GUID
Número (Tamanho do campo = Inteiro longo) Inteiro longoLongadIntegerLONG (Consulte Observações) (Sinônimos: INT, INTEGER, INTEGER4)
Objeto OLEObjeto OLEString (tipo de dados Seqüência: um tipo de dados básico que contém caracteres, um caractere por 2 bytes. Uma seqüência de comprimento fixo pode conter de 1 a 64 K de caracteres; uma seqüência de comprimento variável pode conter de 1 a 2 bilhões.) adLongVarBinaryLONGBINARY (Sinônimos: GENERAL, OLEOBJECT)
Memo MemoStringadLongVarWCharLONGTEXT (Sinônimos: LONGCHAR, MEMO, NOTE)
Número (Tamanho do campo = Simples) SimplesSingle (tipo de dados Simples: um tipo de dados que armazena variáveis de ponto flutuante de dupla precisão como 32 bits (4 bytes) no intervalo de -3,402823E38 a -1,01298E-45 para valores negativos e 1,401298E-45 a 3,402823E38 para valores positivos.) adSingleSINGLE (Sinônimos: FLOAT4, IEEESINGLE, REAL)
Número (Tamanho do campo = Inteiro) InteiroInteger (tipo de dados Inteiro: um tipo de dados fundamental que contém números inteiros. Uma variável Integer é armazenada como um número de 16 bits (2 bytes) no intervalo de -32.768 a 32.767.) adSmallIntSHORT (consulte Observações) (Sinônimos: INTEGER2, SMALLINT)
Texto Texto StringadVarWCharTEXT (Sinônimos: ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR)
HiperlinkMemoStringadLongVarWChar LONGTEXT (Sinônimos: LONGCHAR, MEMO, NOTE)
Sem suporteValorVariant (tipo de dados Variante: o tipo de dados padrão para variáveis que não têm caracteres de declaração de tipo quando uma instrução Deftipo não está sendo usada. Um Variante pode armazenar dados numéricos, seqüências de caracteres, data/hora, valores Nulo ou dados Vazio.) adVariantVALUE (consulte Observações)
  Observações
    • MS Access propriamente não utiliza o tipo de dados BINARY. Esse tipo é reconhecido somente para utilização em consultas a tabelas vinculadas provenientes de outros produtos de banco de dados que oferecem suporte ao tipo de dados BINARY.

    • O tipo de dados INTEGER no SQL do mecanismo de banco de dados do MS Access não corresponde ao tipo de dados Integer para campos de tabela, parâmetros de consulta ou Visual Basic. Em vez disso, no SQL, o tipo de dados INTEGER corresponde a um tipo de dados Long Integer para campos de tabela e parâmetros de consulta e a um tipo de dados Long no Visual Basic.

    • A palavra reservada VALUE não representa um tipo de dados definido pelo mecanismo de banco de dados do Access. Entretanto, em consultas do Microsoft Access ou do SQL, a palavra reservada VALUE pode ser considerada um sinônimo válido para o tipo de dados Variant do Visual Basic.

    • Se você estiver definindo o tipo de dados para um objeto DAO (DAO (Objetos de Acesso a Dados): uma interface de acesso a dados que se comunica com o Microsoft Jet e com fontes de dados compatíveis com ODBC para se conectar, recuperar, manipular e atualizar dados e a estrutura do banco de dados.) em código do Visual Basic, terá que configurar a propriedade Type do objeto.



Reference:


TagsVBA, Access, SQL, Visual Basic, DAO, Type, Tipo, Variables, data, dados, ODBC, 

VBA Access - Excluindo fontes de dados conectadas.

Inline image 1


Quando criamos aplicações de automação com o MS Access, não raro precisarmos implementar conexões nas diversas bases de dados como: planilhas MS Excel, arquivos Texto, ou outras tabelas MS Access, sites da Web, etc... Essas conexões geralmente precisam ser refeitas e/ou excluídas.

Abaixo segue parte de códigos com variantes para aplicar ao seu gosto...Boa diversão!

Esta primeira função é muito rápida, ela checa se o objeto que desejamos excluir está disponível para deleção:


CÓDIGO: 

Function CheckExistTbl(tblName As String) As Integer
    ' Author:                     Date:               Contact:                 URL:
    ' André Bernardes             09/11/2010 09:45   bernardess@gmail.com     https://sites.google.com/site/vbabernardes/
    ' Application: 
    ' Detecta a tabela e a deleta.

    Dim i As Integer ' Counter.
    
    Let CheckExistTbl = False

    For i = 0 To CurrentData.AllTables.Count - 1

        If CurrentData.AllTables(i).Name = tblName Then
            Let CheckExistTbl = True
        End If
    
    Next i
End Function

Por exemplo: 


CÓDIGO: 

If CheckExistTbl(strConectionTbl01) Then
   DoCmd.DeleteObject acTable, strConectionnTbl01
Endif


Abaixo demonstro uma das inúmeras técnicas para se conectar dados à sua aplicação MS Access, neste caso efetuo conexões a outras bases MS Access.


CÓDIGO: 

Function ConectAll(nBase As String, strConection As String)
    ' Author:                     Date:               Contact:                 URL:
    ' André Bernardes             09/11/2010 09:31   bernardess@gmail.com     https://sites.google.com/site/vbabernardes/
    ' Application: 
    ' Efetua as conexões.

    Dim dbsTemp As Database
    Dim strMenu As String
    Dim strInput As String
    Dim nTbl01 As String
    Dim nTbl02 As String
    Dim nTbl03 As String

    ' Tabelas
    nTbl01 = "tbl_01x"
    nTbl02 = "tbl_02y"
    nTbl03 = "tbl_03k"

    Set dbsTemp = CurrentDb

    ' Deleta os objetos pré-existentes.
    If CheckExistTbl(strConection & nTbl01) Then
        Call Banner("Desconectando tabela:" & strConection & nTbl01)
        DoCmd.DeleteObject acTable, strConection & nTbl01

        Call Banner("Desconectando tabela:" & strConection & nTbl02)
        DoCmd.DeleteObject acTable, strConection & nTbl02

        Call Banner("Desconectando tabela:" & strConection & nTbl03)
        DoCmd.DeleteObject acTable, strConection & nTbl03
    End If

    ' Conecta o grupo de tabelas respectivas ao mês de análise.
    Call Banner("Conectando a tabela: " & strConection & nTbl01)
    ConnectOutput dbsTemp, strConection & nTbl01, ";DATABASE=" & nBase, nTbl01

    Call Banner("Conectando a tabela: " & strConection & nTbl02)
    ConnectOutput dbsTemp, strConection & nTbl02, ";DATABASE=" & nBase, nTbl02

    Call Banner("Conectando a tabela: " & strConection & nTbl03)
    ConnectOutput dbsTemp, strConection & nTbl03, ";DATABASE=" & nBase, nTbl03
End Function

 Perceba no código acima a utilização das funções explanadas anteriormente.

Abaixo observaremos a simples e suave conexão da fonte de dados com o banco de dados atual:

CÓDIGO: 

Sub ConnectOutput(dbsTmp As Database, strTbl As String, strConnect As String, strSourceTbl As String)
    ' Author:                     Date:               Contact:                 URL:
    ' André Bernardes             09/11/2010 08:01   bernardess@gmail.com     https://sites.google.com/site/vbabernardes/
    ' Application:
    ' Efetua as conexões.

    Dim tblLinked As TableDef

    Set tblLinked = dbsTmp.CreateTableDef(strTbl)

    Let tblLinked.Connect = strConnect
    Let tblLinked.SourceTableName = strSourceTbl

    dbsTmp.TableDefs.Append tblLinked
End Sub


Outra técnica também eficiente, seria utilizar o código abaixo:

CÓDIGO: 

    Dim d_b As Database

    On Error GoTo ProcessingErrorMsg:

    Set d_b = CurrentDb()

    d_b.TableDefs.Delete "tbl_Bernardes"

    Exit Sub

ProcessingErrorMsg:

    Select Case Err.Number
             Case 3265 'Table
                    Resume Next
             Case Else
                    MsgBox Err.Number & " - Descrição: " & Err.Description, vbExclamation, Err.Source
    End Select
Reference:


TagsVBA, Access, datasource, data, dados, excluir,  object

VBA Access - Documentando os objetos da aplicação

VBA Access - Documentando Objetos da Aplicação MS Access - Code Documenter


ACESSE AO CÓDIGO ATUALIZADO PARA TODAS AS VERSÕES AQUI.



Envie seus comentários e sugestões e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com

✔ Brazil SFE®✔ Brazil SFE®´s Facebook´s Profile  Google+   Author´s Professional Profile  ✔ Brazil SFE®´s Pinterest       ✔ Brazil SFE®´s Tweets

VBA Access - Ordenando a propriedade de um RecordSet.

Inline image 1

Os Recordsets são um dos objetos mais utilizados quando manipulamos dados. Esta dica simples, mas útil, é importante conceitualmente. Procure acrescentar sua experiência a este código e depois compartilhe os seus resultados conosco, postado aí embaixo.

De vez em quando é necessário que re-organizemos o modo como o nosso Recordset será ordenado, isso após termos definido a string SQL.

Bem, isso pode ser implementado mesmo após este momento como poderá ver abaixo:


Sub SortRecordset()
    Dim intCounter As Integer
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset

    Letrst.ActiveConnection = CurrentProject.Connection
    Let rst.CursorLocation = adUseClient

    rst.Open "Select * from tbl_Bernardes"

    Debug.Print "Sem ordenação."

    Do Until rst.EOF
        Debug.Print rst("BernardesID")

        rst.MoveNext
    Loop

    Debug.Print "Ordenado."

    Let rst.Sort = "[BernardesID]"

    Do Until rst.EOF
        Debug.Print rst("BernardesID")

        rst.MoveNext
    Loop

    rst.Close

    Set rst = Nothing
End Sub


Reference:

TagsVBA, Access, order, property, sprt, recordset, ADODB,

VBA Outlook - Envie todas as suas mensagens por e-mails como Bcc, automaticamente.

Inline image 2

O MS Outlook tem uma regra quando enviamos mensagens para outra pessoa como Cc, mas não há nada equivalente para mensagens enviadas como BCC (ou CCo, Oculta).

Utilizaremos o evento Application.ItemSend, que será acionado quando um usuário for enviar uma mensagem.

A versão ideal para se fazer isso é a partir do MS Outlook 2003. Ela usa objetos exclusivamente Outlook e inclui manipulação de erro para evitar problemas com um endereço inválido Bcc.

Ela usa objetos exclusivamente MS Outlook e inclui manipulação de erro para evitar problemas com um endereço inválido Bcc

Coloque esse código VBA no módulo interno de ThisOutlookSession:


1ª Versão:

Private Sub Application_ItemSend (ByVal Item As Object, Cancel As Boolean)
    Dim objRecip As Recipient
    Dim strMsg As String
    Dim res As Integer
    Dim strBcc As String
    On Error Resume Next

    strBcc = "bernardess@gmail.com"

    Set objRecip = Item.Recipients.Add(strBcc)
    objRecip.Type = olBCC

    If Not objRecip.Resolve Then
        strMsg = "Não posso enviar esta mensagem oculta. " & _
                 "Deseja continuar enviando a mensagem?"

        res = MsgBox(strMsg, vbYesNo + vbDefaultButton1, _
                "Não posso enviar esta mensagem oculta.")

        If res = vbNo Then
            Cancel = True
        End If
    End If

    Set objRecip = Nothing
End Sub

A razão pela qual este método não é adequado para as versões anteriores do MS Outlook 2003 é porque ele dispara um alerta de segurança devido ao uso do Recipients.Add

Você pode evitar avisos de segurança, simplesmente definindo a propriedade Item.Bcc para o endereço desejado, mas terá dois problemas. Primeiro, iria retirar os destinatários Bcc que o usuário já tivesse adicionado. Além disso, em algumas configurações do MS Outlook, mesmo se você usar um endereço SMTP apropriado, obteria um erro, e o  MS Outlook  não enviaria a mensagem.


2ª Versão:

Esta versão utiliza a mesma técnica básica da 1ª, apenas adiciona a biblioteca de terceiros Outlook Redemption para evitar avisos de segurança das versões anteriores ao MS Outlook 2003 e, caso o beneficiário não possa ser resolvido, para mostrar ao usuário uma caixa de diálogo de resolução dos nomes.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    ' Requires a reference to
    ' the SafeOutlook library (Redemption.dll)

    Dim objMe As Redemption.SafeRecipient
    Dim sMail As Redemption.SafeMailItem

    On Error Resume Next
    
    Set sMail = CreateObject("Redemption.SafeMailItem")

    Item.Save

    sMail.Item = Item
    Set objMe = sMail.Recipients.Add ("bernardess@gmail.com")
    objMe.Type = olBCC

    If Not objMe.Resolve(True) Then
        Cancel = True
    End If
    
    Set objMe = Nothing
    Set sMail = Nothing
End Sub

Reference:
TagsVBA, Outlook, Outlook 2003, send, message, mensagem, Bcc, ThisOutlookSession, automation
diHITT - Notícias