Una alternativa al control Calendario en Excel 2010 – Monthview

Como mencionamos en el pasado el control Calendar (mscal.ocx) fue removido del paquete Office 2010. Esto genera problemas en aplicaciones y modelos de Excel que fueron desarrollados en versiones anteriores y queremos usarla en Excel 2010.

En la nota mencionada sugerimos tres alternativas posibles: usar el Data Picker, el complemento desarrollado por Sam Radakovitz o descargar e instalar el control. Esta última alternativa implica tener que registrar el control para poder usarlo. El problema con esta última solución es que no podemos registrar el control en Windows 8.

Para los que, como a mí, no les gusta el Data Picker, existe la posibilidad de usar el control Monthview (para las versiones 2007 y 2010 de Excel).

¿Cuándo queremos usar el control? Cuando necesitamos asegurarnos que el usuario ingrese fechas en el rango y en el formato necesarios, librándolo al mismo tiempo de tener que ingresar los separadores (”/” o “-“) de la fecha.

En esta nota mostraré como implementar un modelo sencillo: al elegir una celda determinada aparece el calendario y al elegir una fecha en él, ésta aparece en la celda y el calendario desaparece

Paso 1 – Crear un Userform

En el editor de VB (Alt+F11) creamos un userform que contendrá el control

Cambiamos la propiedad Name a “UfFecha” (no es indispensable pero es una buena práctica) y cambiamos Caption a “Elija una fecha”.

Paso 2 - Insertar el control Monthview

Apuntamos al cuadro ToolBox y abrimos el menú contextual con un clic en el botón derecho del mouse

Elegimos al opción Aditional Controls y marcamos el Microsoft Monthview Control

Este aparecerá ahora en la parte inferior del cuadro ToolBox. Con un clic lo seleccionamos y lo insertamos en el Userform

Paso 3 – Definiciones del control

Al seleccionar el control podemos ver el cuadro de propiedades en el editor. Esto nos permite definir su apariencia y comportamiento. Por ejemplo, definimos la propiedad ShowTodat como True, de manera que al abrirse el control muestre la fecha corriente.

Paso 4 – Códigos de los controles.

Ahora necesitamos escribir los códigos que manejen el uso del control. En nuestro modelo queremos que cuando el usuario seleccione la celda C3 (hemos creado un nombre definido que se refiere a la celda: “clFecha”) aparezca el calendario. Esto lo hacemos usando el evento Worksheet_SelectionChange de la hoja

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("clFecha")).Address = Range("clFecha").Address Then
        ufFecha.Show
    End If

End Sub

El código lo ponemos en el módulo de la hoja (abrimos el módulo de la hoja con la opción Ver Código del menú contextual que se abre al hacer clic derecho en la pestaña de la hoja o con un doble clic sobre el icono de la hoja en el editor de Vb)

Ahora necesitamos un código para pasar el valor elegido en el calendario a la celda

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    ActiveCell.Value = DateClicked
    Unload ufFecha
End Sub

Este código va en el módulo del Userform que se abre haciendo un doble clic en el Userform (o apretando Ctrl+Tab hasta llegar al módulo)

Para que el control aparezca junto a la celda de la fecha y no en el centro de la hoja, agregamos el evento UserForm_Activate

Private Sub UserForm_Activate()
    With Me
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 150
    End With
End Sub

El modulo del Userform se verá así

Un tip para el final: si hacemos unn clic sobrel el nombre del mes en el control aparecerán unas flechas que nos permiten saltar de año en año

El archivo con el ejemplo puede descargarse aquí.

Intercalar columnas y filas en Excel – un atajo

A menudo surge la necesidad de intercalar columnas o filas en una tabla de Excel. Por ejemplo, en esta tabla de ventas queremos agregar el segundo trimestre

Es decir, intercalar una columna entre B y C y otra entre C y D

Chandoo publica un atajo para realizar esta tarea con un solo clic:

Manteniendo la tecla Ctrl apretada seleccionamos la columna C y después la columna D (un clic para cada selección); abrimos el menú contextual (clic al botón derecho del mouse) y activamos Insertar

