|
Vincular datos de Access a Excel. |
|||||
|
Jose Ignacio González Gómez Departamento de Economía Financiera y Contabilidad
1. Creación y Modificación del fichero de conexión ODBC.
2. Vincular datos de Access en una Hoja Cálculo Excel.
3. Diseñar una Tabla Dinámica en Excel basada en datos importados y
vinculados
4. Cambios en la base de datos de origen. Ubicación o nombre. 1 Creación y Modificación del fichero de conexión ODBC 1.1 Creación de conexiones y guardarlas El planteamiento del problema es el siguiente, en el directorio 0 Pruebas DB contamos con una base de datos denominada BD Precios y para la cual queremos definir una conexión de acceso a la misma tipo ODBC.
Figura 1
Para ello procederemos en primer lugar por ejecutar la aplicación Orígenes de datos (ODBC) contenida dentro de las opciones de Windows XP – Herramientas Administrativas. Esta aplicación nos permite la gestión y control de todas las conexiones del tipo ODBC, es decir todos los orígenes de datos con nombres de origen de datos (DSN) de usuario. De esta forma accedemos al siguiente cuadro de dialogo.
Figura 2
Nuestra intención es crear un fichero de origen de datos a nuestra base de datos de carácter general, es decir que puedan acceder todos aquellos usuarios que tengan acceso al mismo y deseamos guardar el mismo en el directorio donde tenemos localizados nuestra base de datos (0 Pruebas BD), para ello vamos a la opción agregar.
Figura 3
A partir de este momento comenzamos a configurar la conexión seleccionando en primer lugar el controlador para comunicarnos con la misma, en nuestro caso Microsoft Access Driver, tal y como vemos en la Ilustración 4.
Figura 4 Pulsando en siguiente nos aparece un nuevo cuadro de dialogo para escribir el nombre del conector y el sitio donde queremos almacenarlo por defecto es en Data Source, que si queremos cambiarlo tendremos que seleccionar examinar para elegir el nombre y lugar alternativo donde queremos guardar el fichero de conexión.
Figura 5
Figura 6
De esta forma nos aparece una nueva ilustración similar a la Ilustración 5 pero adaptada a nuestros requerimientos.
Figura 7
Pulsando sobre el botón siguiente nos aparece una pantalla de confirmación de la creación de la conexión deseada (ver Ilustración 8)
Figura 8
De esta forma ya hemos creado la conexión ahora al pulsar finalizar comenzamos la configuración de la misma, es decir definiendo a que base de datos queremos conectar.
Figura 9
Para ello en la Ilustración 9 pulsamos sobre el botón Seleccionar y escogemos nuestra base de datos.
Figura 10
Y finalmente aceptamos con lo cual tendremos creada y configurada
nuestra conexión,
Figura 11
Este archivo según la definición creada estará ubicado en el directorio establecido anteriormente.
Figura 12
Abrimos la BD a la que queremos crear la conexión y elegimos el elemento (tabla o consulta a la que queremos conectarnos. Debemos tener en cuenta que posteriormente podremos modificar la conexión así como a los elementos a los que nos queremos conectar, por tanto no nos preocupemos ahora en ese detalle y centremos nuestra atención en la creación de la conexión. 1.2 Recuperación y/o modificación de conexiones ODBC Para acceder y/o modificar la conexión porque bien la base de datos ha cambiado de nombre o ubicación o porque hemos cambiado el nombre del conector basta con volver a la opción Orígenes de Datos ODBC (Ilustración 2) y recuperar el fichero de conexión.
Figura 13 Pulsando sobre configurar podemos cambiar los parámetros de la misma
(ver Ilustración
Figura 14 2. Vincular datos de Access en una Hoja Cálculo Excel 2.1 Importar y vincular datos entre la BD Access y Excel Lo primero que vamos a realizar es importar y vincular en una hoja de calculo de un libro Excel los datos contenidos en una tabla o consulta, para ello abierto el libro Excel y situados en la hoja de calculo donde deseamos vincular los datos de la BD Access, vamos a la opción del programa Menú Datos > Obtener Datos Externos > Importar datos, tal y como se muestra en la Ilustración 15
Figura 15
A continuación nos solicita seleccionar el fichero de origen de datos, en este caso buscaremos el fichero ODBC de conexión que hemos guardado y el cual contiene los parámetros necesarios para enlazar con la base de datos.
Figura 16
De esta forma se abre la conexión a la base de datos configurada y nos presenta las distintas tablas y consultas que contiene la misma para que podamos elegir que tabla o consulta deseamos y que campos de la misma queremos tener a disposición en nuestra hoja de calculo y vinculada con la fuente de la base de datos.
Figura 17
Para finalizar se nos presenta un nuevo cuadro de dialogo que nos pregunta que deseamos hacer con los datos conectados a la base de datos, en nuestro caso seleccionaremos la opción primera “Devolver datos a Microsoft Excel” que nos permite tener los mismos a disposición y vinculados con la fuente, es decir con la base de datos. Seleccionamos el lugar (hoja) y sitio donde deseamos importarlo.
Figura 18
El resultado de todo ello es los resultados obtenidos la Ilustración 19.
2.2 Actualizar datos vinculados y modificar la consulta a la base de datos y a los datos vinculados. Cualquier valor que se añada o modifique en la base de datos puede ser incorporada a los datos de la hoja de calculo a través de la opción “Actualizar Datos” tal y como se muestra en la Ilustración 19.
Figura 20
También contamos con la opción Modificar consulta que nos permite actualizar los campos disponibles en el caso de haber modificado el diseño de la misma en la base de datos e incorporarlos a los datos vinculados de Excel, todo ello a través del cuadro de dialogo presentado en la Ilustración 20. Por ejemplo si en la base de datos creamos un nuevo campo o un nuevo cálculo esto lo podremos tener disponible en la hoja de cálculo, modificando solo la conexión. 2.3 Propiedades del Rango de Datos Vinculados en Excel. Otra opción interesante a tener en cuenta y presente también en la Ilustración 19 es la de configurar las propiedades de conexión con la base de datos.
Figura 21
En esta opción se nos presenta entre otras posibilidades la de actualizar los datos de forma automática o cada cierto tiempo, establecer una contraseña para la conexión, ajustar los anchos de columnas, etc.
3 Diseñar una Tabla Dinámica en Excel basada en datos importados y vinculados con una base de datos Access 3.1 Crear la Tabla Dinámica en Excel vinculada a una base de datos Access Ya tenemos a disposición los datos necesarios para crear nuestra tabla dinámica y vinculados estos a la base de datos de origen lo que garantiza una continua actualización de la fuente de origen.
Figura 22
De esta forma vamos diseñar y configurar la tabla dinámica desde nuestra hoja de calculo, para ello recuperamos el libro Excel donde tenemos vinculados los datos y en una nueva hoja en blanco de este libro vamos a la opción Datos > Informe de tablas y gráficos dinámicos.
Figura 23
Para ello en primer lugar debemos indicar donde están los datos que queremos tener como base para nuestra tabla dinámica, en este caso elegiremos los datos vinculados y finalmente donde queremos generar la tabla dinámica.
Figura 24
Figura 25
El resultado final es el tener a disposición el área de diseño de la tabla de dinámica en Excel.
Figura 26
3.2 Configurar los campos de la Tabla Dinámica en Excel Ahora simplemente arrastrando los campos disponibles al área de diseño vamos configurando así nuestra tabla, es decir como es habitual vamos arrastrando los campos disponibles a la tabla dinámica pudiendo configurar cada campo contenido en la TD de forma individualizada situándoos sobre el mismo y con el botón derecho escogemos la opción configuración de campo tal y como se muestra en la Ilustración 27.
Figura 27
De esta forma y para cada campo contenido en la tabla dinámica de Excel podremos cambiar el nombre con el que se reconoce, el valor que tiene de referencia, su formato, etc.
Figura 28
3.3 Configurar Opciones de la Tabla Dinámica. Retomando la Ilustración 27, otra de las opciones disponibles y que merecen nuestra atención es el acceso a las opciones de la tabla dinámica
Figura 29
Aquí podemos establecer los parámetros básicos de configuración
asociados a nuestra 3.4 Campo Fecha como campo de página en una TD. Nos puede interesar establecer un filtro fecha para seleccionar los registros a analizar en la tabla dinámica, por ejemplo, nos podría interesar filtrar los valores de la tabla dinámica siguiente por un rango de fechas predeterminadas.
Figura 30
Para ello vamos a utilizar el campo fecha y situarlo en el área de página de la TD pero antes vamos a configurar este campo fecha para tener varias opciones de selección. Lo primero que hacemos es arrastrar el campo fecha y situarlo en el área de fila, columna A tal y como se muestra en la Ilustración 31 y situado sobre el campo fecha y con el botón derecho accedemos a la opción Agrupar que nos permitirá agrupar los registros de este campo por varios criterios.
Figura 31
De esta forma hemos seleccionado la opción de agrupar los registros del campo fecha en días contando 150 días (5 meses x 30 días = 150 días). Esto nos permitirá seleccionar por este campo además por meses, trimestres y años. Al darle aceptar tendremos como la Ilustración 31 se ha transformado en la Ilustración 33.
Figura 33
Pero recordemos que el campo fecha nos interesa tenerlo a disposición como filtro de la TD para poder adaptar los resultados de la misma a los periodos deseados. Para ello basta con arrastrar los mismos a la zona campos de página de la TD, teniendo así como resultado la siguiente Ilustración.
Figura 34
Contamos con otra forma de limitar las opciones a representar los datos en la tabla dinámica respecto a un rango de fechas determinados. Para ello tomemos el siguiente ejemplo: En la siguiente tabla dinámica contamos con los valores filtrados por campo fecha, en concreto podemos limitarlo por Años, Trimestres o Meses.
Figura 35 Si deseamos filtrar por Meses, por ejemplo se nos desplegaría el siguiente cuadro de dialogo
Figura 36
Como podemos observar nos presenta la limitación que solo podamos escoger un mes, de los disponibles, si quisiéramos escoger unos determinados meses como representativos para nuestro análisis, por ejemplo Enero, Mayo y Agosto tendríamos que modificar las propiedades de ese campo fecha concreto.
Figura 37
Para ello nos situamos sobre el campo concreto y con el botón derecho accedemos a las propiedades del mismo, tal y como muestra la Ilustración 37 . Dentro de las opciones que se nos presenta seleccionamos en concreto
“Configuración de
Figura 38
En el nuevo cuadro de dialogo que se nos presenta, no interesa en
concreto el apartado inferior relacionado con Ocultar elementos, en este
caso seleccionaremos todos los meses excepto los de Enero, Mayo y Agosto
para que el análisis se centre en esa selección múltiple
Figura 39
Una cuestión que debemos tener en cuenta es que si la columna que se está tratando de agrupar tiene datos de tipo "diferentes", por ejemplo, cuando se quiere agrupar fechas, y hay un valor vació o con nos aparece el siguiente error “Imposible agrupar esta selección". 4 Cambios en la base de datos de origen. Ubicación o nombre. Problemas con tablas vinculadas. Si por cualquier motivo cambiamos el nombre o el lugar de ubicación de la base de datos, la conexión de los datos de Excel no funcionara y por tanto no se actualizara, tendremos que modificar los parámetros de conexión. En primer lugar es necesario cambiar los parámetros del fichero ODBC al que queremos conectar procediendo como hemos visto en el apartado 1.2 Recuperación y/o modificación de conexiones ODBC. Una vez hecho esto abrimos el origen de datos de Excel y pulsando sobre los registros de la base de datos accedemos a la opción de modificar la consulta. De esta forma intenta conectar con el origen de datos pero debido al cambio de ubicación o de nombre de la base de datos no es capaz de localizar por lo cual nos muestra el siguiente mensaje de error (Ilustración 41).
Figura 40
Figura 41
Figura 42
Si aceptamos sobre el cuadro de dialogo anterior (Ilustración 41), podremos acceder a modificar los parámetros de conexión con la base de datos que han sido modificados, en concreto pulsando sobre el botón de base de datos de la Ilustración 42 y seleccionamos la base de datos deseada. Posteriormente se nos abre la aplicación Microsoft Query y pulsamos el icono nueva consulta que nos permitirá redefinir la consulta de acceso a datos.
Figura 43
Nos volverá a pedir el origen de datos y la tabla o consulta así como sus campos, tal y como se muestra en la Ilustración 16 y en la Ilustración 17 y por tanto esta quedara otra vez definida correctamente.
Figuara 44
Puede que aún la conexión ODBC nos siga dando problemas, especialmente
en el caso de que tengamos tablas vinculadas, por tanto es necesario
realizar un ajuste y actualizar estas tablas vinculadas para ello abrimos
la base de datos original donde tenemos las tablas vinculadas y
atualizamos cada una de ellas. Otra forma de hacerlo seria a traves de la
opción del programa Herramientas/ Utilidades de las bases de datos
administración de tablas
Figura 45
Figura 46 Siempre nos deberíamos plantear que es mejor si vincular o exportar o importar de la base de datos.
|
|||||
|
Título:
Vincular datos de Access a Excel: Creación de una TD Excel basada en datos
de una BD Access |
|||||
