La función HIPERVINCULO con direcciones de correo electrónico

Cuando introducimos en una celda un texto con un patrón dirección de correo eléctronico, Excel crea un hipervínculo

Al pinchar este hipervínculo Excel abre el Outlook y crea un correo electrónico

También podemos crear hipervínculos a correos electrónicos usando la función HIPERVINCULO, como ya hemos mostrado en otros posts de este blog.
Por ejemplo, si tenemos una tabla con nombres y sus repectivos correos electrónicos, podemos crear una hipervínculo dinámico combinando las funciones HIPERVINCULO y BUSCARV. Sin embargo veremos que la creación del hipervínculo no es trivial.

Veamos este ejemplo:

La fórmula "=HIPERVINCULO(BUSCARV(C15,$B$9:$C$12,2,0))" en la celda C16 crea un hipervínculo. Sin embargo si pinchamos el vínculo en la celda veremos el mensaje: la dirección de este sitio no es válida.

El problema es que cuando Excel reconoce un patrón de correo electrónico, al crear el hipervínculo agrega el identificador "mailto:", lo que no sucede cuando lo creamos con la función HIPERVINCULO

Para poder crear hipervínculos al correo electrónico tenemos que agregar "mailto:" a la dirección

Si queremos que "mailto:" no aparezca en el contenido de la celda (C16 en nuestro ejemplo), modificamos la fórmula de esta manera:

=HIPERVINCULO("mailto:"&BUSCARV(C15,$B$9:$C$12,2,0),BUSCARV(C15,$B$9:$C$12,2,0))

Otra forma de crear Hipervínculos en Excel

Los hipervinculos son una excelente herramienta para crear vínculos a celdas u objetos en hojas o cuadernos y también a archivos y páginas Web. Pero también tienen sus bemoles y una visita a los foros de Excel en la red revela que uno de los problemas es que cada tanto los hipervínculos desaparecen o dejan de apuntar adonde deberían.

Pero en esta nota no vamos a hablar de la desaparición de los hipervínculos, sino mostrar otra forma de crearlos, casi instantánea.

Hace unos años atrás mostré una técnica que implica el uso de macros. Hoy vamos a mostrar otra técnica que usa el "drag and drop". Esta técnica es muy útil cuando no tenemos una gran cantidad de hojas.

Uno de los usos más prácticos de los hipervínculos en Excel es crear un índice del contenido del cuaderno. Supongamos que tenemos un cuadernos con datos de cuatro sucursales de un empresa y queremos crear una hoja con hipervínculos que apunten a cada una de las hojas.

En la hoja "indice" queremos crear los hipervínculos a cada una de las hoja de las sucursales.

Seleccionamos la hoja Norte, por ejemplo, y en ella la celda B3 que contiene el texto "Sucursal Norte"

Ahora arrastramos la celda usando el botón derecho del mouse y apretando simultáneamente la tecla Alt. Arrastramos la celda hasta apuntar a la pestaña de la hoja "indice", lo que la activará.
Una vez en la hoja "indice", dejamos de apretar la tecla Alt y posicionamos el mouse en la celda indicada. Al soltar el botón del mouse aparecerá en menú contextual donde elgimos la opción "Crear hipervínculo aquí"

Excel crea automáticamente el hipervínculo, ahorrándonos la molestia de tener que definir el texto.

Esta técnica funciona solamente en cuadernos que han sido previamente guardados.

Este video demuestra la técnica

Hipervínculos dinámicos dentro de un cuaderno Excel

Hemos tratado el tema de los hipervínculos en Excel con bastante detalle (o como dicen algunos de mis más ibéricos lectores, "rizando el rizo"). Pero siempre queda algo en el tintero (o en rioplatense, "otra vuelta de tuerca").
En una nota pasada discutimos el tema de las referencias en los hipervínculos (absolutas y relativas). Pero, como decíamos, siempre queda algún detalle por tratar.

