Dicas Excel - Como comprimir arquivos XLSX para arquivos ainda menores - How to Compress xlsx Files to the Smallest Possible Size

DESENVOLVO APLICAÇÕES VBA

Acesse uma ENORME lista de Dashboards e Infográficos

Aprenda a detectar gráficos que precisam mudar

Dados ou Informações?

Dashboards - 8 Importantes características desejáveis

Dashboards e Infocharts - Diversas Visualizações de Dados

Diversos exemplos de Gráficos

Excel - DashMap - Mapa do Brasil

Key Performance Indicators - 7 Características desejáveis

MIS - Usufruindo a excelência na análise das suas Informações

O Melhor do Business Intelligence

Os 10 mandamentos do gerenciamento de projetos

Receba 3 Calendários Customizáveis de 2013 em Excel

Usando Gauges em Dashboards no MS Access

Você tem direito as informações da sua empresa

Hello folks!

Há muitos posts atrás indiquei como era possível compactarmos o conteúdo de planilhas por simplesmente:

- Exportar o conteúdo delas para planilhas novas

- Manter o mesmo tipo de fonte em toda a planilha

- Inserir um código que ao fechar excluísse todas as linhas em branco não utilizadas

Muitos implementaram essas maluquices e tiveram bons resultados.

Agora a dica é boa, e é prá valer.

Suponhamos que você tenha um arquivo xlsx com 20 MB de tamanho. Precisando reduzir o arquivo para um tamanho mais aceitável.

Normalmente, eu o converteria num arquivo xls, o que o tornaria muito menor. Mas esse arquivo em particular tem demasiadas linhas para serem convertidas para um xls.

Então a primeira coisa que eu tento fazer é 'zipá-lo' com o WinZip. Mas, como você pode perceber, ele é comprimido para apenas 17 MB, o que realmente não é muito menor. Por quê? Isso porque os arquivos xlsx já são tecnicamente compactados. E todos nós sabemos que quando você tenta 'zipar' um arquivo 'zipado', não obterá um bom encolhimento neste.

Bem, vamos ao truque. Pego o meu arquivo original e mudo a extensão dele para .zip

Depois disso, eu extraio o conteúdo do arquivo .zip.

Uma vez que os conteúdos são extraídos, eu os 'zipo' de volta usando um programa de compressão,como o WinZip mesmo.

Isto nos deixa com um arquivo compactado contendo todo o meu conteúdo, como o

tamanho comprimido de 14 MB. Agora

posso mudar a extensão de volta para xlsx.

Quando o arquivo for alterado novamente para xlsx, ele funciona apenas como um arquivo Excel normal. 6 MB menor que o original.

Então você pode estar se perguntando: Ei! O que houve aqui? Aparentemente a tecnologia de compressão que o MS Excel utiliza para criar arquivos xlsx é inferior ao algoritmo utilizado no Winzip. Agora tente compactar com o Winrar para ver.

Ahh, só de brincadeira, que tal criar um aplicativo que automatize toda a compressão? E não deixe de enviá-lo para que eu possa dar o crédito. Té +, e boa diversão.

Reference: DataPigTechnologies

Tags: Bernardes, MS, Microsoft, Office, Excel, small, size, xls, xlsx, dica, trick, tip, truque, zip, compact, winzip

VBA Excel - Interações com APIs - UserForms With Windows API Functions

Disponibilizar diversas funcionalidades que propiciem interferência nos formulários que criamos no MS Excel através de chamadas as suas APIs é muito prático ,rápido e funcional.

Bem, como sempre, estou usando um exemplo simples como o de fazer um botão aparecer e sumir num formulário criado por nós mesmos no MS Excel. Mas veja esta como uma oportunidade para aprender a utilizar códigos de terceiros dentro das suas aplicações VBA.

O que é uma API?

API, é o acrônimo, sim, mais prá nossa coleção, de Application Programming Interface (ou Interface de Programação de Aplicativos) é um conjunto de rotinas e padrões estabelecidos por um software para a utilização das suas funcionalidades através de aplicativos que não pretendem envolver-se em detalhes da implementação do software, mas apenas usar seus serviços.

De modo geral, a API é composta por uma série de funções acessíveis somente por programação, as quais nos permitem utilizarmos características de software menos evidentes aos programadores mais tradicionais.

