Blog do Kazu - Dicas rápidas de Office

sábado, 15 de julho de 2023

Importação de arquivos csv em lote - Excel VBA

Sub importa_texto_nome()
On Error GoTo erro

    Dim Pasta As String
    Dim Arquivo As String
    Dim linha As Double
    Dim registro As String
    Dim vetor() As String
    
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Selecione a pasta"
        .Show
        If .SelectedItems.Count = 0 Then
            Exit Sub
        Else
            Pasta = .SelectedItems(1) & "\"
        End If
    End With
 
    Range("A:AA").Clear
   
    Application.Cursor = xlWait
    
    Application.ScreenUpdating = False
    
   
    linha = 1
    Arquivo = Dir(Pasta & Arquivo)
    
    Do While Arquivo <> ""
        Open Pasta & Arquivo For Input As #1
        DoEvents
        
            Do Until EOF(1)
                DoEvents
                Line Input #1, registro
                vetor() = Split(registro, ";") 'Ajustar a quantidade de colunas de acordo com a necessidade

                Cells(linha, 1) = vetor(0)
                Cells(linha, 2) = vetor(1)
                If linha = 1 Then
                    Cells(linha, 3) = "Arquivo"
                Else
                    Cells(linha, 3) = Arquivo
                End If
                linha = linha + 1
            Loop
        Close #1
        Arquivo = Dir
        
    Loop
    
erro:
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    If Err.Number = 0 Then
        MsgBox "A importação dos arquivos de texto foi concluída."
    Else
        MsgBox Err.Description, vbCritical
    End If
    Exit Sub
    

End Sub

quarta-feira, 26 de abril de 2023

Outlook VBA - capturar o link de uma imagem no corpo do e-mail

Deixando registrado aqui o código de uma necessidade pontual.

;-)

Sub CapturarAtributoTag()
Dim objMail As Outlook.MailItem
Set objMail = Application.ActiveExplorer.Selection(1)

Dim objHTMLDoc As MSHTML.HTMLDocument
Set objHTMLDoc = New MSHTML.HTMLDocument

objHTMLDoc.body.innerHTML = objMail.HTMLBody

Dim objImg As MSHTML.HTMLImg
Set objImg = objHTMLDoc.getElementsByTagName("img")(0)

MsgBox objImg.getAttribute("src")
End Sub

sexta-feira, 3 de março de 2023

Capturar a assinatura do Outlook pelo VBA

Naõ tenho certeza se este código ainda funciona, mas vou deixar aqui para registro antes de apagar dos meus backups.

;-)

Option Explicit

Dim assinatura As Variant

Public Function pega_assinatura(ByVal sFile As String) As String

'Dick Kusleika

Dim FSO As Object

Dim ts As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

Set ts = FSO.GetFile(sFile).OpenAsTextStream(1, -2)

pega_assinatura = ts.ReadAll

ts.Close

End Function

Sub Cria_mensagem_HTML()

'Creates a new e-mail item and modifies its properties.

Dim olApp As Outlook.Application

Dim objMail As MailItem

Set olApp = Outlook.Application

'Create mail item

Set objMail = olApp.CreateItem(olMailItem)

assinatura = pega_assinatura("C:\Documents and Settings\" & Environ("username") & "\AppData\Roaming\Microsoft\Assinaturas\Paulo.htm")

With objMail

'Set body format to HTML

'a tag <br/> quebra linha

'a tag <strong></strong> formata o texto para negrito

.BodyFormat = olFormatHTML

.HTMLBody = "<HTML><H2>The body of this message will appear in HTML.</H2><BODY>Type the " & _

"<strong>message</strong><br/>Nova Linha</BODY><br/></HTML>" & assinatura

.Display

End With

End Sub

quarta-feira, 8 de fevereiro de 2023

Dicas para não perder código VBA

Segue aí uma dica para evitar perda de códigos VBA, que pode ocorrer se o personal.xlsb corromper ou mesmo se der problema no próprio computador.

Um dia o meu personal.xlsb corrompeu e eu perdi vários códigos porque não conseguia acessá-los mais e também não foi possível recuperar o arquivo, desde então parti para duas soluções:

1 - fazer backup do personal.xlsb periodicamente;

2 - exportar os módulos um a um em arquivos separados. Isso facilita também quando quero levar os códigos para outro computador, bastando apenas importá-los dentro do VBA.

sexta-feira, 18 de novembro de 2022

Tabela de feriados nacionais até 2078 - formato xlsx.

Deixando registrado aqui para futuras consultas...

https://www.anbima.com.br/feriados/feriados.asp

;-)