Por ejemplo, un lector me comenta que tiene un cuaderno con una hoja que sirve de plantilla. Esta hoja contiene hipervínculos a celda dentro de la misma hoja. Cada vez que crea una nueva hoja en el cuaderno a partir de la plantilla, los hipervínculos siguen refiriéndose a la plantilla y no a la nueva hoja.

Esto se debe que al crear el hipervínculo con Inserta-Vínculos-Hipervínculo (o con el menú contextual, o con el atajo de teclado), Excel guarda la información como texto que incluye la referencia a la hoja.

La solución es usar la función HIPERVINCULO con un pequeño truco. Si queremos que el hipervínculo apunte siempre a la celda E5, por ejemplo, de la hoja activa, usamos esta fórmula

=HIPERVINCULO("#E5","texto en la celda")

Al usar el símbolo # en la referencia a la celda, Excel apunta siempre a la celda en la hoja donde se encuentra la fórmula.

En este video, la hoja Plantilla contiene dos hipervínculos que apuntan a la celda E5. Uno creado con con Ctrl+Alt+K (en la celda B2, con el texto "estático") y el otro creado con la función HIPERVINCULO (en la celda B4, con el texto "dinámico"). Creamos una nueva hoja (Mover o Copiar-Crear Copia). Al pulsar el hipervínculo en la celda B2, volvemos a la celda E5 en la hoja Plantilla; al hacerlo en B4, llegamos a E5 en la hoja activa.

Convertir hipervínculos a texto

Hace un poco más de un año atrás publiqué una nota mostrando una técnica para seleccionar celdas que contienen un hipervínculo.

Uno de mis lectores me consulta ahora cómo convertir la dirección del hipervínculo a texto que aparezca como valor de la celda. En otras palabras, queremos que en las celdas de la columna B aparezca las direcciones de los hipervínculos de las celdas en la columna A

Excel no tiene una función nativa para esta tarea por lo que tendremos que usar una UDF, es decir, escribir nuestra propia función.

Esta función es muy sencilla

Function hyp_to_text(rngCell As Range) As String
  
    hyp_to_text = rngCell.Hyperlinks(1).Address

End Function


Para usarla podemos ponerla en una módulo común del cuaderno Personal y la aplicamos usando el asistente de funciones con la categoría "Definidas por el usuario"

y finalmente apretamos Aceptar

Si la celda no contiene un hipervínculo, el resultado es #¡VALOR!

Evitar la creación automática de hipervínculos en Excel

Curiosamente esta semana una lectora y un compañero de trabajo me dirigen la misma consulta: como evitar que Excel cree hipervínculos automáticamente.

Cada vez que ingresamos datos en una celda de la hoja, Excel trata de interpretar de qué tipo de dato de se trata. Si ingresamos un valor que se como fecha (dos o tres grupos de cifras separados por "-" o "/"), Excel lo convierte automáticamente en fecha.

Lo mismo sucede con textos que parecen ser direcciones de sitios (URL) o de correos electrónicos.

Existen varias maneras de evitar que Excel cree hipervínculos automáticamente.

Si queremos eliminar este comportamiento totalmente tenemos que seguir estos pasos

Excel 2007

1. Apretar el botón de Office y luego el botón de Opciones de Excel
2. En las opciones elegir Revisión y apretar el botón Opciones de autocorrección
3. En la pestaña "Autoformato mientras escribe" quitar la marca de la opción "Rutas de red e Internet…"

En Excel Clásico (97-2003) el proceso es similar: Herramientas-Opciones-Ortografía-Opciones de Autocorrección-Autoformato mientras escribe…

Una vez realizados estos cambios no se crearán hipervínculos en forma automática (hasta que volvamos a cambiar las definiciones).

Existen también opciones manuales para cada celda en particular. Si inmediatamente después de ingresar el valor ha sido convertido en hipervínculo por Excel apretamos Ctrl+Z (deshacer), el hipervínculo desaparece quedando el valor en la celda. También podemos usar "Quitar Hipervínculo" del menú contextual que se abre al apretar el botón derecho del mouse sobre la celda.