Por exemplo, um sistema operacional possui uma grande quantidade de funções na API, que permitem ao programador criar janelas, acessar arquivos, criptografar dados etc. Mas as APIs dos sistemas operacionais costumam ser dissociadas de tarefas mais essenciais, como a manipulação de blocos de memória e acesso a dispositivos. Essas tarefas são atributos do núcleo (kernel) de sistema e raramente são programáveis. Outro exemplo são os programas de desenho geométrico que possuem uma API específica para criar automaticamente entidades de acordo com padrões definidos pelo utilizador.

Mais recentemente, o uso de API tem se generalizado nos plugins (acessórios que complementam a funcionalidade de um programa). Os autores do programa principal fornecem uma API específica para que outros autores criem plugins, estendendo as funcionalidades do programa.

Agora que já ampliamos um pouco mais a nossa compreensão do que são as APIs, façamos melhor, vamos utilizar algumas chamadas às suas funcionalidades.

Ativar no formulário o botão Maximize

Mostra ou torna invisível o botão de Maximizar e o de Minimizar no formulário ativo. Os botões ficarão visíveis, mas somente terão funcionalidades de acordo com a respectiva chamada da sua função. ShowMinimizeButton ou ShowMaximizeButton.

Function ShowMaximizeButton (UF As MSForms.UserForm, HideButton As Boolean) As Boolean

Function ShowMinimizeButton (UF As MSForms.UserForm, HideButton As Boolean) As Boolean

Bem, antes de efetuar chamadas as funções acima, precisa criar um módulo no qual colocará os códigos abaixo, pois estes são quem efetuarão as chamadas as APIs do Windows.

Existem inúmeras funções com chamadas intrínsecas, e será muito produtivo que tire tempo para estudar cada uma destas, a fim de ampliar a sua compreensão de como efetuar tais chamadas.

Este post é apenas uma pincelada na utilização desta técnica, que poderá ser ampliada até mesmo ao uso das APIs do Google, GoogleMaps, GMail, Skype, etc...

As funções que não encontrar constantes abaixo, podem ser facilmente encontradas com o Google na Internet. E isso foi proposital, para que pesquise outras idéias em outros Blogs e fóruns.

Function ShowMaximizeButton (UF As MSForms.UserForm, _

HideButton As Boolean) As Boolean

'

' ShowMaximizeButton

' Mostra (Se HideButton estiver como False) ou esconde (Se HideButton estiver como True)

' O botão de maximizar da janela.

'

Dim UFHWnd As Long

Dim WinInfo As Long

Dim R As Long

Let UFHWnd = HWndOfUserForm (UF)

If UFHWnd = 0 Then

   Let ShowMaximizeButton = False

   Exit Function

End If

Let WinInfo = GetWindowLong (UFHWnd, GWL_STYLE)

If HideButton = False Then

   Let WinInfo = WinInfo Or WS_MAXIMIZEBOX

Else

   Let WinInfo = WinInfo And (Not WS_MAXIMIZEBOX)

End If

Let R = SetWindowLong (UFHWnd, GWL_STYLE, WinInfo)

Let ShowMaximizeButton = (R <> 0)

End Function

Function ShowMinimizeButton (UF As MSForms.UserForm, _

   HideButton As Boolean) As Boolean

Dim UFHWnd As Long

Dim WinInfo As Long

Dim R As Long

Let UFHWnd = HWndOfUserForm (UF)

If UFHWnd = 0 Then

   Let ShowMinimizeButton = False



   Exit Function

End If

Let WinInfo = GetWindowLong (UFHWnd, GWL_STYLE)

If HideButton = False Then

   Let WinInfo = WinInfo Or WS_MINIMIZEBOX

Else

   Let WinInfo = WinInfo And (Not WS_MINIMIZEBOX)

End If

Let R = SetWindowLong (UFHWnd, GWL_STYLE, WinInfo)

Let ShowMinimizeButton = (R <> 0)

End Function

Function HWndOfUserForm (UF As MSForms.UserForm) As Long

' HWndOfUserForm

' This returns the window handle (HWnd) of the userform referenced

' by UF. It first looks for a top-level window, then a child

' of the Application window, then a child of the ActiveWindow.

Dim AppHWnd As Long

Dim DeskHWnd As Long

Dim WinHWnd As Long

