Consolidación y uso compartido de datos

Presentación del ejemplo

En informática, la consolidación permite agrupar datos procedentes de diferentes fuentes para obtener un informe estructurado.

En el ejemplo siguiente se consolidarán primero varios orígenes de datos en uno y, a continuación, se trabajará en la tabla consolidada para extraer información clave.

Se trata de un grupo inmobiliario compuesto por dos agencias separadas que se encuentran en Madrid y Sevilla. Aunque similar en su organización, la introducción de las transacciones actuales no se realiza de la misma manera para las dos agencias. Sin embargo, ambas usan un archivo de Excel que rastrea su actividad.

Por lo tanto, el objetivo será consolidar estas dos fuentes de datos en un único archivo que contenga toda la información. La agencia inmobiliaria también desea ofrecer una tabla resumen de su actividad.

En este ejemplo no se proporciona una interfaz de usuario. El resultado se presenta en forma de una fuente de datos consolidada.

Descripción de los libros

Este ejemplo se presenta con tres libros independientes:

Operación con hojas y libros

La operación con hojas y libros consiste en operar con variables de objetos.

Administración de la aplicación Excel

La importación consistirá en abrir libros de Excel (Sevilla y Madrid) y luego seleccionar las hojas necesarias. Toda esta información se almacenará en variables de tipo objeto.

Dim ExcApp As Excel.Application 'Variable de administración de la aplicación 
Dim WB As Excel.Workbook 'Variable libro 
Dim WS As Excel.Worksheet 'Variable hoja de cálculo 

Seleccionar y abrir un libro de Excel

Método GetOpenFileName

Para seleccionar el archivo de Excel, puede utilizar el método GetOpenFileName de la clase Application, que abre una ventana de selección de archivos. Este método devuelve la ruta de acceso de la aplicación seleccionada y se puede utilizar para abrir el archivo seleccionado.

