Comunicación con Outlook

Continuando con la interacción entre los distintos programas de Microsoft con Excel esta vez le toca el turno a Outlook.

El procedimiento que viene a continuación nos permite crear mensajes (mails) Outlook y enviar el libro activo como dato adjunto.

Antes de ejecutar el procedimiento deberemos de seleccionar la referencia Microsoft Outlook 14.0 Object Librery desde el menú herramientas.

A continuación copiamos el procedimiento en un modulo.

Sub EnviarCorreo()

Dim appOutlook As Outlook.Application

Dim mensage As Outlook.MailItem

Set appOutlook = CreateObject("outlook.application")

Set mensage = appOutlook.CreateItem(olMailItem)

With mensage

    .Subject = "Prueba"

    .Body = " Prueba con datos adjuntos..... "

    .BodyFormat = olFormatHTML

    .Recipients.Add ("Nombre1")

    .Recipients.Add ("Nombre2")

    .Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name

    .Display

    .Send

End With

appOutlook.Quit

Set appOutlook = Nothing

End Sub

Para probar el ejemplo debes remplazar la palabra nombre(x) por nombres que figuren en la lista de contactos de Outlook.

De este procedimiento cabe destacar el método CreateItem (olMailItem)   que crea un nuevo elemento (mensaje de correo electrónico) de Microsoft Outlook.


El resto de propiedades y métodos no nos extenderemos más ya que los hemos utilizado en otras ocasiones…

Tablas dinámicas

Las tablas dinámicas son muy útiles para estructurar datos que se presentan en bruto, resumirlos y crear informes. Normalmente, una tabla de datos de Excel no puede cambiar de estructura. Podemos ordenar los datos, filtrarlos, añadir subtotales, pero la estructura en filas y columnas es inalterable. Uno de los objetivos de las tablas dinámicas es la posibilidad de alterar esa estructura y a la vez resumir datos.

El siguiente procedimiento establece las bases principales de utilización de VBA para crear una tabla dinámica. El código que utilizamos puede resultar un poco confuso.

A continuación explico brevemente algunos objetos relevantes:

PivotCaches:  Representa todas la memoria caché de tabla dinámica del libro especificado.

PivotTable:  Colección de todos los objetos de la hoja de cálculo especificada.

PivotFields: Representa un solo campo de tabla dinámica.

DisplayFieldCaptions: Controla si se muestran o no los botones de filtro y los títulos PivotField para filas y columnas en la cuadrícula.

Sub PivotTable()

Dim PTCache As PivotCache

Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Create _

(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)

Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _

TableDestination:=Range("F1"), TableName:="Ventas")

With PT

    .PivotFields("Zona").Orientation = xlPageField

    .PivotFields("Mes").Orientation = xlColumnField

    .PivotFields("Nombre").Orientation = xlRowField

    .PivotFields("Ventas").Orientation = xlDataField

    .DisplayFieldCaptions = False

End With

End Sub

La última sección del código se añade los campos a la tabla dinámica y se especifica su localización dentro de ella.

El código también podría ser más general si se utiliza índices en lugar de cadenas literales para las colecciones PivotFields. De esta forma, si el usuario cambia los encabezados de las columnas, el código seguirá funcionando.

Barra de Progreso

Una de las necesidades más comunes para un programador de Excel es indicar visualmente al usuario el progreso de la ejecución de una tarea y para ello utilizan la barra de progreso.

La barra de progreso es el elemento (normalmente representado por un formulario) que nos permiten mostrar de forma gráfica (parecido a un termómetro) el estado del avance de una tarea o proceso. Generalmente el gráfico está acompañado de información en forma de texto para medir con exactitud el avance.

Un indicador de progreso ralentiza un poco la ejecución de la macro, por eso si la rapidez de la macro es crucial en nuestro libro será mejor no utilizarla.

El siguiente procedimiento genera  mediante bucles una serie de números aleatorios, según se ejecutan realizan una serie de cambios en el formulario que hemos creado con anterioridad y que nos quedaría de la siguiente forma.

El  formulario Progreso contiene los siguientes elementos:

• Control de marco (Frame1)
• Control de etiqueta (Lavel1)  que simula la barra de progreso.
• Control de etiqueta (Lavel2) opcional

En un modulo copiamos los siguientes procedimientos:

Sub GeneradorNumeros()

Const RowMax As Integer = 500

Const ColMax As Integer = 40

Dim Cuenta As Integer

Dim r As Integer, c As Integer

