sexta-feira, 29 de julho de 2011

EXCEL GANTT TEMPLATE

Para o planeamento de tarefas e projecto, utilizo normalmente o MSPROJECT.. Há algum tempo atrás porém, tive necessidade de criar no Excel algo similar, de modo a poder juntar no mesmo ficheiro vários tipos de informação, para que o cliente pudesse usar o mesmo programa e abrir um único ficheiro.
Após inúmeras consultas e pesquisas em sites e fóruns da especialidade, consegui criar um modelo de planeamento com gráfico de GANTT, com algumas funções do MSPROJECT, suficientes para satisfazer as necessidades daquele projecto em particular e poder, assim, incluir no ficheiro Excel que enviei ao cliente o respectivo planeamento que, de outro modo, só seria possível apresentando um outro ficheiro do MSPROJECT.
O exemplo que aqui apresento está bastante simplificado em relação ao modelo final. A seu tempo mostrarei a evolução e as melhorias que fui incrementando, adaptando-o assim a outras projectos. No entanto, este já permite trabalhar com barras e predecessores, indentação e wbs.
For project management and planning, I use MSPROJECT most. Some time ago however, I needed to create something similar in EXCEL, so I was available join an link in the same file several types of information so that customers could use a single program and open a single file.
After numerous consultations and research on specialty websites and forums, I managed to create a model of planning with GANTT charts, with some functions of MSPROJECT, sufficient to cover the needs of that particular project and can thus be included in the Excel file I sent to my client that, otherwise, could only be presenting in another file MSPROJECT.
The example given here is greatly simplified compared to the final model. In time, I will share the evolution and the improvements that I have been increasing, thus adapting it to other projects. However, this allows to work already with bars and predecessors, indentation and wbs.
Indentação das Tarefas e Criação de WBS (Task Indentation and WBS generate)
Planeamento em GANTT (GANTT Management)
Linha de Tempo Diferente (A different Timeline)

A enorme versatilidade do Excel permite recriar, ainda que com limitações, algumas funcionalidades de outros programas, com a vantagem de podermos criar rotinas e automatizar ligações a informação diversificada,  tudo dentro do mesmo ficheiro.
Outra vantagem destes modelos em Excel é que podemos manipular a sua estrutura e adaptá-la a outra situação qualquer. É bom lembrar, contudo, que é necessário perceber alguma coisa de VB, fórmulas e uso do Excel em geral para poder proceder a quaisquer alterações de fundo.
Para quem não trabalha ou não gosta da complexidade do MSPROJECT, para quem precisa apenas de uma ferramenta simplificada de programação, penso que este modelo poderá ser útil.
Não tive muito cuidado com o aspecto e o design. Preocupei-me mais em divulgar o modelo.
The versatility of Excel allows you to recreate, even with limitations, some features of other programs with the advantage of being able to create routines and automate links to diverse information, all within the same file.
Another advantage of these models in Excel is that we can manipulate its structure and adapt it to any other situation. It is worth remembering, however, it is necessary to understand something of VB, using Excel formulas and in general to be able to make any substantive changes.
For those who do not work or do not like the complexity of MSProject, who just needs a streamlined programming tool, I think this model could be useful.
I didn't care too much about the look and design. I worried more to spread the model.

Se quiser experimentar, clique aqui para descarregar (versão portuguesa).
If you want to try this template, click here (english version)

quarta-feira, 20 de julho de 2011

VBA EXCEL - ENVIAR FOLHA DE CÁLCULO POR EMAIL (Emailing Worksheet)

Enviar por Email uma folha de cálculo (criando um novo ficheiro)
Email a spreadsheet (creating a new file)

Sub Mail_ActiveSheet()
    Dim strDate As String
    ActiveSheet.Copy
    strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
    ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
                        & " " & strDate & ".xls"
    ActiveWorkbook.SendMail "nome@domain.com", _
                            "Assunto" 'Subject Line
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
End Sub

VBA EXCEL - COPIAR DETERMINADAS FOLHAS DE CÁLCULO PARA UM NOVO LIVRO (Copy some specific worksheets to a new workbook)

Criar um novo ficheiro, a partir do livro existente, exportando ou copiando apenas as folhas de cálculo que desejamos.
Create a new file from the existing book, exporting or copying only the worksheets that we desire.

Sub ExportarFolhas()
Dim wb As Workbook
Dim fNome As Variant
Worksheets(array("Folha1", "Folha2")).Copy  '
cria novo livro (Creates new workbook)
Set wb = ActiveWorkbook
fNome = Application.GetSaveAsFilename( _
 FileFilter:
="Excel Files (*.xla), *.xla", _
 Title:="Especificar Localização:") 'Specify Location for Copy
If fNome = False Then
 '  Se o utilizador escolhe "cancelar" a cópia é eliminada
 ' If user has chosen "cancel" delete the copy
 wb.Close Savechanges:=False
Else

 wb.SaveAs Filename:=fNome
 wb.Close Savechanges:=False
End If
End Sub

EXCEL - A UTILIDADE DA TECLA F5 (F5 key usefulness)