Nombre_Archivo = Application.GetOpenFilename("Archivos de Excel (*.xlsm),  
*.xlsm") 'filtro en los archivos de Excel 
If Nombre_Archivo <> False Then 
'Abrir archivo 
Else 
Msgbox ("Archivo no seleccionado") 
End if 

Método FileDialog

Sin embargo, el ejemplo propuesto utilizará otro método más completo: Application.FileDialog, que permite cualquier tipo de intercambio con directorios y archivos: 

Dependiendo del argumento asociado con el método FileDialog, el cuadro de diálogo tendrá una forma diferente:

Por ejemplo:

With Application.FileDialog(msoFileDialogFolderPicker) 
'Establecer el cuadro de diálogo para seleccionar un directorio. 
        .Title = "Seleccionar el directorio" 'Agregar un título al cuadro 
de diálogo 
        .Show 'Visualizar la ventana 
If .SelectedItems.Count > 0 Then 
    Msgbox .SelectedItems(1) 'Visualizar el primer elemento seleccionado. 
End If 

Estructura del código

El código se realizará dentro de un único procedimiento que se utilizará una sola vez para recuperar los datos de ambas hojas.

Por lo tanto, es necesario crear un procedimiento que almacenará todo el procesamiento y se llamará InsercionDatos.

Option Explicit 
Sub InsercionDatos 
'El código se insertará aquí 
End sub 

Declaración de variables de hoja y libro

Para realizar este ejemplo, primero tendrá que crear variables de objeto para almacenar y trabajar con hojas y libros.

Será necesario tener seis variables:

Definir las variables 
Dim WBSevilla As Excel.Workbook 
Dim WBMadrid As Excel.Workbook 
Dim WBFinal As Excel.Workbook 
Dim WSSevilla As Excel.Worksheet 
Dim WSMadrid As Excel.Worksheet 
Dim WSFinal As Excel.Worksheet 

Dim WBFinal, WBSevilla, WBMadrid As Excel.Workbook 
Dim WSFinal, WSSevilla, WSMadrid As Excel.Worksheet 

Para asignar valores a las variables, es necesario utilizar la palabra clave Set, que se utiliza para asignar una referencia al objeto. En este caso, WBFinal hará referencia al libro de trabajo actual: ThisWorkbook.

Finalmente, la hoja WSFinal será la hoja Datos de WBFinal.

Set WBFinal = ThisWorkbook 
Set WSFinal = WBFinal.Sheets("Datos") 

Configuración del cuadro de diálogo para abrir el archivo

El primer paso es abrir un cuadro de diálogo para seleccionar los dos archivos que desea importar.

Para definir las características del cuadro de diálogo, llame al método FileDialog de la clase Application con el argumento msoFileDialogFilePicker para especificar que se trata de una selección de archivo.

En primer lugar, debe crear el objeto correspondiente a este cuadro de diálogo y, a continuación, definir sus características.

Almacenaremos el archivo de Sevilla como una variable o, más concretamente, asignaremos a la variable WBSevilla una referencia a la instancia de Excel.Workbook.

Para simplificar el código, utilice la estructura With ... End with para no reescribir sistemáticamente el código del cuadro de diálogo:

With Application.FileDialog(msoFileDialogFilePicker) 
End with 

A continuación, defina las características del cuadro de diálogo como su título...

Presentación del ejemplo

El objetivo de este ejemplo es proponer una solución que permita a ambas agencias inmobiliarias introducir datos. El problema es que este archivo de Excel no está destinado a ser mantenido por una sola agencia, sino que debe ser accesible y modificable por ambas agencias; quizás, al final, por una multitud de ellas.

Presentación de los libros y herramientas utilizadas

A efectos de este ejemplo, se utilizará el archivo generado en la primera parte de este capítulo, ya que contiene los datos introducidos en las agencias, aunque se le han aplicado algunas mejoras, incluida una pestaña Configuración. El archivo en el que nos vamos a basar es Enunciado_6-DEF.xlsm.

Este ejemplo requerirá una cuenta en Microsoft OneDrive (https://onedrive.live.com/) y, para la última parte, es necesario tener Outlook 2021 u Outlook Microsoft 365 instalado en su ordenador. Si no dispone de Outlook, el código se puede adaptar fácilmente para otras soluciones.

La cuenta de OneDrive le permitirá crear un formulario de Excel en línea cuyos valores se almacenarán dentro de un archivo.

La aplicación Outlook le permitirá enviar un correo electrónico a partir de los datos contenidos en Excel.

Funcionalidades

Las funcionalidades que se abordarán son:

Formulario de tabla

La opción Formulario es una característica de Excel para agregar/editar/eliminar datos en una serie de datos. Normalmente, se usa con tablas, pero esta funcionalidad también se puede usar con una simple serie de datos.

Ventajas

La ventaja de esta funcionalidad es que genera un formulario de entrada y edición de una manera sencilla, simplemente con un clic. La edición, la adición y la eliminación son de fácil acceso e incluso es posible buscar un elemento.

Desventajas

La principal desventaja es la falta de posibilidades de ayuda a la hora de introducir datos.

Es imposible calificar los datos que se han de insertar. Además, si ha aplicado restricciones a los datos (pestaña Datos - Validación de datos), es posible que no pueda insertar los datos con el formulario. De hecho, si el valor introducido no se corresponde con el valor esperado, no se insertará toda la fila.

¿Cómo insertar el formulario?

Encontrará en la hoja dos tablas idénticas en A1:C9 y H1:J9. Cada tabla tiene tres columnas donde aparecen los nombres, el sexo y el nombre del equipo.

La primera tabla (rango A1:C9) representa un rango de datos, no está declarada como tabla de Excel. Ninguna casilla conlleva ninguna restricción. La segunda tabla (rango H1: J9) es una tabla de Excel. Las columnas relativas al sexo y al nombre del equipo son obligatorias: el usuario debe elegir uno de los valores.

Obtendrá la siguiente vista:

Aparecerá la siguiente ventana; haga clic en Aceptar para mostrar el formulario.

El formulario se muestra de la siguiente manera:

1. El botón Nuevo crea un registro nuevo vacío.

2. El botón Eliminar elimina el registro actual.

3. Los botones Buscar anterio/Buscar siguien permiten navegar a través de los registros.

4. El botón Criterios permite introducir criterios para mostrar solo los registros que cumplen con estos criterios.

Se ha aplicado una restricción a la tabla: solo se aceptan los valores "Hombre" y "Mujer" para...

Crear un formulario de tipo autocompletar para facilitar la introducción de datos

El formulario de entrada permitirá la introducción de datos en una tabla sin tener que usar un formulario VBA.

Mostrar el formulario

El formulario aparece de la siguiente manera:

Modificar un dato

El agente Benito le informa de que finalmente no se realizó una venta después de un problema de última hora en la concesión del préstamo. Sabe que la oferta apareció el 15/12/2021.

Búsqueda de datos

El agente Cruz está buscando una de sus ventas en la que se ha colado un error: el cliente disponía de parking. Recuerda que fue la venta de una villa de más de 350 000 €. Utilice la herramienta para realizar la búsqueda.

Crear una encuesta compartida a través de OneDrive y entregarla

Como el formulario permite introducir datos en local, la encuesta parece satisfacer en todos los puntos la necesidad de compartir el mismo formulario de datos con las agencias. La encuesta permitirá a las agencias tener un enlace web para introducir sus datos, que se consolidarán en el mismo archivo.

Crear una cuenta de OneDrive

La interfaz de OneDrive aparece así:

Crear la encuesta

Aparece un formulario sin título que le ofrece crear su encuesta de inmediato.

El resultado es el siguiente:

Los detalles de la pregunta de tipo Opción se muestran de la siguiente manera:...