Si queremos quitar todos los hipervínculos de una hoja de un golpe, no tendremos más remedio que usar una macro. En un módulo común del editor de Vb ponemos este código, preferentemente en el cuaderno Personal

Sub remove_hyper()
    Cells.Hyperlinks.Delete
End Sub

Ubicar celdas que contienen hipervínculos

Uno de mis lectores, Daniel, me pregunta como ubicar celdas que contienen hipervínculos. Ya hemos visto que Excel nos permite ubicar todo tipo de celdas de acuerdo a sus características usando Ir A-Especial (F5)

Como puede apreciarse, no existe la opción de seleccionar celdas que contengan hipervínculos.
Como ya habrán presentido, podemos hacerlo creando una macro que haga la tarea.
La idea es seleccionar el rango donde pueden encontrarse las celdas con hipervínculos y correr la macro. La macro examina cada una de las celdas del rango y si contiene un hipervínculo la selecciona.

El código es

Sub select_hyperlink()
    Dim rngCell As Range, strCells As String
  
    strCells = ""
  
    For Each rngCell In Selection
        If rngCell.Hyperlinks.Count = 1 Then
            strCells = strCells & rngCell.Address & ","
        End If
    Next rngCell
  
    If Len(strCells) < 2 Then Exit Sub
  
    strCells = Left(strCells, Len(strCells) - 1)
    Range(strCells).Select
  
End Sub

Lo más conveniente es copiar el código en un módulo del Personal.xls (para poder usarlo en todo cuaderno abierto de Excel) y agregarle un atajo de teclado, por ejemplo Ctrl+Mayús+H

Hipervínculos en Excel - referencias absolutas y relativas

El tema de los hipervínculos en Excel sigue siendo uno de los más consultados por mis lectores. Las preguntas más frecuentes tienen que ver con la "dirección".

La dirección es la referencia al archivo, objeto o páginas Web que se abrirá al pulsar el enlace. Técnicamente la referencia es un URL (Uniform Resource Locator).

El URL puede apuntar tanto a una página WEB como a un archivo.

De acuerdo con Office Online los hipervínculos, es decir la referencia URL, pueden ser absolutos o relativos.

Un URL absoluto es aquel que contiene la dirección completa que se compone de cuatro partes: el protocolo (http, ftp, file), la ubicación física (el servidor Web, o el lugar en la red o en la máquina), el path y el nombre del archivo.

Un URL relativo es aquel en el que falta alguna o algunas de las partes mencionadas.

En Excel los hipervínculos son, por defecto, relativos al cuaderno que los contiene.

Como ejemplo supongamos que tenemos un cuaderno con hipervínculos a imágenes de productos. El cuaderno está guardado en la dirección D:\Catalogo. Las imágenes están en el directorio D:\Catalogo\Productos

Abrimos un cuaderno nuevo, agregamos la lista de productos (en nuestro ejemplo los números de catálogo) y creamos el hipervínculo para el primero de la lista

Nótese que la dirección es absoluta. Esto se debe a que aún no hemos guardado el cuaderno. Lo mismo puede apreciarse apuntando al enlace

Después de guardar el cuaderno, la dirección se vuelve relativa

Ahora copiamos D:\Catalogo a un CD (D:\Catalogo incluye, obviamente, la carpeta Productos). De esta manera podremos entregar copias del catálogo a nuestros clientes.

Cuando nuestro cliente abra el cuaderno, los enlaces apuntarán a la ubicación del cuaderno (en nuestro ejemplo, E :\)

La dirección ha cambiado en relación a la ubicación del cuaderno.

Si queremos forzar dirección del hipervínculo podemos hacer lo siguiente:

Abrimos el menú Propiedades del cuaderno
En Excel Clásico: Archivo-Propiedades-Resumen;
En Excel 2007: botón del Office-Preparar-Propiedades-Propiedades Avanzadas
En la ventanilla Base del Hipervínculo ponemos la referencia deseada, de manera.

