lunes, 13 de agosto de 2012

VBA: Un hipervínculo con macros de Excel.

Recientemente un usuario realizaba una consulta sobre la manera de construir una macro que convirtiera el valor de una celda en un hipervícunlo hacia un fichero localizado en una carpeta de nuestro Pc.
...Quiero hacer una macro que coja el nombre que tengo escrito en una celda y le haga un hipervínculo a un achivo que habrá en una carpeta "X2" dónde habrá un archivo con el mismo nombre que yo he puesto en la celga (.html); [...] de manera que sólo active el hipervínculo en caso que la celda activa esté en la columna X ó Y...

Hace ya un año expliqué un planteamiento similar, pero utilizando sólo las herramientas estándar, la función HIPERVINCULO y la Validación de datos (ver entrada). Sin embargo, hoy aplicaremos un método bastante sencillo en nuestro procedimiento, el objeto Hyperlinks, en concreto con el método Add:
expresión.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)


El código que insertaremos en la Hoja1 (en la hoja donde queramos se ejecute) del Explorador de proyectos del Editor de VBA, formando nuestra macro de Excel, con un evento SelectionChange, quedando:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'verificamos que sólo actue si nos encontramos en las columnas X ó Y
If Not Intersect(Target, Range("X:Y")) Is Nothing Then
'asignamos variables a la ruta de nuestro equipo
ruta = "E:\excelforo\"
'y asignamos como nombre del fichero a vincular el nombre de la celda seleccionada
fichero = Target.Value
direccion = ruta & fichero
    'depuramos posibles errores, en caso que la celda está vacía salimos de la rutina
    If Target.Value = "" Then
    Exit Sub
    Else
    'si la celda tiene algún valor, lo convertimos en un hipervínculo
    'relacionándolo con el fichero situado en la carpeta y con nombre especificado.
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero
    End If
End If

End Sub


Estaríamos listos para comprobar su funcionamiento. Para cualquier celda con valor en la columna X ó Y, de la Hoja1, se convierte en un hipervínculo que relaciona con el fichero del mismo nombre que el valor de la celda, localizado en la carpeta E:\excelforo\ (en mi ejemplo).