quinta-feira, 26 de agosto de 2021

Criar documentos a partir do Excel

Olá!

Quando for criar um documento a partir do Excel (um contrato, nota promissória, recibos, etc.) utilize o recurso de automação do Office e aproveite as funcionalidades do Word que é o programa adequado para tratar textos.

Crie um documento modelo no Word e utilize indicadores (bookmarks) para inserir os textos no documento.

Imagine um contrato de várias páginas onde, no início existem campos que precisam ser preenchidos, na sequência constam as cláusulas e no final o nome dos envolvidos.

Dá para fazer no Excel? Dá, mas imagine como fazer o alinhamento das margens, controle do fluxo do texto nas páginas, caso alguns campos sejam variáveis em quantidade (uma lista de produtos por exemplo no meio do contrato), numeração de página, etc.

Aproveite o potencial de cada programa para fazer um trabalho adequado e com qualidade.

Abaixo um exemplo bem simples para ilustrar melhor o recurso.

Importante: não esqueça de marcar o Microsoft Word nas referências do VBA.

;-)

sábado, 14 de agosto de 2021

Cuidados ao trabalhar com arquivos csv

O formato csv (Comma Separated Values) são comumente utilizados como arquivos de troca entre sistemas e muitas vezes precisamos visualizar o conteúdo para fins de conferência.

Apesar do Windows atribuir o Excel como aplicativo padrão para abertura desses arquivos, é importante saber que o formato csv não é nativo do Excel, apesar deste permitir a abertura sem problema algum.

Ao abrir o arquivo, o Excel vai converter campos como por exemplo, números no formato texto para o formato numérico, vide exemplos abaixo.

O problema é quando o sistema destino "espera" um campo de números com zeros à esquerda.

Se o usuário abrir o arquivo no Excel e salvar, os zeros à esquerda dos números serão perdidos, portanto, recomendo fortemente que para conferir arquivos csv, sejam utilizados o bloco de notas ou algum outro editor de texto puro (notepad++, Kedit, TextPad dentre outros) ou habitue-se sempre a fechar o arquivo sem salvar.

;-)

sexta-feira, 2 de abril de 2021

Guia de atalhos e funções do Excel

Segue o link para download gratuito. Aproveitem para visitar as demais áreas desse excelente site. ;-)

https://exceljet.net/subscriber-toolbox?utm_source=newsletter&utm_medium=email&utm_campaign=functionsPDF

sexta-feira, 26 de junho de 2020

Obtendo o valor financiado a partir da parcela

Desenvolvi este código há muito tempo, vou deixar aqui para quem quiser utilizar.
O valor financiado quase sempre terá uma diferença de centavos, devido as decimais que envolvem o cálculo.
A tabela price está considerando a contagem dias corridos, ou seja, varia de 30, 31, 28 ou 29 dias conforme o mês (lembrando que no cálculo com a HP12 o resultado será sempre diferente pois a mesma considera todos os meses com 30 dias).
No início da função estão as informações de como passar os parâmetros.
Testem antes de utilizar!

;-)

Public Function f_composicao(taxa As Double, _
                            qtd_parcelas As Integer, _
                            dt_inicio As String, _
                            dt_primeira_parcela As String, _
                            vlr_parcela) As Double
                            
    'Para passar os argumentos desta função:
    'Informar valores no padrão americano (ponto no lugar de vírgula)
    'Informar data entre aspas duplas (string) Exemplo: "31/07/2013"
    'Informat a taxa em percentual. Exemplo 1,5% -> informar 0.015
                            
    Dim i                   As Integer
    Dim tx_ao_dia           As Double
    Dim vlr_composicao      As Double
    
    tx_ao_dia = Fix((((taxa + 1) ^ (1 / 30) - 1) * 100) * 1000000000) / 1000000000
    
    vlr_composicao = 0
    
    'Primeira parcela
    vlr_composicao = vlr_parcela * (Fix(1 / ((tx_ao_dia / 100 + 1) ^ ((CDate(dt_primeira_parcela) - CDate(dt_inicio)))) * 1000000000) / 1000000000)
    
    For i = 1 To qtd_parcelas - 1
        vlr_composicao = vlr_composicao + vlr_parcela * (Fix(1 / ((tx_ao_dia / 100 + 1) ^ (DateAdd("M", i, CDate(dt_primeira_parcela)) - CDate(dt_inicio))) * 1000000000) / 1000000000)

    Next
    f_composicao = vlr_composicao
    
    Exit Function

End Function

terça-feira, 14 de abril de 2020