Dim UFHWnd As Long

Dim Cap As String

Dim WindowCap As String

Let Cap = UF.Caption

' First, look in top level windows

Let UFHWnd = FindWindow (C_USERFORM_CLASSNAME, Cap)

If UFHWnd <> 0 Then

   Let HWndOfUserForm = UFHWnd

   Exit Function

End If

' Not a top level window. Search for child of application.

Let AppHWnd = Application.hwnd

Let UFHWnd = FindWindowEx (AppHWnd, 0&,

C_USERFORM_CLASSNAME, Cap)

If UFHWnd <> 0 Then

   Let HWndOfUserForm = UFHWnd

   Exit Function

End If

' Not a child of the application.

' Search for child of ActiveWindow (Excel's ActiveWindow, not

' Window's ActiveWindow).

If Application.ActiveWindow Is Nothing Then

   Let HWndOfUserForm = 0

   Exit Function

End If

Let WinHWnd = WindowHWnd (Application.ActiveWindow)

Let UFHWnd = FindWindowEx (WinHWnd, 0&, C_USERFORM_CLASSNAME, Cap)

Let HWndOfUserForm = UFHWnd

End Function

Referência: Chip Pearson

Tags: Bernardes, MS, Microsoft, Office, Excel, VBA, FORM, userform, API, functions, Chip Pearson, ShowMaximizeButton, HWndOfUserForm, GetWindowLong, SetWindowLong, FindWindow, FindWindowEx, WindowHWnd

André Luiz Bernardes

A&A® - Work smart, not hard.

bernardess@gmail.com

VBA Excel - Array - Carregando o conteúdo de uma célula num vetor.

Os desenvolvedores sempre precisam lidar com bases de dados esdrúxulas. Às vezes estas tem como fonte planilhas do MS Excel, e invariavelmente precisamos popular as nossas bases de dados com este conteúdo sem qualquer higienização.

Imagine que você tenha uma lista de dados onde uma das colunas contém inúmeros endereços de e-mail da mesma pessoa, ou inúmeros telefones de contato, ou dois ou três endereços de contato, mas a sua tabela tem um campo para cada uma dessas ocorrências.

Isso seria bem trabalhoso não é mesmo?

Suponha que eu tenha uma planilha com o faturamento da minha rede de sorveterias. Mas coloquei tudo nas seguintes 2 colunas:

Faturamento Cidades

R$ 5,500,00 Abatiá, Altamira do Paraná, Alto Paraíso, Alto Paraná, Alto Piquiri, Altônia

R$ 3.700,00 Campina da Lagoa, Campo Bonito, Campo Mourão, Cândido de Abreu

R$ 3.700,00 Godoy Moreira, Goioerê, Grandes Rios, Guaíra, Guairaçá, Guapirama

Mas a minha tabela tem 10 campos onde posso desmembrar as cidades, como faço?

Utilizei a função FillCitiesVector (nFrase As String, nOccurs As Single)

Dentro do meu módulo de funções defini uma variável como pública, conforme abaixo:

Public MyNames (1 To 500) As String ' (500) é o Nº máximo de cidade que podem ser encontradas numa célula.

A minha chamada à função FillCitiesVector é precedida das seguintes linhas de código:

Dim nCharss As Single

Let nCharss = Val (fCountOccur (Range("C" & i).Value, ",")) + 1

' Coloca todas as cidades num vetor.

Call FillCitiesVector(Replace(Range("C" & i).Value, ", ", ","), nCharss)

A função fCountOccur permite contar o número de separadores dentro de uma string, que no nosso caso são as vírgulas.

A função Replace está substituindo as vírgulas seguidas de espaços, apenas por vírgulas sem espaços.

Segue a função FillCitiesVector