Imagine que pretende eliminar todas as caixas de texto existentes numa folha de cálculo mas não consegue localizar todas. E se pretender saber, por exemplo, quais as células que contêm formatações condicionais. E se quiser seleccionar as células com fórmulas numéricas ou de texto ou que contenham valores de erro.
F5 é uma tecla a partir da qual pode seleccionar o que pretende e, depois, proceder à sua eliminação ou alteração de dados.
Por exemplo, se quisermos eliminar todas as imagens existentes:
Imagine you want to delete all existing text boxes on a spreadsheet but can not find all of them. And if you want to know, for example, which cells containing conditional formats. And if you wish to select cells with text formulas or numerical or containing error values.
F5 is a key from which you can select what you want and then proceed to their elimination or data changing.
For example, if you want to delete all existing images:

1. Premir F5 (Press F5)
2. Escolher ESPECIAL (Choose SPECIAL)
3. Escolher Objectos (Choose Objets)
4. Eliminar (Delete)

terça-feira, 19 de julho de 2011

EXCEL - SUBSTITUIR PONTO POR VÍRGULA (Changing comma to point) ou vice-versa

Por vezes, copiamos números de outros programas (Word, PDF, etc.) para Excel e reparamos que os mesmos são colados em formato texto, porque a separação das casas decimais é um ponto em vez de vírgula. Se se trata de uma ou duas células, podemos fazer a substituição manualmente, mas se for uma tabela de dados considerável, vamos perder um tempo precioso a proceder à substituição em cada célula.
Existe uma forma rápida de executarmos o procedimento de uma só vez:
Sometimes when copy numbers from other programs (Word, PDF, etc.) to Excel we notice that they are stuck in text format, because decimal separation is a point instead of comma. Whether it is one or two cells, we can make the substitution manually, but if it is a considerable data table, we will lose valuable time to replace it in each cell.
Here is a quick way to execute the procedure once:
1. Seleccionar o intervalo de valores ou células que pretende alterar
2. CTRL+U - abre a janela de substituição
3. Digitar "." no campo "Localizar"
4. Digitar "," no campo "Substituir Todas"
1. Select range values ​or cells ​you want to change
2. CTRL + U - Opens the replacement window
3. Type "." in the "Find"
4. Enter "," in the "Replace All"

VBA EXCEL - ABRIR UM FICHEIRO NUMA FOLHA ESPECÍFICA (Open a workbook in a specific sheet)

Abrir um Ficheiro numa Folha específica
Open a workbook in a specific sheet

Sub AbrirFicheiro()
Dim Ws As Worksheet
Dim SProc As String
Dim SEncontrar As String
Application.ScreenUpdating = False
'Procurar a Folha Específica (Look for the specific sheet)
SProc = WorkSheets("Folha1").Range("A1")
'Abrir o livro (Open workbook to look in)
Workbooks.Open Filename:="C:\...\Livro1.xls"
'
Procurar em todas as folhas até encontrar a pretendida (Look through each sheet until found the wanted one)
For Each Ws In ActiveWorkbook.Worksheets
On Error Resume Next
SEncontrar = Ws.Cells.Find _
(What:=SProc, after:=Ws.Cells(1, 1)).Address
If SEncontrar <> "" Then
Application.ScreenUpdating = True
End If
Next Ws

End Sub

VBA EXCEL - BLOQUEAR UMA DETERMINADA ÁREA PARA VISUALIZAÇÃO (Block a particular area for a view)

Existem duas formas de bloquear ou congelar uma determinada área da folha de cálculo, de modo a fixar a sua visualização, sempre que entramos na folha:
There are two ways to block or freeze a particular area of the spreadsheet in order to establish their view, whenever we enter the spreadsheet

1.
Sobre o separador da folha de cálculo, clicar no botão direito do rato
Over the tab of the worksheet, right click the mouse button
Escolher "Ver Código"
Choose "View Code"
Premir F4
Press F4
Em "ScrollArea" editar o intervalo que queremos fixar, por exemplo "G2:AI50"
In "ScrollArea" edit the range that we set, for example "G2: AI50"
Premir Alt+Q para fechar a janela VBA
Press Alt+Q to close the VBA window
Guardar
Save

2.
Sobre o separador da folha de cálculo, clicar no botão direito do rato
Over the tab of the worksheet, right click the mouse button
Escolher "Ver Código"
Choose "View Code"
Introduzir o seguinte código:
Enter the following code:

Private Sub Worksheet_Activate()
    ActiveSheet.ScrollArea = "G2:AI50"   
End Sub

segunda-feira, 18 de julho de 2011

VBA EXCEL - IMPORTAR DADOS DE UM LIVRO PARA OUTRO (Import Data from a Workbook to Another)

Uma forma de importar ou copiar dados de um livro ("Livro1") fechado para o livro activo ("Livro2"), atendendo a um conjunto de condições.
Este exemplo mostra como podemos importar ou copiar um conjunto de dados de um livro para outro, sem ser necessário o recurso a colar e copiar.
Para tal, basta colocar este código num módulo VBA e correr a macro. Podemos igualmente associar a macro a uma botão, possibilitando assim a execução do processo através de um único click.
A simple way to import or copy data from a closed book ("Livro1") for the active workbook ("Livro2"), given a set of conditions. 
This example shows how we can import or copy data from one workbook to another without requiring the use of copy and paste.
Simply put this code in a VBA module and run the macro. We can also associate the macro to a button, allowing the execution of the process through a single click.
 