Dim Pct As Single

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    Cells.Clear

    For r = 1 To RowMax

        For c = 1 To ColMax

            Cells(r, c) = Int(Rnd * 49)

            Cuenta = Cuenta + 1

        Next c

        Pct = Cuenta / (RowMax * ColMax)

        Call Progreso(Pct)

    Next r

Unload UserForm1

End Sub

Sub Progreso(n)

With UserForm1

    .Frame1.Caption = Format(n, "0%")

    .Label1.Width = n * (.Frame1.Width - 10)

    .Repaint

End With

End Sub

Sub ShowDialog()

UserForm1.Label1.Width = 0

UserForm1.Show

End Sub

Insertamos el siguiente procedimiento en la ventana de código del UserForm1, este procedimiento solo invoca al generador de números y nos colorea la etiqueta (Lavel1) con el color del tema de nuestro libro.

Como os podéis dar cuenta este procedimiento se escribe en el evento Activate del formulario que se desencadena cuando se muestra el formulario.  

Private Sub UserForm_Activate()

UserForm1.Label1.BackColor = ActiveWorkbook.Theme. _

ThemeColorScheme.Colors(msoThemeAccent1)

Call GeneradorNumeros

End Sub

Para iniciar el proceso ejecutamos la macro ShowDialog desde el cuadro de dialogo de Macro en la ficha programador.

Cuadros de dialogo Segunda Parte

Enlace de la primera parte.

Método GetOpenFilename:

Método GetOpenFilename es un objeto de Application, que obtiene un nombre de archivo correcto a través de un cuadro de dialogo. Este método muestra el habitual cuadro de dialogo de Abrir, pero no abre realmente el archivo especificado, el método devuelve la cadena que contiene la ruta y el nombre del archivo seleccionado por nosotros.

La sintaxis para este método es la siguiente: (todo es opcional)

GetOpenFilename ( FileFilter , FilterIndex , Título , ButtonText , MultiSelect )

En este ejemplo se muestra el cuadro de diálogo con el filtro de archivos definido como archivos de Word.

Sub SelectArchivo()

Dim RutaArchivo As String

    MsgBox ("Seleccione el archivo de Word que desee.")

    RutaArchivo = Application.GetOpenFilename _

    ("Documento de texto  (*.docx), *.docx")

On Error GoTo Control:

    MsgBox ("El archivo seleccionado es:  " & RutaArchivo)

Control:

End Sub

Método GetSaveAsFilename:

El método GetSaveAsFilename es muy similar al GetOpenFilename muestra el cuadro de diálogo estándar de Guardar como y obtiene el nombre de archivo seleccionado por nosotros  sin guardar realmente ningún archivo.

La sintaxis para este método es la siguiente: (todo es opcional)

GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Como diferencia al anterior metodo la opción InitialFilename nos sugiere el nombre de archivo, si este argumento se omite, Microsoft Excel usará el nombre del libro activo.

Sub SaveArchivo()

Dim RutaArchivo As String

    RutaArchivo = Application.GetSaveAsFilename _

    (FileFilter:="Documento de Excel  (*.xlm), *.xlm")

On Error GoTo Control:

    ActiveWorkbook.SaveAs (RutaArchivo)

    MsgBox "El archivo :  " & RutaArchivo & _

    vbCrLf & " se ha guardado correctamente.", vbInformation

Control:

End Sub

Crear un menú contextual

Con Excel VBA podemos crear nuestro propio menú contextual y hacer que aparezcan como respuesta a un evento determinado. El código que presento a continuación  crea un sencillo menú con tres  elementos.

Sub CrearMenu()

    Set myBar = CommandBars.Add _

      (Name:="MyMenu", Position:=msoBarPopup, _

       Temporary:=True)

    Set myItem = myBar.Controls.Add(Type:=msoControlButton)

    With myItem

        .Caption = "&Formato Numero..."

        .OnAction = "ShowFormatNumber"

        .FaceId = 1554

    End With

       

    Set myItem = myBar.Controls.Add(Type:=msoControlButton)

    With myItem

        .Caption = "&Alineacion..."

        .OnAction = "ShowFormatAlignment"

        .FaceId = 217

    End With

    Set myItem = myBar.Controls.Add(Type:=msoControlButton)

    With myItem

        .Caption = "&Fuente..."

        .OnAction = "ShowFormatFont"

        .FaceId = 291

    End With

End Sub

CommandBars  representa la barra de menús y todas las barras de herramientas de Microsoft Office.

La propiedad OnAction Devuelve o establece el nombre de un procedimiento de Visual Basic que se ejecutará cuando hacemos clic al valor de un control de nuestro menú.