Bons sites de Excel

Deixando registrado aqui para não esquecer.

https://analystcave.com/

https://exceljet.net/

;-)

domingo, 19 de janeiro de 2020

Tabela no Excel

Um dos recursos bem interessantes do Excel é a utilização de tabelas dentro da planilha.
Para converter um intervalo (ou range) em tabela, selecione-a e clique no menu Inserir e na faixa de opções, selecione "Tabela".
Nomes de colunas serão criados automaticamente caso não haja.
A partir daí, surgem as vantagens:
- Ao inserir uma soma no final de alguma coluna, novas opções serão exibidas pelo Excel: média, contagem, máx, mín, etc.;
- Ao rolar a tabela para cima, os cabeçalhos da tabela se fixam no cabeçalho de linhas;
- O autofiltro aparece automaticamente nos cabeçalhos;
- A área da tabela já recebe uma nova formatação que facilita a visualização;
- Fórmulas ficam mais fáceis de interpretar (vou abordar isso em outro post futuro);
- Muitas outras opções a mais.

Caso queira voltar a tabela para um intervalo comum, clicar em alguma célula da tabela, ir no meu Design e na faixa de opções, clicar em Converter em intervalo.

Nas primeiras vezes que se utiliza o recurso, pode parecer um pouco complicado, mas rapidamente você se acostuma e percebe que é um recurso muito útil, fácil de utilizar e que oferece muitas opções interessantes.

;-)

segunda-feira, 13 de janeiro de 2020

Substituindo o antigo arquivo *.bat

Antigamente usávamos os arquivos do tipo "batch" que tinham vários comandos e funcionavam como se fosse executáveis.
O uso era muito comum nas tarefas de automatização.
Hoje temos os scripts que podem executar uma gama de tarefas muito maior, como por exemplo, abrir um banco de dados Access e executar consultas e macros.
A sintaxe dos comandos é muito parecida com o VBA, quem já programa nessa linguagem, não terá dificuldades com os scripts.
Para criar um script, basta digitar o(s) comando(s) num bloco de notas e salvar com extensão *.vbs.
Para executar, é só dar um duplo clique ou acioná-lo pelo agendador de tarefas (o que é mais usual).
Vejam um exemplo bem simples de script para copiar arquivo:

Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "C:\FSO\ScriptLog.txt" , "D:\Archive\", OverwriteExisting

Indico abaixo 2 sites excelentes para aprender mais sobre os scripts:

http://www.macoratti.net/wsh_1.htm
https://www.juliobattisti.com.br/tutoriais/ricardosilva/wsh001.asp

;-)

domingo, 12 de janeiro de 2020

Dividindo uma amostra em partes iguais (ou o mais próximo disso)

Numa base de CNPJ's relativamente grande (acima de 100 mil registros), é comum se dividir a base para aplicar alguma estratégia de negócios diferentes para cada grupo e depois efetuar o comparativo para ver qual deles gerou melhor resultado.
Uma das formas práticas e rápidas para dividir a base é utilizar o random digit, utilizando para isso, o 6º e 7º dígito de uma raiz do cnpj com 9 dígitos para identificar o registro e depois classificá-los conforme a necessidade.
Segue abaixo o código para se obter os dígitos e depois basta separar o público.

Public Function fn_randomDG(cnpj As Double) As Integer

    Dim v_cnpj As String
    Dim d6 As String
    Dim d7 As String

    v_cnpj = cnpj   

    d6 = Mid(v_cnpj, 6, 1)
    d7 = Mid(v_cnpj, 7, 1)   

    fn_randomDG = Val(d6 + d7)

End Function

Neste exemplo, dividi uma base de 200 mil registros em 4 partes com random digit de
0 a 25, 26 a 50, 51 a 75 e 76 a 99.
Veja que o resultado são 4 bases de quantidades de registros bastante próximos:

50.920
49.972
49.739
49.364

A base usada para testes foi baixada no site da Receita Federal, são CNPJ's válidos e disponibilizados para qualquer pessoa fazer o download:
https://www.fazenda.pr.gov.br/modules/conteudo/conteudo.php?conteudo=109

Não vou entrar no detalhe do random digit pois é um assunto no qual não sou especialista, portanto, sem propriedade nenhuma para escrever aqui como foi desenvolvido e a aplicabilidade em outros tipos de análises.
Se você tiver interesse e paciência, procure no Google ou converse com algum estatístico (essa segunda opção, eu acho mais interessante...)

Até a próxima!
;-)

segunda-feira, 30 de dezembro de 2019