Function FillCitiesVector (nFrase As String, nOccurs As Single)

   ' Author: Date: Contact: URL:

   ' André Bernardes 16/11/2011 16:41 bernardess@gmail.com http://inanyplace.blogspot.com/

   ' .

   ' Listening: .

   Dim i As Single

   Dim ponteiro As Single

   Dim nCheck As Boolean



   Let nCheck = True

   For i = 1 To nOccurs + 1

   If nCheck Then

   Let MyNames(i) = Mid(nFrase, i, InStr(i, nFrase, ",") - 1) ' Popula esta dimensão do vetor.

   Let ponteiro = Len(MyNames(i)) + 1 ' Reposiciona o ponteiro.

   Let nFrase = Trim(Mid(nFrase, ponteiro + 1, 5000))

   Let nCheck = False

   Else



   If InStr(1, nFrase, ",") <> 0 Then ' Checa se ainda há mais de uma cidade

   Let MyNames(i) = Mid(nFrase, 1, InStr(1, nFrase, ",") - 1) ' Popula esta dimensão do vetor.

   Let ponteiro = Len(MyNames(i)) + 1 ' Reposiciona o ponteiro.

   Let nFrase = Trim(Mid(nFrase, ponteiro + 1, 5000))

   Else

   Exit For

   End If

   End If

   Next

End Function

Segue a função fCountOccur

Public Function fCountOccur (strSource As String, strMatch As String) As String

   ' Author: Date: Contact: URL:

   ' André Bernardes 16/11/2011 16:41 bernardess@gmail.com http://inanyplace.blogspot.com/

   ' .

   ' Listening: .

   Dim iCount As Integer

   Dim iPosition As Integer



   Let iCount = 0



   For iPosition = 1 To Len(strSource)

   If Mid(strSource, iPosition, 1) = strMatch Then iCount = iCount + 1

   Next



   Let fCountOccur = iCount

End Function

Tags: array, cell, matriz, vetor, carregar

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

bernardes@.com

VBA ACCESS - Campo Auto Numérico - Creating an AutoNumber field from code - VBA, DAO, DDL e SQL

Criar campos de autonumeração em tabelas usando VBA, DAO, DDL e SQL

Creating an AutoNumber field from code

Append a record using vba where there is an auto number field

Auto Increment

Auto-number text field

AutoNumber and INSERT INTO VBA Issue

Autonumber field

Autonumber value of last inserted row

Autonumber vba

Autonumeração complexa

Create an Autonumber Field with VBA For a MS Access

Creating an AutoNumber field from code

Creation of an autonumber with VBA access

Microsoft Access Autonumber Increments

Setting the value of an Autonumber field

VBA - How to create user-defined auto-increment data type

Existem 2 caminhos para acrescentarmos um campo cujo o conteúdo seja autonumerado:

Um dos modos é fazê-lo através do comando SQL DDL Create Table, e o outro é o modo como abordaremos neste artigo usando VBA e DAO para adicionar um campo em uma tabela pré-existente.

Antes de continuar, um pequeno parênteses, deixe seus comentários para este post.

A função abaixo, desenvolvida por Dev Ashish, serve para efetuar tal ação em qualquer tabela na qual tenhamos a necessidade de inserirmos um campo numerado.

Function fCreateAutoNumberField (ByVal strTableName As String, ByVal strFieldName As String) As Boolean

   Dim db As DAO.Database

   Dim fld As DAO.Field

   Dim tdf As DAO.TableDef

   On Error GoTo ErrHandler

   Set db = Application.CurrentDb

   Set tdf = db.TableDefs(strTableName)



   ' Primeiro criamos um campo com o tipo = Long Integer

   Set fld = tdf.CreateField(strFieldName, dbLong)



   With fld

   ' Adicionamos o dbAutoIncrField para efetuar chamadas ao Jet que sozinho efetuará a autonumeração do campo

   Let .Attributes = .Attributes Or dbAutoIncrField

   End With



   With tdf.Fields

   .Append fld

   .Refresh

   End With



   Let fCreateAutoNumberField = True



ExitHere:

   Set fld = Nothing

   Set tdf = Nothing

   Set db = Nothing

   Exit Function

ErrHandler:

   Let fCreateAutoNumberField = False



   With Err

   MsgBox "Erro: " & .Number & vbCrLf & .Description, _

   vbOKOnly Or vbCritical, "CreateAutonumberField"

   End With

   Resume ExitHere

End Function

Ei, Ok! tudo bom, tudo legal, mas e quanto a primeira opção? Não chorem, coloquei-a mais abaixo:

Para criar uma tabela com um campo com AutoNumeração e PrimaryKey, além de um campo chamado MyText com o tipo TEXTO e comprimento de 10, insira a instrução a seguir na janela de consulta de definição de dados e, em seguida, executar a consulta.

CREATE TABLE Table1 _

(Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, _

MyText TEXT (10))