Hipervínculos en Excel - cambiar ubicación

Excel permite crear hipervínculos en celdas tanto a páginas Web como a archivos. Como ya hemos visto, Excel nos proporciona dos caminos:

• con el menú Insertar-Hipervínculo en Excel Clásico o Hipervínculo en la pestaña Insertar de Excel 2007 (o con el atajo de teclado Ctrl+Alt+K en ambas versiones de Excel o con el menú contextual)

• usando la función HIPERVINCULO

Este último método tiene la ventaja de permitirnos crear ubicación del vínculo, es decir el texto con la ruta del archivo, en forma dinámica.

Con dinámica nos referimos a que en lugar de un texto fijo, ponemos una referencia a una celda que contiene el texto. De esta manera, podemos cambiar el texto en la celda y la referencia cambiará automáticamente.

Esta técnica puede ser muy útil en ciertas situaciones. Por ejemplo, si tenemos hipervínculos a varios archivos en una carpeta y por algún motivo debemos moverlos a otra carpeta. Los hipervínculos creados con el menú no son dinámicos y al mover los archivos dejarán de funcionar. En estos casos la solución será usar macros, que mostraremos más adelante.



En este ejemplo podemos ver que la ruta del archivo se crea combinando el valor de B1 con el nombre del archivo

De esta manera, si movemos el archivo a otra carpeta todo lo que tenemos que hacer es cambiar el valor de la celda B1.

Pero la vida, como siempre, nos enfrenta a situaciones más complicadas. Como el caso de uno de mis lectores que fue creando hipervínculos a archivos guardados en una carpeta y verse necesitado a moverlos a otra carpeta. Como los hipervínculos no fueron creados con la función, sólo tenemos dos alternativas: o cambiar cada vínculo manualmente o usar una macro.

Vamos a mostrar una macro sencilla para reemplazar la ruta de todos los hipervínculos en una hoja. En esta hoja hemos creado cuatro hipervínculos a archivos en la carpeta “D:\My Documents\temp”.



Si queremos cambiar la ruta a “D:\My Documents\blog” podemos usar esta macro

Sub hipervinc_cambio()

    Dim hpVinc As Hyperlink
    Dim strOldAddress As String, strNewAddres As String
  
    strOldAddress = "D:\My Documents\blog\"
    strNewAddres = "D:\My Documents\algodistinto\"
  
    For Each hpVinc In ActiveSheet.Hyperlinks
        hpVinc.Address = Replace(hpVinc.Address, strOldAddress, strNewAddres)
    Next
  
End Sub

Esta es una macro simplificada a los solos efectos de demostrar cómo cambiar los vínculos programáticamente.
En lugar de poner las rutas en el cuerpo del código podemos ponerlas en celdas de la hoja y modificar el código de acuerdo.
Por ejemplo podemos usar las celdas E1 y E2 para alojar el texto de la ruta

y usar esta rutina

Sub hipervinc_cambio()

    Dim hpVinc As Hyperlink
    Dim strOldAddress As String, strNewAddres As String
  
    strOldAddress = Range("E1")
    strNewAddres = Range("E2")
  
    For Each hpVinc In ActiveSheet.Hyperlinks
        hpVinc.Address = Replace(hpVinc.Address, strOldAddress, strNewAddres)
    Next
  
End Sub

Hipervínculo a una dirección de correo Outlook

Siguiendo en el tema de los hipervínculos, respondemos hoy a la consulta de un de mis lectores: ¿Cómo puedo hacer para crear un hipervínculo a una dirección de correo Outlook dependiendo del valor de una celda con una lista validada?

EL tema es más sencillo de lo que parece en principio, pero hay un pequeño problema por resolver, como en casi todos los aspectos de nuestras vidas.

Empecemos por notar que cuando ponemos en una celda de Excel un texto que sigue el patrón de una dirección de correo electrónico, Excel agrega automáticamente un hipervínculo de tipo “mailto:xxx@yyy.zzz”