Sub ImportarDados ()
    Workbooks.Open Filename:="C:\...\Livro1.xls"
     Dim Origem As Worksheet
     Dim Destino As Worksheet
     Set Origem = Workbooks("Livro1.xls").Worksheets("Folha1")
     Set Destino = Workbooks("Livro2.xls").Worksheets("Folha1")
       k_linhas = 0
       For rw = 11 To k_linhas
          If Origem.Cells(rw, 1).Value > 0 And Origem.Cells(rw, 5) > 0 And Destino.Cells(rw, 17).Value =_
          _Origem.Cells(rw, 1).Value Then

          Destino.Cells(rw, 17).Value = Origem.Cells(rw, 1).Value
          k_linhas = k_linhas + 1

          End If
       Next rw
   Workbooks("Livro1.xls").Close SaveChanges:=False
    MsgBox "Introdução de Dados Concluída"
End Sub

domingo, 17 de julho de 2011

VBA EXCEL - OCULTAR LINHAS EM BRANCO (Hide Blank Rows)

Uma das situações recorrentes na utilização do EXCEL é esconder as linhas em branco que, propositadamente ou não, vamos deixando para trás à medida que construímos uma folha de cálculo. Mais tarde, constatamos que aquelas linhas estariam melhor escondidas.
Um opção rápida e consistente é utilizar uma pequena macro em VBA que, mediante um ou mais critérios, permite num só clique escolher as linhas que pretendemos esconder.
Aqui deixo três possibilidades:
One recurring situations in the use of EXCEL is to hide blank lines that, intentionally or not, we will leave behind as we build a spreadsheet. Later, we found that those lines would be better hidden.
One option is to quickly and consistently use a small VBA macro that, through one or more criteria, allows one-click to select the lines you want to hide.
Here I leave three possibilities:

1. 
Esconder todas as linhas em branco desde a linha 2 até à 65000
Hide all blank lines from the line 2 to the 65000

Sub OcultarLinha()
  Dim Linha, LinhaFinal As Integer
  LinhaFinal = Range("A65000").End(xlUp).Row
  For Linha = 2 To LinhaFinal
    If Range("A" & Linha).Value = 0 Then
    Range("A" & Linha).EntireRow.Hidden = True
    Else
    End If
  Next Linha
End Sub

2. 
Esconder todas as linhas, no intervalo entre a linha 4 e a linha 500, que na coluna "G"contenham células em branco ou de valor zero (processamento mais rápido que a alternativa 1)
Hide all rows in the range between line 4 and line 500, that in column "G" cells contain blank or zero (processing faster than the alternative 1)

Sub OcultarLinha()
  Dim i As Integer
  Application.ScreenUpdating = False
    With Sheets("sumario")
      .Cells.EntireRow.Hidden = False
          For i = 4 To 500
            Select Case .Range("g" & i).Value
              Case 0
              .Rows(i & ":" & i).EntireRow.Hidden = True
            End Select
         Next i
    End With
    Application.ScreenUpdating = True
End Sub

3.
Activando o Filtro Automático via VBA. A intenção é esconder as linhas que na coluna 7 ("G") contenham a letra "s" (processamento mais rápido que as alternativas anteriores)
Enabling AutoFilter via VBA. The intention is to hide the lines in column 7 ("G") containing the caracter "s" (that faster processing of the above)
 
Sub OcultarLinha()
    Sheets("Folha1").Select
    Range("A2:G500").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=7, Criteria1:="s"
End Sub

quinta-feira, 14 de julho de 2011

INTRODUÇÃO

Hoje é o início de um projecto que já estava pensado há algum tempo mas que, por manifesta indisponibilidade, foi sendo adiado mês após mês. Penso agora ter as coisas mais ou menos organizadas e, sobretudo, a disponibilidade necessária para poder ir em frente com este desejo antigo. O propósito deste blog é simplesmente partilhar os meus conhecimentos em algumas áreas relacionadas com a minha actividade, em particular a criação de macros e modelos em EXCEL. Sendo esta a minha primeira abordagem no que respeita a blogs, estou convencido que os primeiros posts serão ainda tímidos quanto à forma e conteúdo e que demorará ainda algum tempo até que possa optimizar a página, nomeadamente na suas perspectivas estética e funcional.
Vamos ver como corre.

Today is the beginning of a project that was thought for some time but, for obvious unavailability, it was being delayed month after month. I now have things more organized and, above all, the availability needed to be able to go ahead with this old desire. The purpose of this blog is simply to share my knowledge in some areas related to my work, in particular macros and templates creation in Excel. Being this my first approach to blogs, I am convinced that the first posts are still modest in form and content and it will take some time to optimize the page, especially in its aesthetic and functional perspectives.
Let's see how it goes.