95 comentarios:

  1. Buenas tardes:estaba buscando hacer exáctamente esto pero me surge una duda, (no estoy muy acostumbrado a trabajar con macros):
    He escrito la macro según viene y únicamente he puesto el nombre de la ruta donde están los archivos.
    Vuelvo al excel y para que se vinculen tengo que pinchar en cada una de las celdas de X donde están los nombres de los archivos y de esta manera me lo vincula uno a uno. Pero si quiero seleccionar todos (seleccionando y arrastrando)me da un error de ejecución 13 y se me marca en amarillo la línea: dirección=ruta&fichero
    El problema también es que si hay un nombre que no coincide con algún archivo de la carpeta de C: también me crea un hipervínculo pero luego al pinchar me dice que (logicamente porque no existe)"no se puede abrir el archivo especificado"

    ¿Estoy haciendo algo mal?

    Un saludo y gracias

    ResponderEliminar
    Respuestas
    1. Hola Antonio,
      bueno, lo que hace esta macro en concreto es vincular una única celda con un fichero existente en una ruta dada. Si seleccionas un grupo de celdas estás intentando construir una dirección imposible.
      Si lo que pretendes es convertir una serie de celdas en hipervínculos mediante una macro, tendrías que cambiar la estructura de la macro, incorporando por ejemplo una sentencia FOR EACH NEXT que recorra celda a celda la selección, y fuera convirtiéndolas en hipervínculo... siempre que exista el fichero.
      Para evitar el problema, quizá se pudiera comprobar previamente que existe ese fichero con ese nombre, y en caso negativo dejar sin hipervínculo esa celda.
      Slds

      Eliminar
  2. Saludos: una consulta
    cada vez que hago clic en el hipervinculo me aparece un mensaje de seguridad, como puedo hacer para deshabilitar el mensaje

    ResponderEliminar
    Respuestas
    1. Hola Gustavo!
      ¿qué mensaje es el que te aparece?.
      Es posible deshabilitar, mediante una sencilla macro los mensajes de advertencia o seguridad (pero incialmente todos, sin distinguir). Esto significaría que mensajes cotideáneos no se mostrarían, pudiendo llevarnos a inconsistencias en nuestros libros. No suelo recomendar esta opción.
      Podría ser en un evento _Open:
      Private Sub Workbook_Open()
      Application.DisplayAlerts = False
      End Sub
      y en otro _Close:
      Private Sub Workbook_BeforeClose(Cancel as Boolean)
      Application.DisplayAlerts = True
      End Sub

      Auque yo analizaría antes que todo esto qué tipo de mensaje de error aparece, ya que quizá sea más sencillo y práctico corregir el origen del mensaje de seguridad.
      Slds

      Eliminar
  3. Excelente aporte!
    Igual ando trabajando con hipervinculos, pero en mi caso necesito hacer algo que está un poco más complejo. Lo que requiero es convertir en hipervinculo el resultado de un conteo (CONTAR.SI.CONJUNTO), es decir, tengo una tabla con de donde cuento la cantidad de productos en base a un par de criterios (ciudad, fecha y tipo), el resultado de ese conteo necesito convertirlo en un hipervinculo que apunte directo a la parte de la tabla de donde se contaron. Por ejemplo:

    CIUDAD | PRODUCTO | FECHA |
    ALMERIA |PROD1 |02/09/2013
    GERONA |PROD2 |02/09/2013
    ALMERIA |PROD1 |02/09/2013
    GERONA |PROD2 |03/09/2013
    ALMERIA |PROD1 |04/09/2013

    Entonces, si aplico el contar si conjunto en base a la ciudad ALMERIA, el producto PROD1 y la fecha 02/09/2013, el resultado nos dará 2, este 2 necesito convertirlo en un hipervinculo que me mande exactamente a donde estan estos registros de donde salio la cuenta. Tomese en cuenta que el CONTAR.SI.CONJUNTO se encuentra en otra pestaña.

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Martín,
      se me ocurre, al tener diferentes campos para obtener un 'registro único', que realices un concatenado auxiliar, sobre el que realizar la búsqueda y poder así localizar una posición de coincidencia, ya con esa dirección podrás construir tu hipervínculo...
      Pero ojo, el hipervínculo sólo te puede llevar a una dirección!!!, con lo que te propongo, sería la primera coincidencia encontrada en la búsqueda sobre el concatenado.

      Espero te haya orientado.

      Eliminar
  4. Hola Romero:

    Tengo una pregunta un poco loca.

    Es posible ejecutar una macro con un hipervinculo, o alguna manera de detectar el momento en que se le da clic en un hipervinculo.

    ResponderEliminar
    Respuestas
    1. Hola Jumartos,
      es posible asociar un evento DoubleClick a una celda/s para ejecutar un procedimiento... Este evento iría en el código de la hoja:
      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      ' el código...
      End Sub

      para concretar más las celdas podrías aplicar dentro del código un método intersect que restrinja el rango sobre el que actuar.

      Saludos

      Eliminar
  5. Hola, no se si podriais ayudarme... Quiero poner un hipervinculo que me redireccione a una página web desde un campo de una tabla dinámica creada con Power Pivot. Gracias.

    ResponderEliminar
    Respuestas
    1. Hola María,
      con sinceridad no creo sea posible generar un hipervínculo sobre una Tabla dinámica ('normal' o generada con PowerPivot)...

      Lo siento, pero debemos reconocer algunos límites en Excel.

      Un cordial saludo y mucha suerte!

      Eliminar
  6. Hola Ismael,
    Soy nuevo en VBA y estoy preparando un archivo el cual parte de una base de datos y se crea una tabla dinamica, por tema de formatos esta se replica en una pestaña extra. No obstante quisiera vincular la información de esta última pestaña con sus correspondientes en la tabla dinamica que como bien sabemos pueden estar en diferentes celdas.

    Muchas gracias de antemano, disculpa las molestías

    ResponderEliminar
    Respuestas
    1. Hola!
      discúlpame pero no comprendo..
      Entiendo que sobre un origen de datos construyes una tabla dinámica... ¿y esta tabla dinámica la copias (supondré como valores) en otra hoja de tu libro ??...
      Me pierdo con el objetivo, 'vincular la última información de la segunda hoja' son sus datos correspondientes de la tabla dinámica ???

      Si he entendido bien, no hace falta ninguna macros, basta que actives la Opción de Tabla dinámica para Generar GetPivotData, esto te permitirá, vincular celda a celda desde cualquier sitio (tu segunda hoja) a la información contenida en tu TD, respecto a las agrupaciones, filtros y resúmenes de valores.

      Saludos cordiales

      Eliminar
  7. Muchas Gracias Ismael,
    la segunda hoja la construyo a partir de referencias relativas por lo que utilizó el index y match para obtener la información (desde la macro) y colocarla en la hoja 2, por motivos de formato están en diferente orden y ubicación.
    No había escuchado de la opción GetPivotData, me podrías explicar un poco más?

    Gracias y disculpa las molestias

    ResponderEliminar
    Respuestas
    1. Esa opción de Genera GetPivotData se activa desde las Opciones de tabla dinámica; una vez está activa puedes referirte a cruces concretos de la tabla dinámica (estén en las celdas que estén), a modo de vínculo...
      En la hoja de cálculo aparecerá la función IMPORTARDATOSDINAMICOS(...) haciendo referencia a esa TD.

      Activa la opción y desde cualquier celda selecciona una celda que te interese de la TD, verás enseguida cómo funciona...
      además es posible modificarla (empleando/anidando otras funciones) para adaptarla.

      Trataré de subir al blog una entrada poniendo algún ejemplo sobre el asunto.

      Saludos

      Eliminar
  8. Muchas gracias

    ResponderEliminar
  9. Hola Ismael,
    Primero de todo gracias por el aporte, es justo lo que buscaba. No obstante no consigo que se abran los archivos y creo que es porque en la ruta que genera la macro no especifica la extensión del archivo.
    Me explico: He creado una hoja (BDFactures) donde automáticamente se añaden filas con el contenido de otra hoja (Factura), a la par, se crea un archivo PDF el nombre del cual es el mismo que el número de factura y se guarda en una carpeta especificada. Ahora he añadido tu macro a la hoja (BDFactures) y funciona pero no del todo. Cuando selecciono la celda donde está el número de factura se crea el Hipervínculo y la ruta es la correcta pero cuando pincho me dice: "No se puede abrir el archivo especificado" . Al modificar la ruta del hipervínculo solo añadiendo la extensión ".pdf" ya me funciona.
    ¿Hay algún modo de solucionarlo?
    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Ismael,
      Como pensaba, el fallo estaba en la extensión. He modificado la linea que asigna el nombre a vincular con el archivo añadiendo (& ".pdf") y ahora sí me funciona. Pero me da error cuando selecciono una fila entera, me aparece este mensaje: "Se ha producido el error '13' en tiempo de ejecución: No coinciden los tipos."
      Cuando le doy a depurar me marca la linea modificada.
      De momento no me molesta mucho porque no tengo intención de seleccionar las filas enteras, pero me gustaría poder solucionarlo de alguna manera.

      Esta es la linea modificada:

      fichero = Target.Value
      direccion = ruta & fichero & ".pdf"

      Gracias.

      Eliminar
    2. Hola Xavi,
      para solucionar el segundo error (al seleccionar la fila entera o varias celdas al tiempo), podrías añadir un nuevo condicional, de tal manera que cuando tuvieras más de una celda seleccionada no hiciera nada...
      Algo de este estilo:

      Set isect = Application.Intersect(Target, Range("C:C"))
      If Not isect Is Nothing Then
      If Selection.Cells.Count = 1 Then
      If Target.Value = "" Then
      Exit Sub
      Else
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero
      End If
      End If
      End If
      Set isect = Nothing

      Creo sería suficiente...

      Un saludo

      Eliminar
    3. Hola Ismael,
      Dobles gracias, por la celeridad y por el contenido.
      Funciona a la perfección.

      Eliminar
  10. Hola Ismael

    sabes de alguna forma de generar un hypervinculo que me lleve a una pag. web al apretar un botón

    Gracias!!

    ResponderEliminar
    Respuestas
    1. Hola,
      te serviría, si quieres emplear macros, la fución VBA hyperlink, en el argumento Address incluye la URL a la que quieras acceder.
      Luego de tener creado tu procedimiento Sub podrías asignarlo a un Botón de formulario

      Espero haberme explciado
      Un cordial saludo

      Eliminar
  11. Hola Ismael. Estoy bastante agradecido por tu gran ayuda. Ya los nombres se vuelven hipervinculos y la extension de los archivos es la correcta. Pero tengo un problema, cada vez que doy click sobre la celda o paso sobre ella con las flechas del teclado, se repite la extension del archivo. ASI:

    hola.docx.docx.docx.docx.docx

    Espero puedas ayudarme. Mil Gracias

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      parece que el problema sea que has incluido parte del código en algún bucle, y a la hora de componer el nombre del archivo, repite la extensión...

      Habría que ver el código, pero asegúrate que cuando nombras el fichero, queda fuera del bucle que tengas...

      Saludos cordiales

      Eliminar
    2. Hola, muchas gracias, quería contarle Ismael que ya pude solucionar ese problema. El programas es buenísimo, pero tengo ahora el inconveniente de que al seleccionar toda la columna me sale el "Error 13". Aunque usted ya publico la solución no me sirve o la verdad no se en que parte del programa ubicarla.

      Agradecería cualquier ayuda posible.

      Eliminar
    3. Hola Daniel,
      si quieres adjúntame el código a
      excelforo@gmail.com
      y le echo un vistazo.
      Slds

      Eliminar
  12. Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'verificamos que sólo actue si nos encontramos en las columnas X ó Y
    If Not Intersect(Target, Range("AP:AP")) Is Nothing Then
    'asignamos variables a la ruta de nuestro equipo
    ruta = "C:\Users\Calidad\Desktop\RECLAMOS-AUDITORIAS\COLOMBIA TELECOMUNICACIONES - TELEFÓNICA MOVIL\SEPTIEMBRE\Santa Marta\PARCIALES\SEGUNDO PARCIAL SEP 2014"
    'y asignamos como nombre del fichero a vincular el nombre de la celda seleccionada
    fichero = Target.Value
    direccion = ruta & fichero
    'depuramos posibles errores, en caso que la celda está vacía salimos de la rutina
    If Target.Value = "" Then
    Exit Sub
    Else
    'si la celda tiene algún valor, lo convertimos en un hipervínculo
    'relacionándolo con el fichero situado en la carpeta y con nombre especificado.
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero
    End If
    End If

    End Sub

    ResponderEliminar
    Respuestas
    1. Para mi caso queda así pero no se porqué no funciona, ayudenme por favor

      Eliminar
    2. Hola Juan Carlos,
      hace falta que tu ruta termine en barra invertida \
      ruta = "C:\Users\Calidad\Desktop\RECLAMOS-AUDITORIAS\COLOMBIA TELECOMUNICACIONES - TELEFÓNICA MOVIL\SEPTIEMBRE\Santa Marta\PARCIALES\SEGUNDO PARCIAL SEP 2014\"

      Saludos!

      Eliminar
  13. Hola Ismael de gran útilidad tu macro, es posible que me ayudes con algo similar respecto al hiperlinks
    tengo el siguiente código que imprime en PDF:

    Dim Reader As String, Archivo_PDF As String
    Reader = "c:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
    Range("C2").Select
    While ActiveCell <> ""
    Archivo_PDF = Chr(34) & ActiveCell.Hyperlinks(1).TextToDisplay & Chr(34)
    Shell Reader & " /p /h " & Archivo_PDF
    ActiveCell.Offset(1, 0).Select
    Wend


    Resulta que el imprime muy bien solo cuando doy la ruta en Archivo_PDF
    es decir: Archivo_PDF = "C:\Users\gonz\Desktop\Imprimir\DEVOLUCION.pdf"

    pero cuando ejecuto tal cual como esta en el código
    Archivo_PDF = Chr(34) & ActiveCell.Hyperlinks(1).TextToDisplay & Chr(34)

    me genera un mensaje que me informa que el archivo no se encuentra.

    la cuestion es que necesito que en la celda activa C2, puede abrir el archivo con el link
    lo intente con: Archivo_PDF = Chr(34) & Selection.Hyperlinks(1).Follow & Chr(34)
    me habre el archivo pdf pero ya no me imprime, como si lo hace cuando le doy:
    Archivo_PDF = "C:\Users\gonz\Desktop\Imprimir\DEVOLUCION.pdf"

    agradezco tu ayuda. saludos y gracias por tus macros.

    ResponderEliminar
    Respuestas
    1. Hola!
      quizá el fallo esté en que tomas el TextToDisplay de la celda...
      Si es un hipervínculo es mejor que tomes su address, así te aseguras te diriges a su dirección/ruta exacta y completa...
      Saludos

      Eliminar
    2. Hola Ismael, te cuento que funciono perfectamente, el hiperlinks con el address, muchas gracias por tu ayuda, y ahora tengo un error al querer cambiar Range("C2").Select por Range(activecell).Select el cual me aparece como mensaje que "se esperaba función o variable", lo que intento es que tome el rango de la celda activa, tambien lo intente con ActiveCell.Select pero sigue el mismo error. Muchas gracias por tu ayuda.

      Eliminar
    3. ;-)

      ??
      con el bucle que ta tenías te irá recorriendo las celdas no vacías desde C2
      Range("C2").Select
      While ActiveCell <> ""
      Archivo_PDF = Chr(34) & ActiveCell.Hyperlinks(1).TextToDisplay & Chr(34)
      Shell Reader & " /p /h " & Archivo_PDF
      ActiveCell.Offset(1, 0).Select
      Wend

      Lo siento, pero no comprendo?
      Slds

      Eliminar
  14. Hola señor ismael disculpe si se repite la consulta pero mi pc esta un pco lento, la volvere a subir, es que me presenta error en la ultima linea (ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero), le adjunto el codigo completo , señor ismael le agradeceria me ayudara

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'verificamos que sólo actue si nos encontramos en las columnas X ó Y
    If Not Intersect(Target, Range("a8:a15")) Is Nothing Then
    'asignamos variables a la ruta de nuestro equipo
    ruta = "C:\Users\Linea Viva\Desktop\coti\cotizacion\"
    'y asignamos como nombre del fichero a vincular el nombre de la celda seleccionada
    fichero = Target.Value
    direccion = ruta & fichero & ".pdf"
    'depuramos posibles errores, en caso que la celda está vacía salimos de la rutina
    If Target.Value = "" Then
    Exit Sub
    Else
    'si la celda tiene algún valor, lo convertimos en un hipervínculo
    'relacionándolo con el fichero situado en la carpeta y con nombre especificado.
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero
    End If
    End If

    End Sub

    ResponderEliminar
  15. el valor de la direccion = ruta & fichero & ".pdf", la tome de un comentario del señor Xavi Torrens, vera la carpeta que dice (coti) contiene el excel con la macro y contiene la carpeta cotizacion que es donde estan guardados los pdf que me genera, no se si asi este bien la ruta

    ResponderEliminar
    Respuestas
    1. Hola pazalex,
      he replicado tu código, cambiando la ruta por una local (y también por otra de escritorio) y funciona perfectamente, esto me hace pensar que o bien existe un error en el nombre del archivo (el que está escrita en la celda) o bien en la ruta...

      Saludos

      Eliminar
  16. muchas gracias señor ismael, de verdad le agradezco el que usted se tome un espacio en su valioso tiempo para colaborarnos a todos los que a usted le consultamos, este tipo de ayuda que usted presta sin nada a cambio es lo que fomenta el altruismo y nos hace recordar que aun existe personas que dan sin esperar nada a cambio.
    un abrazo desde Colombia

    ResponderEliminar
  17. te podria enviar el archivo para que le des un vistazo?, si fuera asi a que correo podria enviarlo de verdad m,e da pena molestarlo, pero seria de muchisima ayuda

    ResponderEliminar
    Respuestas
    1. puedes enviarlo a
      excelforo@gmail.com

      pero ya te figo, no parece problema del código (que funciona) y sí del nombre del fichero o ruta

      Saludos cordiales

      Eliminar
  18. Amigo ismael cordial saludo desde colombia queria hacerte una consulta con otro tema, a ver si me puedo explicar.

    en la hoja 1, tengo un boton con una macro para insertar una nueva fila desde la fila 8. Hasta hay no hay inconveniente, en el range("a8") tengo una validacion de datos por lista que viene de la hoja 2, (alta, media, baja), pero al insertar la nueva fila ya la validacion desaparece y toca nuevamente validar datos desde la barra de menu de datos.
    hay forma de que al insertar la nueva fila, esta ingrese con esa validacion de datos al rango a8 ?

    ResponderEliminar
    Respuestas
    1. Hola,
      si donde estás insertando está convertido en Tabla, te arrastra las fórmulas, formatos, validaciones, etc de arriba...

      Saludos

      Eliminar
  19. Mi amigo gracias!!! eso era sencillamente lo que debia hacer, convertirlo en tabla!

    ResponderEliminar
  20. Buen dia Ismael, Gracias por tu aporte de antemano, quisiera hacer una macro donde me lleve de un click , un boton o elemento en powerpoint a un lugar especifico en excel, se que con hyperlink se puede colocando la ruta el fichero y al final un #mas ubicacion. por ejemplo archivo.xml#e33 , pero es un poco tedioso darle clik derecho abrir hyperlink. Saludos.

    ResponderEliminar
    Respuestas
    1. Hola Cindy,
      los procedimientos en PowerPoint son algo distintos de Excel...será mejor consultes en un Foro específico de PowerPoint.
      Saludos cordiales

      Eliminar
  21. Hola Romero tengo una problema tenia un libro con Hipervinculos que habia llenado una serie de usuarios, no se que sucedio pero los hipervinculos se grabaron con otra ruta y ya no puedo acceder a los las rutas

    ejemplo tenia
    L:\Common\BusinessUnits\CSC\3)Quality\MSA Projects\Star 2\1) ICT\ICT15\MSA_STAR2_ICT15_Abr14.pdf


    y ahora
    C:\Users\uidw8976\AppData\Roaming\Microsoft\Excel\Star 2\1) ICT\ICT15\MSA_STAR2_ICT15_Abr14.pdf


    quito
    L:\Common\BusinessUnits\CSC\3)Quality\MSA Projects

    y agrego
    C:\Users\uidw8976\AppData\Roaming\Microsoft\Excel


    ¿como puedo recuperar los hipervinculos?

    ResponderEliminar
    Respuestas
    1. Hola Maldonado,
      entiendo el cambio es constante en todos los casos.
      Si es así, para la hoja activa:
      Sub cambiaLink()
      Dim hLink As Hyperlink

      For Each hLink In ActiveSheet.Hyperlinks
      hLink.SubAddress = Replace(hLink.SubAddress, "C:\Users\uidw8976\AppData\Roaming\Microsoft\Excel", "L:\Common\BusinessUnits\CSC\3)Quality\MSA Projects")
      Next hLink

      End Sub


      Espero te sirva.
      Slds

      Eliminar
  22. Buenas Ismael, de antemano agradecerte que dediques tu tiempo a responder todas las dudas que tenemos tantos usuarios, es un esfuerzo increíble.
    A ver si puedes resolver la mía, te cuento:

    Estoy trabajando con Power Pivot y necesito exportar unos datos procedentes de una tabla Excel. Los datos siempre están en las mismas celdas pero, el problema es que el archivo Excel cambia de nombre cada semana, es decir, cada semana es uno nuevo con los valores actualizados para esa semana. Por tanto, el nombre del archivo Excel va cambiando (S1, S2, S3...).

    Me han comentado que existe una función que vincula el archivo Excel (cuyo nombre de origen va cambiando) y te calcula el cambio de semana, buscando así el documento que corresponde a cada semana. Siempre coge los valores de las mismas celdas pero cambia el nombre de origen del archivo.

    No encuentro cuál es la función. ¿Podrías ayudarme a resolver mi problema?

    Muchísimas gracias!!!

    ResponderEliminar
    Respuestas
    1. Hola Laura,
      realmente si hablas de PowerPivot estaríamos hablando de conexiones (cadenas de conexión) o vínculos externos, no creo una función te solucione el problema a priori...
      Quizá la función a que te refieres es la función INDIRECTO en la que puedes construir una ruta variable, algo así:
      =INDIRECTO("E:\Excelforo\'[S"&NUM.DE.SEMANA(HOY();2)&"]Hoja2'!A1:A10")
      También puedes buscar entre las funciones de la categoría CUBO:
      CONJUNTOCUBO, etc...
      Es un problema muy concreto.. siento no poder decirte algo más
      :(

      un saludo

      Eliminar
  23. Antes que nada un cordial y atento saludo Sr. Ismael!
    Tengo un libro en excel en el cual una de las hojas tiene un inventario por empleado, ya realice un programa en vba extension de excel el cual me busca los comentarios que tienen cada uno de ellos y me muestra su ubicacion ( Su celda) en un listbox. En mi caso necesito un hipervinculo en un commandbutton que al ejecutarlo me lleve a la celda mostrada en el listbox

    De antemano muchas gracias por su ayuda y que tenga un feliz dia.

    ResponderEliminar
    Respuestas
    1. Hola,
      si ya tiene su ubicación en el listbox o donde sea, sólo toma ese literal para convertirlo en Rango o Celda (Range o Cells) para asociarlo al evento _Click del ListBox y que realice la acción de seleccionar dicho destino:
      Algo así:
      Private Sub ListBox1_Click()
      Range(ListBox1.Value).Select
      End Sub

      suponiendo, como indicabas que el listbox esté cargado con las ubicaciones (por ejemplo, del tipo A1, B2, C4, etc.)

      Saludos

      Eliminar
    2. Muchas gracias sr. Ismael... me sirvio de mucho su ayuda. Ahora tengo un problemita, me estan pidiendo que a el programa le haga unas validaciones a la hora de ingresar el nombre del empleado, o sea, que sea como sea que el usuario introduzca el nombre se lo localize en el inventario. Por ejemplo: Luis Gomez, luis gomez, Luis gomez, luis Gomez, etc... Que puedo hacer en este caso?

      Eliminar
    3. Hola,
      lo más habitual es tratar la información con UCASE o LCASE sobre los campos a comparar, i.e., convertirlo todo a mayúscula o minúscula.
      Slds

      Eliminar
    4. Muchas graciaa, pero si le soy sincero, no se hacerlo, me gusta la programacion y la entiendo, pero la verdad nunca he trabajado con CASE en VB, me podria decir como hacerlo? Mandarme un codigo o algo por el estilo. De ante mano, muchas gracias por su tiempo y su ayuda.

      Eliminar
    5. Hola,
      no te hablo de CASE (Select case), si no de las funciones UCASE y LCASE...
      se aplican simplemente sobre el valor a convertir
      UCASE(combobox1.value)
      convertiría y trataría el valor devuelto en mayúscula.
      Slds

      Eliminar
    6. Hola! Y esas funciones pueden usarse juntas sin que una altere a la otra? Y me imagino que eso lo pongo en el textbox en el cual introduzco el nombre del empleado?

      Eliminar
    7. se trata de homogeneizar el dato, independientemente de cómo lo introduzca el usuario (en cuanto a mayúsculas y minúsculas), emplearás uno u otra (UCASE o LCASE), no las dos a la vez.
      Puedes aplicarlo sobre el textbox para que registre el dato en la hoja de cálculo siempre en mayúscula, asegurándote que siempre se refleja de igual forma...
      o también a la hora de realizar búsquedas, etc.
      Slds

      Eliminar
  24. Buenos días, no tengo experiencia en Macros pero me gustaría crear hipervínculo en 1 Excel de la celda A1:A11251 con archivos pdf que tienen el mismo nombre que las celdas del rango indicado. Me podríais explicar los pasos a seguir para lograrlo? Gracias

    ResponderEliminar
    Respuestas
    1. Hola Lucía,
      en principio el código sería casi idéntico:
      Sub vinculo()
      ruta = "E:\excelforo\"
      for each celda in Range("A1:A11251")
      fichero = celda.Value
      direccion = ruta & fichero
      If celda.Value = "" Then
      Exit Sub
      Else
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero
      End If
      End If
      next celda
      end sub

      Espero te sirva... Recuerda añadir la ruta donde se encuentren los pdf en lugar de
      ruta = "E:\excelforo\"
      con la barra \ al final
      Saludos

      Eliminar
    2. Gracia Ismael, me da un error de complicación: ENd IF sin bloque IF, Qué debería cambiar?
      Saludos y gracias

      Eliminar
  25. Se me olvidaba comentar que se pone en amarillo la 1ª línea donde pone Sub vinculo ()

    ResponderEliminar
    Respuestas
    1. Hola Lucía,
      sobra un End if...
      elimínalo y te debería funcionar sin problemas
      Saludos

      Eliminar
    2. Hola Ismael a mi me sale un error diferente
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero

      argumento o procedimeiento no valido me sale tambien erroe de ejcucion y un "5"

      Eliminar
    3. Hola José Luís,
      es importante que todas las rutas sean correctas y existan los ficheros.. en caso contrario fallará y aparecerá el depurador...
      puedes emplear el depurador de errores:
      On error resume next
      ...
      on error goto 0

      así conseguirás que las rutas/ficheros no existentes no hagan saltar el depurador.
      Saludos

      Eliminar
    4. Hola Ismael, me sale el mismo error "5", será que todos los valores son numéricos?, porque ya hice la prueba con valores de texto y así no tengo problema.

      Cuando le doy en depurar lo que me selecciona en color amarillo es

      "ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero"

      Porque lo que quiero es no tener que agregar el .pdf en otra celda.

      Ejemplo: Celda=234567

      ruta = "E:\Usuario Asignado\Escritorio\1\"
      For Each celda In Range("B2:B173")
      fichero = celda.Value
      direccion = ruta & fichero & ".pdf"
      If celda.Value = "" Then
      Exit Sub
      Else
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=fichero
      End If

      Next celda
      End Sub


      Saludos.

      Eliminar
    5. Hola Kar TG,
      curioso.. prueba con esto:
      Sub xxx()
      Dim fichero As String
      Dim direccion As String
      ruta = "E:\"
      For Each celda In Range("a1:a3")
      celda.Select
      fichero = celda.Value
      direccion = ruta & fichero & ".pdf"
      If celda.Value = "" Then
      Exit Sub
      Else
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion, TextToDisplay:=CStr(fichero & ".")
      End If
      Next celda
      End Sub


      Saludos

      Eliminar
  26. Hola Ismael
    Mira he desarrollado una macro que crea un archivo nuevo en donde hay hiperlink entre las hojas, lo cree en excel 2010 Español.
    El problema viene cuando abren el archivo creado en Brasil, y dan clic a los hiperlink saliendo el Error: Referencia no valida(en portugues)
    Creo que podria ser un problema de idiomas, ya que las hojas por defecto se nombran en español Hoja1 y en portugues Plan1.
    Me podrias ayudar con esto por favor?
    Agradezco tu atencion

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      entiendo que si el vínculo tiene una ruta correcta (incluyendo nombre del Libro y nombre de la hoja), no importa en qué idioma estés trabajando...
      Yo verificaría que la ruta está tomando exactamente la ubicación de libro así como el nombre de la hoja y celda... que el nombre de la hoja sea Hoja1 o Plan1 es intrascendente para el vínculo.
      Saludos

      Eliminar
    2. Gracias por tu respuesta Ismael, es bien extraño ya que ese mismo documento lo abren usuarios en Chile Colombia Argentina y Peru, pero les es imposible para Brasil.
      Una prueba que hice fue crear un hiperlink manualmente dentro del mismo archivo y si funciono.
      Gracias nuevamente por tu preocupacion.

      Eliminar
  27. Buenos dias Ismael, tengo una macro la cual alimenta una base de datos de la cual una columna es referente a las facturas, las cuales quisiera saber si se puede generar un hipervínculo a una pagina web que contiene el mismo folio de factura en archivo pdf, agradecería mucho tu apoyo.

    Saludos y Gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola,
      en principio cualquier fichero o 'cosa' colgada en una web tiene una URL.. por tanto solo debes conocer dicha URL y realizar un hipervínculo a dicha dirección.
      Sigue las indicaciones de este mismo post...

      Espero te sirva la pista

      Saludos

      Eliminar
  28. Buen dia!! me podria ayudar me funciono perfecto pero ahora requiero que se vinculen de dos celdas a dos diferentes carpetas.. es decir la informacion de la celda A1 a "x" carpeta y la informacion de otra celda B1 a "y" carpeta..
    No se si me explique..
    De antemano muchas gracias!!
    Salduso!!

    ResponderEliminar
    Respuestas
    1. Hola Antonio,
      si requieres dos direcciones distintas para dos vínculos, necesitarás las dos rutas, y una condición que derive a una u otra (con un IF.. THEN)

      Saludos

      Eliminar
    2. me podrias decir donde es que tengo que inserta esa condicion.. realmente no se mucho sobre programacion...
      Gracias!!

      Eliminar
    3. Hola Antonio,
      podría ser en:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Target, Range("X:Y")) Is Nothing Then
      ruta1 = "E:\excelforo\"
      RUTA2 = "E:\segundaruta\"
      fichero = Target.Value
      direccion1 = ruta1 & fichero
      DIRECCION2 = ruta2 & fichero
      If Target.Value = "" Then
      Exit Sub
      Else
      IF [aquí la condición que derive a la ruta 1] THEN
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion1, TextToDisplay:=fichero
      ELSEIF [aquí la condición que derive a la ruta 2] THEN
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=direccion2, TextToDisplay:=fichero
      ENDIF
      End If
      End If
      End Sub

      algo así te debería funcionar.
      Slds

      Eliminar
  29. UNA PREGUNTOTA
    Primero que nada la macro funciona exelente bueno asta cierto punto, si me crea los hipervinculos que necesito el problema es que al dar click sobre este me sale un mensaje que dice: "No se puede abrir el archivo especificado" ya cree los archivos con dichos nombres creo hipervinculos manualmente y si me redirecciona creo que el problema es que con los hipervinculos de la macro la direcion del archivo termina asi: TARIMA DE MADERA .xlsx Y con el hipervinculo manual asi: TARIMA DE MADERA.xlsx osea que en de la macro me crea un espaio entre la terminacion y el nombre del archivo y con el manual no entonces quiero saber si es ese el problema o no, mi archivo que crea la macro esta en una celda con formula BUSCARV

    ResponderEliminar
    Respuestas
    1. Hola Diana,
      si determinaste que el problema es que te aparece un espacio en blanco al final del nombre, seguramente es que efectivamente, tu función BUSCARV toma el dato con ese espacio..
      No es problema en sí mismo de la macro (aunque se podría intentar ajustar para solucionarlo), si no del origen de datos, donde el nombre del fichero arrastra ese espacio en blanco al final que genera el error...
      Solución, elimina ese espacio sobrante con la función ESPACIOS:
      =ESPACIOS(BUSCARV(...))
      Saludos

      Eliminar
    2. Excelente

      muchísimas gracias era exactamente lo que faltaba, y pensar que era con algo tan simple

      Eliminar
  30. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  31. Saludos,

    Tengo un problema, que no sé si está relacionado con las macros.
    Hace ya dos años creé una hoja excel, con la función de "base de datos", es decir voy creando filas, con diferentes clientes y su información. Hace poco le puse unas macros, para que al pinchar un botón, aparezca sola la información relacionada con el código, esta información se encuentra en otro excel.
    Hasta aquí todo bién, el problema ocurre, cuando creo hipervinculos (sea con o sin la macro)en un código, ya que al principio funciona bien. Pero al cabo de unos días o semanas, cuando voy a abrir el mismo hiprvinculo, me lleva al hipervinculo de otro código, sin relación alguna. Es como si se intercambiaran o traspapelaran los hipervinculos entre ellos.
    He probado diferentes soluciones basada en las hipotesis siguientes: que pase por culpa de filtrar mediante la función de filtro de excel, he estado mirando que en opciones/Al calcular este libro... Y sale un cuadradito que dice Actualizar vínculos a otros documentos. Pero no consigo que los hipervínculos permanezacan en su lugar.
    Por ultimo ahora me pregunto si el problema puede venir dado por las macros que tengo en el éxcel.
    Perdón por el parrafón, pero me tiene desesperado este problema.

    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola Octavi,
      tendría que ver la situación completa para entender bien la situación (y el problema)..
      ¿son hipervínculos sobre la celda o generados con la función HIPERVINCULO?
      Slds

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. Hola Ismael,

      Son sobre la celda, la semana pasada descubrí la función hipervínculo y estoy probando.

      Saludos,

      Eliminar
    4. Deberías quizá gestionarlo con la función HIPERVINCULO, donde puedes controlar más fácilmente ubicación y texto mostrado...
      Sdls

      Eliminar
    5. Es lo que estoy probando actualmente, haber si en unas semanas siguen los hipervínculos en su sitio.

      Gracias

      Eliminar
  32. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  33. Muchas gracias Ismael, fue de mucha utilidad la información que encontré en este foro.

    ResponderEliminar
  34. HOLA ISMAEL TENGO UN FORMULARIO EN EXCEL Y EN UNA CELDA TENGO LA SIGUIENTE FORMULA =SI(B2="","",HIPERVINCULO(CONCATENAR("C:\Users\Desktop\OTROS INGREOS 2016\ENERO\FACTURACIÓN\FACTURAS ENERO","\",B2,".pdf"),B2)), Y NECESITO QUE EN EL FORMULARIO EN EL TextBox10_Change() ME APAREZCA COMO HIPERVINCULO PARA ABRIR EL ARCHIVO EN PDF, ESTOY OCUPANDO LO SIGUIENTE PARA INDICAR DE QUE CELDALAS DEBE MOSTRAR LA INFORMACION EL FORMULARIO

    ResponderEliminar
    Respuestas
    1. Hola,
      es posible replicar el comportamiento del hipervínculo con el método .FollowHyperlink, puedes ver un ejemplo en:
      http://excelforo.blogspot.com.es/2015/10/vba-un-hipervinculo-dentro-de-un-listbox.html

      Saludos

      Eliminar
    2. BUEN DIA. OK, LO INTENTO Y TE AVISO, MUCHISIMAS GRACIAS

      Eliminar
  35. BUEN DIA SE ME OLVIDO COMENTARTE ES UN HIPERVINCULO DINAMICO, ES DECIR SELECCIONO UNA OPCION Y ME CARGA UNA INFORMACIÓN, SELECCIONO OTRA OPCION Y ME CARGA INFORMACIÓN DISTINTA, PARA ELLO ESTOY OCUPANDO
    If CheckBox1.Value = True Then
    ActiveCell.Offset(0, -2) = TextBox2.Value
    ActiveCell.Offset(0, -1) = TextBox3.Value
    ActiveCell.Offset(0, 1) = TextBox4.Value, PARA INDICAR QUE CELDA DEBE CARGAR Y ENTRE ESA CARGA Y MEDIANTE ESA DECLARACIÓN DEBE CARGAR LA FORMULA =SI(B2="","",HIPERVINCULO(CONCATENAR("C:\Users\Desktop\OTROS INGREOS 2016\ENERO\FACTURACIÓN\FACTURAS ENERO","\",B2,".pdf"),B2)) Y ABRIR EL HIPERVINCULO.

    SALUDOS.

    ResponderEliminar
    Respuestas
    1. Hola,
      la opción que te comenté de .FollowHyperlink no requiere del uso de funciones... lo que sería raro (quizá inposible) dentro de un ListBox en un Userform

      Slds

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
  36. Hola Ismael:
    Tengo una consulta.....necesito asignar desde un boton de comando un hipervinculo desde la web (donde debo elegir entre varios archivos de google drive) a una forma que tengo en una celda, me puedes ayudar con una macro para lograrlo?
    De antemano te agradezco enormemente tu buena disposición...

    ResponderEliminar
    Respuestas
    1. Hola,
      no tengo claro muy bien el funcionamiento de Google Drive (no lo uso), pero entiendo que tendrá como acceso una URL, por lo que esa sería la dirección del hipervínculo...
      Si esto es así, solo debes añadir ésta en la macro del ejemplo...

      Siento no poder decirte mucho más.
      Un saludo y suerte!

      Eliminar