Como ya sabemos, podemos crear una lista desplegable usando validación de datos. Para eso creamos una lista de direcciones de mail y le damos un nombre definido. Para más comodidad definimos un rango dinámico con DESREF



Luego creamos la lista desplegable con validación de datos-lista



Ponemos la lista desplegable en la celda B1, de manera que podamos elegir la dirección



El problema con este método, es que a diferencia de cuando introducimos la dirección manualmente, Excel no crea el hipervínculo



Existen dos remedios a esta situación:

1 – después de seleccionar el nombre apretamos F2 (edición) e inmediatamente después Enter.

2 – Creamos un evento de manera que las acciones señaladas en 1 sucedan automáticamente. En el módulo Vba de la hoja1 (en nuestro ejemplo) ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, [B1]).Address = [B1].Address Then
SendKeys "{F2}"
SendKeys "{Enter}"
Application.EnableEvents = False
End If
Application.EnableEvents = True

End Sub

La sentencia SendKeys imita el tecleado de F2 y Enter si la variable Target coincide con la celda que hemos definido como celda que contiene la dirección.





Technorati Tags: MS Excel

Hipervínculo a una celda específica en Excel.

En la nota sobre hipervínculos en Excel vimos como crearlos en forma dinámica. En ese ejemplo mostrábamos como hacer que al elegir un valor de una lista desplegable, el hipervínculo se dirigiera a la primer celda vacía de una columna en una hoja determinada.

También podemos hacer que el vínculo se ligue a una celda determinada de un rango. Por ejemplo, supongamos que tenemos un cuaderno con una lista de clientes en una hoja (para el caso, “clientes”). En otra hoja ponemos una lista desplegable (con validación de datos) que muestre todos los nombres de los clientes. Queremos que al elegir un cliente, la celda que contiene el hipervínculo se actualice y que al pulsarlo nos lleve a la fila de cliente.

Como en el caso anterior, usamos la función HIPERVINCULO. La fórmula es sencilla, pero aprovecharemos la ocasión para mostrar también una técnica para construir fórmulas no tan sencillas.

En la hoja “clientes” tenemos esta lista





Empezamos por crear un rango dinámico con un nombre definido





La fórmula del nombre definido es
=DESREF(Clientes!$A$2,0,0,CONTARA(Clientes!$A:$A)-1,1)

Esta fórmula nos permite que el rango definido por el nombre se vaya adaptando a medida que agregamos o quitamos clientes de la lista.

En otra hoja creamos la lista desplegable basándonos en el nombre definido




En la celda contigua queremos poner la fórmula que cree el hipervínculo al cliente según se haya elegido en la lista desplegable.

Para encontrar en fila del rango de los clientes se encuentra el elegido usamos la función COINCIDIR de esta manera

=COINCIDIR(A1,Clientes,0)+1





Usamos el nombre definido “clientes” pero agregamos 1 al resultado ya que el nombre no toma en cuenta la primer fila del rango (el encabezamiento de la columna A).

Ahora podemos crear con facilidad la dirección de la celda buscada con la función DIRECCION

=DIRECCION(B1,1,,,"clientes")




Podemos usar el resultado en la celda B4 como argumento en la función HIPERVINCULO que pondremos en la celda B1

=HIPERVINCULO("#"&B4,"Ver "&A1)



Pero esto nos obligaría a usar celdas auxiliares. Sencillamente podemos reemplazar la referencia en la función por las fórmulas que hemos usado, quedando

=HIPERVINCULO("#"&DIRECCION(COINCIDIR(A1,Clientes!A:A,0),1,,,"clientes"),"Ver "&A1)






La pregunta que se impone ahora es: ¿cómo hacemos para que al elegir el cliente en la celda A1 Excel se dirija automáticamente a la celda correspondiente sin necesidad de pulsar en B1?

Bien, este será el tema de una futura nota.


Technorati Tags: MS Excel

Hipervínculos en Excel

