Vínculos externos rotos al mover el libro de destino en Excel

Cuando Excel no puede actualizar los vínculos externos, nos muestra el siguiente mensaje alertando de la imposibilidad de actualizarlos. 

Podemos hacer clic en Modificar vínculos para editar las rutas de los libros de origen y en principio el problema estaría resuelto. No obstante, cuando los libros de destino y origen están ubicados en la misma carpeta, si copiamos el libro de destino a otra carpeta, se romperán los vínculos. En tal caso tendremos que editar los vínculos de nuevo.

¿Cómo es posible? Están correctamente vinculados y en ubicación indicaba la ruta completa del libro de origen. Si no cambia la ubicación del libro de origen de lugar, ¿por qué se rompen los vínculos?

La explicación se encuentra en cómo Excel almacena las rutas de acceso al vínculo. Excel básicamente sigue una serie de reglas para almacenar la ruta de acceso a un libro vinculado, guardando los vínculos de forma relativa siempre que sea posible. Es decir, no guarda la ruta completa al libro de origen: "si el archivo vinculado y el archivo de datos de origen están en la misma carpeta, se almacena sólo el nombre de archivo."

Al mover el libro de destino, se rompen los vínculos con los libros de origen que estaban en la misma carpeta del libro de destino. Excel busca esos ficheros en la nueva ruta del libro de destino y, al no encontrarlos, no puede actualizarlos. Para solucionarlo, tenemos dos opciones:

1. Copiamos esos libros de origen en la nueva ubicación del libro de destino para que continúen juntos.
2. Con el libro de destino abierto, guardar como en la nueva ubicación y se actualizarán los enlaces adecuadamente.

Por ello es recomendable separar los libros de origen y de destino. Así, si necesitáramos mover el libro de destino a otra ubicación, no tendremos problemas al actualizar los vínculos. Por ejemplo:

Por un lado, los ficheros de origen en la carpeta de su semana correspondiente. Y por otro, fuera de la carpeta, los ficheros de destino. Cuando los ficheros de destino no necesiten actualizarse más, podremos ubicarlos en su carpeta semanal.

Quitar o borrar esquema en Excel con VBA

Recientemente me he encontrado con un libro en el que varias hojas contenían un esquema de filas. Podemos quitar el esquema yendo a la ficha Datos grupo Esquema, haciendo clic sobre Borrar esquema.

Para evitar repetir manualmente el proceso para cada hoja, he creado el siguiente código que desagrupa y borra el esquema de filas y columnas de todas las hojas de un libro de Excel, incluidas las ocultas:

Sub Desagrupar()

Application.ScreenUpdating = False
Dim ws As Worksheet
    For Each ws In Sheets
    ws.Activate
    Cells.ClearOutline
    On Error Resume Next 
    Next
Application.ScreenUpdating = True

End Sub 

La línea On Error Resume Next evita que la ejecución del código se detenga en el caso de que, por ejemplo, la hoja esté protegida.

Buscar y reemplazar cadenas de texto con VBA

Para reemplazar puntualmente texto y números en una hoja de Excel, en la pestaña Inicio, en el grupo Edición, hacemos clic en Buscar y seleccionar. O bien CTRL+L. Si tenemos que reemplazar así varias cadenas de texto el procedimiento es tedioso y lento.

Una manera de acelerar y automatizar el mismo es mediante VBA, creando una función definida por el usuario. Veamos un ejemplo:

En la tabla anterior, columna A, tenemos un campo fecha descargado de un sistema que las procesa en inglés. Cuando tratamos de operar con las fechas nos devuelve un error porque Excel, con el idioma local en español, no reconoce las iniciales de los meses de enero (JAN), abril (APR), agosto(AUG), y diciembre (DEC). Necesitamos sustituir esas cuatro cadenas de texto. Para reemplazarlas creamos en un módulo de VBA, la siguiente función de usuario.

Public Function BuscarCadena(Reemplazarcadena As String) As String