Principais teclas de atalho no ambiente do VBA

A utilização de teclas de atalho aumenta bastante a agilidade quando se programa em VBA, principalmente na fase de depuração do código.
Quem me conhece, sabe que não sou muito fã do mouse e prefiro atalhos por proporcionar acesso direto a recursos, sem precisar clicar em vários itens até chegar onde se precisa.
Segue abaixo a lista dos atalhos que mais utilizo, experimentem.

;-)

CTRL+R = Exibir o Projetc Explorer
CTRL_G = Exibir a janela de verificação imediata
F2 = Exibir o pesquisador de objetos
F5 = Executar o código inteiro
F8 = Executar o código passo a passo
F9 = Ativar/desativar ponto de interrupção
CTRL+Shift+F9 = Desativar TODOS os pontos de interrupção
CTRL+Break=Pausar a execução do código

domingo, 22 de dezembro de 2019

Tabela dinâmica para iniciantes

Para quem quiser aprender tabelas dinâmicas no Excel, sugiro um curso muito bom que fiz na Udemy com o Cristian Firmino.
Vocês aprenderão de forma fácil e rápida para que serve, quando e como utilizar uma tabela dinâmica que é um dos recursos mais versáteis do Excel para análise e apresentação de dados.
Segue o link, esse eu recomendo:

https://www.udemy.com/course/excel-analise-dados-tabela-dinamica-cristian-firmino/

;-)

domingo, 11 de novembro de 2018

Posicionando o cursor na tela com o VBA

Em vários programas tive esse problema: ao selecionar uma célula pelo VBA, o cursor vai até a célula, porém a tela não rola e as vezes o cursor fica fora da área visível da planilha, apesar de selecionada.

Segue o comando para contornar esse problema e deixar sempre a célula selecionada no topo da tela.

Experimentem o comando na janela de verificação imediata do VBA para ver a diferença.

;-)

range("Z200").Select

Application.Goto Reference:=Worksheets("plan1").Range("Z200"), scroll:=True

sábado, 27 de outubro de 2018

Copiando texto de imagem

Antigamente tínhamos vários softwares do tipo "OCR" - Optical Character Recognizer que extraiam textos de imagens, muitos com índice de acerto baixo (minha opinião) dado que as imagens precisavam ter uma resolução muito boa.

O OneNote que faz pate do pacote Office já tem esse recurso.
Conheço poucas pessoas que utilizam esse programa, mas para quem tiver curiosidade, eu recomendo, pois ele é muito útil para anotações diversas nas quais podem ser inseridos imagens, arquivos, textos, prints de telas, etc.

Um programa semelhante e muito útil que também recomendo, é o Keep do Google, vale a pena dar uma olhada para conhecer.

Bem, para extrair o texto pelo OneNote é bem simples:
Basta inserir a imagem em alguma anotação, clicar com o botão direito e selecionar "Extrair texto da imagem".
Obviamente, o texto não será 100% reconhecido, mas dependendo da imagem, o índice de acerto será muito bom.

;-)

domingo, 29 de julho de 2018

Pendrive Sony ou Multilaser?

Em qual marca você confia mais, Sony ou Multilaser?

;-)

segunda-feira, 16 de julho de 2018

Programa para particionar discos

Segue dica de um programa leve e o que é melhor, gratuito, para particionar discos.
Funcionamento muito parecido com o famoso Partition Magic.
Para quem precisa só para tarefas básicas, resolve muito bem.

https://www.minitool.com/partition-manager/partition-wizard-home.html

No site do desenvolvedor, tem diversas outras ferramentas para recuperação de dados em várias mídias, vale a pena dar uma olhada.

;-)

segunda-feira, 25 de junho de 2018

Formatar rapidamente uma planilha

No meu trabalho, todos os dias exporto dados de outros aplicativos para o Excel, seja para análise de dados ou para envio a outros colegas e quase sempre as colunas estão na largura padrão fazendo com que as informações fiquem sobrepostas ou truncadas.
Nada complicado, basta ajustar a largura das colunas e algumas vezes, a altura da linha e tudo bem, mas como no meu caso é repetitivo, crei uma macro que faz isso rapidamente.
Ganho muito em produtividade e os destinatários das minhas planilhas também.
A macro em si é muito simples, mas fica a dica para ganhar tempo em pequenas tarefas recorrentes, que somados, representam um ganho significativo no fim do dia.

Sub AjustaColunas()
'
' Formata Macro
'
' Atalho do teclado: Ctrl+Shift+F
'
    Cells.Select
    Selection.RowHeight = 15
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    
End Sub