Lo mismo podemos hacer con las filas. Supongamos que queremos insertar filas entre Sucursal 1, Sucursal 2 y Sucursal 3; apretando la tecla Ctrl seleccionamos una después de la otra las filas 3, 4 y 5 y usamos el menú Insertar como en el caso anterior

Actualización selectiva de tablas dinámicas en Excel

Como bien sabemos, las tablas dinámicas de Excel no se actualizan automáticamente al cambiar los datos que las alimentan. Dada esta característica existe el riesgo de publicar informes erróneos si nos olvidamos de pulsar el botón Actualizar en el menú de Herramientas de tabla dinámica

En el pasado propuse una macro sencilla para asegurarnos que después de actualizar datos en una hoja, la tabla dinámica se actualice. Se trata de un método un tanto contundente, actualiza todo lo que se cruce a su paso: tablas dinámicas, tablas, etc.

Un lector me pide un método más sutil que permite actualizar únicamente las tablas dinámicas y deje todo lo demás intacto. Mi primer propuesta, que puse en un comentario en la nota en cuestión, fue este código

Sub actualizar_Tabla_Dinamica()
    Dim strPivotName As String

    strPivotName = Application.InputBox(prompt:="Que tabla actualizar?")

    On Error GoTo errCancel 'en caso de apretar Cancel
    ActiveSheet.PivotTables(strPivotName).PivotCache.Refresh

    Exit Sub

errCancel:
Exit Sub

End Sub


En una hoja con varias tablas dinámicas funciona así

El problema con esta macro es que debemos recordar el nombre de las tablas dinámicas. Como pueden ver, el diálogo que se abre tengo que introducir el nombre “TDin1” que identifica a la tabla en cuestión, lo cual puede conducir a errores. Y ya que estamos, unas palabras sobre los nombres de las tablas dinámicas.

Cuando creamos una tabla dinámica, Excel le asigna un nombre por defecto “TablaDinámicaX” donde X es un número de orden. Usando el menú de opciones de las tablas dinámicas podemos cambiar este nombre por algo más significativo (útil en particular cuando escribimos código)

Volviendo a nuestro tema, era obvio que había que escribir un código que permitiera al usuario elegir que tablas o tablas actualizar.

Par lograr esto ya no podemos depender de la función Input, que tiene muchas limitaciones. En su lugar usamos un ListBox que tiene la ventaja de poder realizar selecciones múltiples. Este objeto tenemos que ponerlo en un Userform y crear los eventos para accionarlo y cancelarlo.

El primer paso es crear una Userform donde ponemos los controles List Box y dos CommandButton

Un doble clic en el Userform nos lleva al módulo correspondiente donde ponemos los códigos para manejarlo

Private Sub cbtAceptar_Click()
    ufListaTablas.Hide
End Sub

Private Sub cbtCancelar_Click()
    Unload Me 'ufListaTablas
End Sub

Ahora, en un módulo común ponemos el código de la macro

Sub actualizar_Tabla_Dinamica()
    Dim pt As PivotTable
    Dim lbItemsCount As Integer
    Dim iX As Integer
  
    'poner los nombres de las tabla en un List Box
    With ufListaTablas.lbPivots
        .Clear
        For Each pt In ActiveSheet.PivotTables
            .AddItem pt.Name
        Next pt
        lbItemsCount = .ListCount
    End With
  
    'abrir el List Box para elegir la tabla a actualizar
    ufListaTablas.Show
  
    On Error GoTo errCancel 'en caso de apretar Cancel
  
    With ufListaTablas.lbPivots
    For iX = 0 To lbItemsCount - 1
        If .Selected(iX) = True Then
            ActiveSheet.PivotTables(.List(iX)).PivotCache.Refresh
        End If
    Next iX
    End With
    
    Unload ufListaTablas
  
    Exit Sub
  
errCancel:
Exit Sub

End Sub