Dim Originales As Variant, Sustituciones As Variant
Dim i As Long
Originales = Array("JAN", "APR", "AUG", "DEC")
Sustituciones = Array("ENE", "ABR", "AGO", "DIC")
BuscarCadena = Reemplazarcadena
    For i = 0 To 3 'Los subíndices de matriz empiezan en 0,
                   'por tanto Originales(0) = "JAN"[...] Originales(3)="DEC"
    BuscarCadena = Replace(BuscarCadena, Originales(i), Sustituciones(i), _
    compare:=vbTextCompare)
    Next

End Function

El resultado final, con los cambios resaltados en naranja, es:

En la celda C2 introducimos la función definida en VBA =BuscarCadena(A2) y arrastramos la fórmula hasta C13. Si queremos en lugar de 01-ENE-2013 de la columna C el formato de fecha corta (dd/mm/aaaa) de la columna D hay tres opciones:

1. Aplicar el formato en VBA

BuscarCadena = Format(Replace(BuscarCadena, Originales(i), Sustituciones(i), _
compare:=vbTextCompare), "dd/mm/yyyy")

2.Multiplicar por 1 la columna C. Al operar le cambia el formato a fecha corta.

3. Usar la función en D2 =FECHANUMERO(C1) y arrastrar hacia abajo el controlador de relleno. Nos devuelve el número de serie secuencial que representa una fecha determinada, 41275 para C1. A continuación, para darles el formato deseado, seleccionamos las celdas D2:D13. Y en la ficha Inicio, en el grupo Número, clic en la flecha situada en la esquina inferior derecha. En el cuadro de diálogo Formato de celdas, seleccionamos en la categoría Fecha el primer tipo.

También podemos acceder al cuadro de diálogo Formato de celdas presionando CTRL+1.

Insertar imágenes en una hoja de cálculo de Google Drive

En una entrada anterior vimos como insertar una imagen GIF en Excel. Insertar una imagen GIF en una hoja de cálculo en Google Drive es mucho más sencillo. Existen dos opciones, insertar una imagen en la hoja o en una celda.

Insertar una imagen en una celda

En la celda introducimos la fórmula =image("URL", 3). El parámetro 3 indica que se respete el tamaño original de la imagen. Adaptamos la altura y anchura de la celda hasta que aparezca íntegramente.

Abajo la celda con la imagen insertada:

Insertar una imagen en la hoja

Hacemos clic en el menú desplegable Insertar y seleccionamos insertar imagen. En el menú tenemos diferentes opciones: Subir, Tomar una instantánea, URL, Albumes, Buscar. En nuestro caso vamos a elegir URL, pegamos la ruta de la imagen elegida y presionamos seleccionar.

El tamaño máximo de cada imagen que podemos insertar es de 2MB a día de hoy. El resultado sería:

Entradas relacionadas:

Insertar una imagen GIF en Ms Excel

Incompatibilidad de Excel con consulta de Access usando NZ

En la entrada anterior tratamos el cálculo de expresiones con valores nulos en Access. Una consecuencia imprevista al usar la función NZ() en una consulta de Access es que Excel no permite usar como origen de datos consultas que incluyan dicha fórmula. Si tratamos de importarla, y vamos a la ficha Datos, grupo Obtener datos externos y clic en Desde Access, la consulta no aparecerá listada en el cuadro de diálogo Seleccionar tabla.

Si tratamos de importarla De otras fuentes, Desde Microsoft Query listará la consulta pero al tratar de incorporar sus campos no dará un error.

Se me ocurren varias soluciones al problema:

1.Transformar la consulta de selección y crear una tabla con los resultados. El inconveniente es que crea una tabla innecesaria que duplica la información contenida en la tabla original.

2. Utilizar otra fórmula alternativa a NZ. Usamos la fórmula SiInm y EsNulo para comprobar si el campo es nulo. Si es verdad que devuelva un cero y, si es falso, el campo.

CVble: nz([Coste_variable])  'Expresión con NZ
CVble: SiInm(EsNulo([Coste_variable]);0;[Coste_variable]) 'Sin función NZ

SQL:

IIf(IsNull([Coste_variable]),0,[Coste_variable]) AS CVble

3. Diseñar una tabla donde sea obligatoria la entrada de datos en el campo. Evitaremos nulos pues nos forzará a introducir un valor, p. ej., ceros en lugar de nulos. Podemos modificar una tabla existente haciendo clic con el botón derecho sobre la tabla y clic sobre Vista Diseño. Elegimos el campo deseado y en la propiedad Requerido seleccionamos Sí:

4. Crear una consulta de actualización para sustituir los valores nulos por ceros, y a continuación cambiar el diseño de la tabla para que ese campo sea requerido (ver solución anterior).

En SQL:

UPDATE Costes SET Costes.Coste_variable = 0
WHERE (((Costes.Coste_variable) Is Null));

Dejando de lado la primera opción por ineficiente, la número dos es la más rápida y recomendable si disponemos de poco tiempo. Las soluciones tres y cuatro son complementarias, nos permiten solucionar definitivamente el problema en la base de datos con nulos.

En general, y salvo que sea absolutamente necesario, en el diseño de la tabla debemos prevenir la existencia de valores nulos siguiendo los pasos de la solución tres. De esta manera, con un buen diseño inicial de nuestra base de dato, podemos evitar futuros comportamientos indeseados y ahorrarnos mucho tiempo.

Entradas relacionadas:

Cálculo de expresiones con valores nulos en Ms Access

Cálculo de expresiones con valores nulos en Ms Access

Un error frecuente entre usuarios principiantes es incluir en expresiones o fórmulas campos que incluyen algún valor nulo. Y en Access cuando los cálculos incluyen un valor nulo, el resultado es nulo:

En la columna CTotal vemos el cálculo del coste total para los tres primeros conceptos es correcto, pero no para el último, devolviendo un nulo en lugar de 500. Para evitar este comportamiento causado por los nulos usamos la función NZ(). Tenemos dos opciones, o bien en cada campo que pudiera contener nulos introducimos la función NZ, o bien en el total alrededor de cada campo sumado:

Si queremos que CVble muestre ceros en lugar de nulos: CVble: nz([Coste_variable];0):

Entradas relacionadas:

Incompatibilidad de Excel con consulta de Access usando NZ

Seleccionar objetos y panel de selección en Excel

En Excel cuando seleccionamos una forma o un objeto tenemos dos opciones:

1. Mantener presionada la tecla MAYÚS o CTRL mientras hacemos clic en los objetos.
2. En la ficha Inicio, en el grupo Edición, y clic en Seleccionar objetos.

A continuación puedes ver los dos métodos:

El primer método no es práctico cuando tenemos multitud de objetos. Además corremos el riesgo de no seleccionar algún objeto del área deseada.

Estos dos métodos sólo permiten seleccionar las formas u objetos visibles en una hoja. En cualquier caso es recomendable activar el panel de selección para ver la lista de los objetos de la hoja y su visibilidad.

Desde el panel de selección podemos cambiar el nombre, ordenar, mostrar, ocultar y seleccionar varios objetos (CTRL presionado mientras hacemos clic en los objetos). El panel se puede desanclar y adaptar su tamaño:

Como extra para finalizar, un atajo para seleccionar todos las formas u objetos de una hoja: seleccionamos cualquier objeto y presionamos CTRL+MAYÚS+Barra espaciadora

Insertar una imagen GIF en Ms Excel

Para insertar una imagen GIF en Excel, vamos a la ficha Programador y hacemos clic sobre Insertar y luego sobre el icono Más controles:

En el desplegable seleccionamos Microsoft Web Browser y presionamos Aceptar:

A continuación con el cursor crea un marco para tu imagen. Luego podrás ajustar su tamaño al de la imagen. Sobre el objeto creado, haz clic con el botón derecho y luego sobre Ver código.

Inserta el siguiente código, con la ruta completa de la imagen.

Private Sub Worksheet_Activate()
WebBrowser1.Navigate "C:\Users\nubededatos\Desktop\insertarimagen.gif"  
End Sub 