Excel permite crear hipervínculos con facilidad. Todo lo que tenemos que hace es seleccionar una celda y pulsar Ctrl+Alt+K,




o usar el menú contextual pulsando el botón derecho del Mouse sobre la celda



o usar el menú Insertar-Hipervínculo



En este caso



creamos un vínculo a la celda A1 de la hoja Lista en nuestro cuaderno.

De la misma manera podemos crear vínculos a celdas en otras hojas del cuaderno, celdas en hojas de cuadernos remotos y también a páginas en la WEB.



Al señalar la celda con un hipervínculo, podemos ver la información de pantalla, dato que podemos modificar en el diálogo de creación del hipervínculo.

Además podemos agregar hipervínculos no sólo a celdas sino también a otros objetos como gráficos o imágenes, por ejemplo.

Otra forma de crear un hipervínculo es usar la función HIPERVINCULO. Por ejemplo, podemos crear el mismo hipervínculo como en la celda A1, usando el asistente de funciones


Sin embargo al referirse a un rango en una hoja del cuaderno, la función no funcionará





Para solucionar este problema usamos el símbolo # combinado con el nombre de la hoja y la referencia a la celda, en forma de texto, como argumento de la función
=HIPERVINCULO("#Clientes!A1","Pasar a la hoja Clientes)



La ventaja de usar la función en lugar del menú, es que podemos crear vínculos dinámicos.

En esta nota nos referiremos sólo a vínculos dentro del cuaderno activo.

Por ejemplo, supongamos que tenemos un cuaderno con varias hojas, una de ellas llamada "Clientes" y otra llamada "Proveedores".
En lugar de crear dos hipervínculos, uno a cada hoja, podemos crear una estableciendo la hoja a la que queremos pasar en forma dinámica.
Por ejemplo, en la celda A1 creamos una lista desplegable (usando el menú Validación de Datos) con los nombres de las hojas del cuaderno



En la celda C1 ponemos está fórmula con la función HIPERVINCULO

=HIPERVINCULO("#"&A1&"!A1","Pasar a la hoja "&A1)

Como ven, usamos el valor de la celda A1 también para crear la referencia a la hoja y también para crear el nombre descriptivo



Tanto la referencia como el nombre descriptivo cambian de acuerdo a la selección en la celda A1.

Hasta aquí hemos creado hipervínculos que nos llevan a la celda A1 de la hoja buscada. Ahora supongamos que queremos que el vínculo nos lleve automáticamente a la primer celda vacía de una determinada columna.
En la hoja Clientes tenemos una lista de clientes y sus direcciones. Queremos que el hipervínculo nos lleve, en nuestro ejemplo, a la celda A9 de la hoja Clientes



Dado que la columna A de la hoja Clientes contiene valores de texto, podemos usar esta fórmula para encontrar la dirección de la primer celda vacía

=DIRECCION(COINCIDIR("*",Clientes!A:A,-1)+1,1)

En esta fórmula estamos suponiendo que el símbolo * no aparece como texto en ninguna de las celdas del rango de búsqueda.
Ponemos esta fórmula en la celda B1 y la usamos como referencia en la función HIPERVINCULO



En lugar de usar una celda auxiliar, podemos incluir la fórmula de la celda B1 como argumento en la función HIPERVINCULO

=HIPERVINCULO("#"&A1&"!"&DIRECCION(COINCIDIR("*",Clientes!A:A,-1)+1,1),"Pasar a la hoja "&A1)

Finalmente podemos dar un paso más adelante y vincular dinámicamente también la búsqueda de la primer celda libre en función del valor de la celda A1

=HIPERVINCULO("#"&A1&"!"&DIRECCION(COINCIDIR("*",INDIRECTO(A1&"!A:A"),-1)+1,1),"Pasar a la hoja "&A1)
Aquí tenemos que usar la función INDIRECTO para que Excel considere el valor de la celda A1 en la función COINCIDIR.



El archivo con la fórmula se puede descargar aquí





Technorati Tags: MS Excel