El ListBox muestra las tablas dinámicas de la hoja activa y nos permite elegir cuáles actualizar.
Para que la macro pueda usarse en cualquier cuaderno abierto sugiero guardarla en el cuaderno Personal y crear una icono en la barra de herramientas de acceso rápido.

El cuaderno con el ejemplo puede descargarse aquí.

Evaluación de Able2Extract – convertir PDF a Excel

La gente de Investintech me pide hacer una evaluación de la herramienta Able2Extract, para lo cual tuvieron la gentileza de proveerme con una copia de la aplicación.

Mi evaluación se limita a las primeras impresiones del uso de la herramienta para extraer datos de documentos PDF a Excel. Empecemos por activar el programa

Able2Extract tiene un sistema de ayuda incorporado que resulta muy útil en los primeros usos.

Como pueden apreciar en la imagen, Able2Extract puede convertir archivos PDF a un buen número de formatos: Word, PowerPoint, HTML y más.

Todo lo que hay que hacer es abrir el archivo PDF que queremos convertir o del cual queremos extraer datos, señalar la opción de selección (Select), todo el documento a algún área, y finalmente el tipo de archivo (Excel, Word, etc.). La posibilidad de elegir un área determinada resulta muy útil cuando queremos extraer una tabla rodeada de texto, por ejemplo.

Una vez que pulsamos el icono de la opción podemos elegir el tipo de conversión: automática o definida por el usuario (custom).

En mis pruebas la conversión fue rápida y exacta.

Otras características importantes son la capacidad de procesar lotes de archivos (batch) y el uso de plantillas (template). El uso de plantillas es útil cuando volvemos sobre la misma conversión con frecuencia. Para usar plantillas debemos primero crearlas (usando la opción “custom”) y guardarlas.

El programa contiene un menú de opciones (View-Options) que permite definir y manejar los parámetros de conversión. Por ejemplo forzar el tipo de archivo Excel en la conversión (.xls o ,xlsx, útil cuando tenemos instaladas en nuestra máquina más de una versión de Office).

En resumen, de todos los programas de conversión de archivos PDF que he probado, Able2Extract8 es en mi opinión el más completo y funcional. Sin lugar a dudas, una gran ayuda para todo usuario de Excel que tenga que extraer datos de archivos PDF.

Investintech ofrece dos versiones de Able2Extract: Converter y Professional. Esta última permite procesar PDF escaneados. También se puede descargar una copia de evaluación gratuita, válida sólo por siete días.

Validación de direcciones de e-mail en Excel

Validación de datos es una herramienta excelente, indispensable diría, en todo modelo de Excel que requiera ingresar datos. Pero, como todo en este mundo, tiene sus limitaciones. Por ejemplo, ¿cómo comprobamos que el usuario ingrese direcciones válidas de correos electrónicos?

Si se tenemos una lista de direcciones válidas, nos basta con usar la opción Lista de validación de datos. Pero, ¿qué hacemos si no contamos con una lista de direcciones pero queremos asegurarnos que la sintaxis de la dirección es correcta? Es decir, abc123@xyz.com es una dirección válida, independientemente de que exista o no; pero abc..123@xyz no lo es).

La solución es usar expresiones regulares (regular expresions). La expresión regular es un patrón que nos permite describir un conjunto de texto sin enumerar sus elementos. No voy entrar en los detalles técnicos, que pueden leer en la nota del enlace, pero si señalar que estos patrones pueden ser usados en Vba. De esta manera podemos crear una función definida por el usuario para validar direcciones de correo electrónico.

La función para validar la sintaxis de una dirección de correo electrónico es

Public Function blnEmailValid(ByVal strEmailAdd As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        blnEmailValid = .Test(strEmailAdd)
    End With
End Function

Este código fue publicado por Vishesh en ExcelExperts.com.

En este ejemplo, ponemos la función en la celda D2 para evaluar la dirección ingresada en la celda C2; si la dirección es válida el valor en la celda D2 es VERDADERO, en caso contrario FALSO

También podemos usarla en Validación de Datos, usando el valor de la celda D2 en la opción Personalizada (en este ejemplo, ponemos la función en A2 que podamos hemos ocultado)