La función FaceId  establece el número de Id. de la imagen de un control de nuestro menú.

Después de crear nuestro menú contextual, podemos mostrarlo mediante el método ShowPopup.

El siguiente procedimiento localizado en el modulo de código de un objeto Worksheet, se ejecuta cuando hacemos clic con el botón derecho en una celda.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

CommandBars("MyMenu").ShowPopup

End Sub

Nos quedaría como la siguiente imagen:

Procesar serie de Archivos

Uno de los usos mas comunes que le damos a las macros es el de repetir una operación un determinado numero de veces. El ejemplo que exponemos a continuación nos muestra como manejar diferentes archivos almacenados en el disco.

Lo primero que hacemos es dar las especificaciones que deben de cumplir los archivos que queremos manejar, en este caso de texto.

Como nota importante, al iniciar el procedimiento y al no saber que cantidad de archivos nos puede dar la búsqueda, utilizamos la instrucción ReDim para redimensionar la matriz preservando (Preserve) los datos.

Sub ProcesoMatriz()

Dim ArchivoSpec As String

Dim i As Integer

Dim NombreArchivo As String

Dim ListaArchivos() As String

Dim ArchivosEncontrados As Integer

ArchivoSpec = ThisWorkbook.Path & "\" & "*.txt"

NombreArchivo = Dir(ArchivoSpec)

    If NombreArchivo <> "" Then

        ArchivosEncontrados = 1

        ReDim Preserve ListaArchivos(1 To ArchivosEncontrados)

        ListaArchivos(ArchivosEncontrados) = NombreArchivo

    Else

        MsgBox "No se encontraron archivos que coinciden con " & ArchivoSpec

        Exit Sub

    End If

   

    Do

        NombreArchivo = Dir

        If NombreArchivo = "" Then Exit Do

        ArchivosEncontrados = ArchivosEncontrados + 1

        ReDim Preserve ListaArchivos(1 To ArchivosEncontrados)

        ListaArchivos(ArchivosEncontrados) = NombreArchivo & "*"

        Loop

       

    For i = 1 To ArchivosEncontrados

        Call ProcesaArchivo(ListaArchivos(i))

    Next i

End Sub

La función Dir nos devuelve  el nombre de un archivo, directorio o carpeta que coincide con el patrón o atributo de archivo especificado.

En este caso, importamos el archivo e introducimos una serie de formulas de resumen que describen los datos del archivo.

Los archivos que cumplen la especificación dada se almacenan en la Matriz ListaArchivos, el procedimiento utiliza el bucle For – Next para procesarlos. Dentro del bucle llamamos al procedimiento ProcesarArchivo.

El procedimiento ProcesarArchivo  importa el archivo y luego inserta las cinco formulas.

Sub ProcesaArchivo(NombreArchivo As String)

Workbooks.OpenText Filename:=NombreArchivo

Range("D1").Value = "A"

Range("D2").Value = "B"

Range("D3").Value = "C"

Range("E1:E3").Formula = "=COUNTIF(A:A,D1)"

Range("F1:F3").Formula = "=SUMAIF(A:A,D1,B:B)"

End Sub

El método Workbooks.OpenText Carga y redistribuye un archivo de texto en forma de libro nuevo con una sola hoja que contiene los datos redistribuidos del archivo de texto.

Cualquier duda al respecto de lo comentado en el post no dudéis en exponerlo.

Función Shell de VBA

La función Shell de VBA nos facilita considerablemente el inicio de otros programas. En el siguiente ejemplo que presento a continuación nos permite iniciar la aplicación Command Prompt (cmd).

Sub AbrirAplicacion()

Dim programa As String

Dim Ide As Double

    On Error Resume Next

programa = "cmd.exe"

Ide = Shell(programa, vbNormalFocus)

    If Err <> 0 Then

        MsgBox "No se puede iniciar " & programa, vbCritical, " Error"

    End If

End Sub

La función Shell nos devuelve el número de identificación de la tarea para la aplicación. Posteriormente utiliza este número para activar la aplicación, el segundo argumento determina la forma de mostrar la aplicación, en este caso vbNormalFocus  hace que la aplicación se ejecuta normalmente y reciba el enfoque.

Si la función Shell no funciona genera un error, por eso empleamos una declaración On Error para mostrar el mensaje de “No se puede iniciar cmd.exe”.

Es importante saber que mientras se ejecuta la aplicación que hemos iniciado si el procedimiento tiene mas instrucciones estas continúan ejecutándose, es decir no tiene un intervalo de tiempo constante entre cada evento.