También puedes indicar URL de la imagen:

Private Sub Worksheet_Activate()
WebBrowser1.Navigate "http://kevinburg.com/tumblr/fm2ugifs/train-repeat-615.gif"
End Sub

Finalmente en la ficha Programador desactiva el Modo diseño, haciendo clic sobre el mismo.

Para ajustar el tamaño del marco de la imagen activa de nuevo el modo diseño y arrastra los controladores de tamaño redondos sin colorear que aparecen alrededor del objeto.

Un ejemplo del resultado final. Una imagen gif con la evolución del ratio de fertilidad y la esperanza de vida:

Google's Public Data Explorer - World Bank

Entradas relacionadas:
Insertar una imagen GIF en Ms Excel

Generar campo autonumérico aleatoriamente en Ms Access

En Ms Access los nuevos valores de un campo autonumérico pueden ser generados incrementalmente (por defecto) o aleatoriamente. Para cambiar el modo de generar los nuevos valores, en la vista diseño de la tabla, seleccionar en el desplegable Nuevos valores la opción Aleatoriamente.

Access nos avisa de la irreversibilidad del cambio:

En la tabla, cada vez que comencemos a escribir un nuevo registro se generará un número aleatorio entre -2.147.483.648 y 2.147.483.647.

Como es natural, los números aleatorios asignados a los registros son fijos y no se recalculan (p. ej.: como al usar la función ALEATORIO en Excel). Como mencionamos antes, no es posible revertir la manera de generar nuevos valores de un campo autonumérico de aleatorio a incremental.

En tal caso, deberíamos eliminar dicho campo autonumérico y crear uno nuevo. Si es posible cambiar de incremental a aleatorio.

Entradas relacionadas

• Números aleatorios de una lista o rango de opciones
• Consulta para generar una muestra aleatoria en Ms Access.
• Generar números aleatorios entre dos valores en Ms Access
• Generar números aleatorios entre dos valores con decimales

Consulta para generar una muestra aleatoria en Ms Access

En la entrada anterior vimos como generar números aleatorios con la función NúmAleat. Con esa función podemos generar una muestra aleatoria de nuestros datos.

Creamos una consulta, agregamos los campos deseados, en el ejemplo todos los campos de TuTabla.* Añadimos un campo con la función NúmAleat basado en un campo numérico de la tabla, ordenamos por este campo, seleccionamos en Devuelve: el número de registros de la muestra o porcentaje del total y ejecutamos la consulta.

En SQL, la sintaxis sería:

SELECT TOP 100 TuTabla.*
FROM TuTabla
ORDER BY Rnd([campo numérico]);

Si solamente tuviéramos campos de texto, usamos la función Longitud (Len en inglés) para que nos devuelva como valor el número de caracteres de la cadena de texto. La consulta sería:

En SQL:

SELECT TOP 100 TuTabla.*
FROM TuTabla
ORDER BY Rnd(Len([campo de texto]));

Es importante señalar que la función NúmAleat con un argumento igual a cero devuelve el último número generado. Y si es negativo, repite cada vez el mismo número aleatorio para ese valor.

La solución sería usar la función Abs, que devuelve el valor absoluto de un número:
NúmAleat(Abs([campo numérico]))

En SQL:

SELECT TOP 100 TuTabla.*
FROM TuTabla
ORDER BY Rnd(Abs([campo numérico]));

Otra alternativa sería usar de nuevo la función Longitud como vimos más arriba, en este caso con un campo numérico. Así forzamos a que nos devuelva un número mayor que cero,  aunque tenga un cero o un número negativo.

En SQL:

SELECT TOP 100 TuTabla.*
FROM TuTabla
ORDER BY Rnd(Len([campo numérico]));

Entradas relacionadas

• Muestra aleatoria en Excel
• Números aleatorios de una lista o rango de opciones
• Generar campo autonumérico aleatoriamente en Ms Access
• Generar números aleatorios entre dos valores en Ms Access
• Generar números aleatorios entre dos valores con decimales