Modificar vinculos en Excel con VBA
Alvarez@discussions.microsoft.com Claudia Alvarez
archivos se encuentran en unidades de red. Los vínculos fueron creados
utilizando Office XP.
Cuando se crean vínculos a un archivo excel desde una unidad de red H (para
poner un ejemplo) a otro archivo excel que se encuentra en la misma unidad,
algunas veces guarda el vínculo con el path H:\UnArchivoExcel.xls y otras
veces lo guarda como \\servidor\directorio\UnArchivoExcel.xls.
Hasta aquí todo bien. El problema surge cuando muevo el directorio ya que se
pierden los vínculos y dejan de funcionar. En este momento cuento con una
macro que busca los vínculos que comienzan con \\, lo que necesitaría saber
es cómo poder modificar el camino \\servidor\directorio\ por H:\ con VBA.
Agradezco cualquier ayuda y/o sugerencia.
Claudia.
H�ctor Miguel
> ... problema con los vinculos en excel... en unidades de red ...
> ... algunas veces guarda el vinculo con el path H:\UnArchivoExcel.xls
> y otras veces lo guarda como \\servidor\directorio\UnArchivoExcel.xls.
> El problema surge cuando muevo el directorio ya que se pierden los vinculos y dejan de funcionar.
> En este momento cuento con una macro que busca los vinculos que comienzan con \\
> lo que necesitaria saber es como poder modificar el camino \\servidor\directorio\ por H:\ con VBA...
si suponemos que (solo) moviste de ubicacion/carpeta/... los archivos a donde "saltaban" los hipervinculos -?-
prueba con una macro +/- como la siguiente:
Sub CambiarCarpetaHipervinculos()
Dim Antes As String, Ahora As String, Salto As Hyperlink
Antes = "pon aqui la ruta a la ubicacion ANTERIOR de los documentos"
Ahora = "por aqui la ruta a la ubicacion ACTUAL de los documentos"
For Each Salto In ActiveSheet.Hyperlinks
If InStr(1, Salto.Address, Antes, 1) Then
If Val(Application.Version) > 8 Then _
If Salto.TextToDisplay = Salto.Address Then Salto.TextToDisplay = Ahora
Salto.Address = Application.Substitute(LCase(Salto.Address), LCase(Antes), Ahora)
End If
Next
End Sub
de lo contrario, considera algunos imponderables como los siguientes:
considera que una "unidad H" (de red) pudo ser ayer la unidad "Z", y despues sera... ??? (segun se monte en el servidor)
asi que tu consulta es (como) una "punta de iceberg", y aunque pueden imaginarse algunas situaciones y/o eventualidades...
lo grave de la situacion seria si las reales ni siquiera entran en la imaginacion de alguien (de este lado del mensaje) -???-
espero que lo siguiente te sea de utilidad y... si cualquier duda (o informacion adicional)... comentas?
saludos,
hector.
=== escenario 1 ===
1) si los cambios/movimientos/re-asignaciones/... de rutas, carpetas y sub-carpetas ocurre por sistema operativo...
mientras los libros vinculados estan cerrados (creo que) sera logico que... ni se enteren de lo que esta pasando
(como decir: como esperas que "beto" (ausente/dormido/...) se entere de que "benito" SE CAMBIO DE DIRECCION ???)
2) para que no te afecte mover/cambiar/... un archivo entre rutas y carpetas...
a) procura que todos los libros vinculados esten en el mismo nivel o...
b) mueve/cambia/... los libros vinculados ESTANDO ABIERTOS TODOS por medio de (menu) archivo / guardar como...
3) o (de lo contrario)... desarrollar un procedimiento que escanee las unidades disponibles (incluir unidades en red ?)
que se encargue de ubicar las (posibles) instancias del (o los) archivo/s movido/s...
avisar por los desaparecidos (si fuera el caso) y/o (posibles) duplicados indicando otras propiedades, como fecha...)
etc. etc. etc. o sea que probablemente salga mas caro... el caldo... que las albondigas
(por aquello de utilizar llamadas a las API's, consumo de recursos y demora en la busqueda por las unidades, ...)
4) o... (simplemente) seguir usando las herramientas normales para modificar los vinculos de los archivos
o la sugerencia de conservar los vinculos SIEMPRE en las ubicaciones originales (o en el mismo nivel)
(de ser posible... todo en un solo directorio, si el caso sera de traslados de los archvos a diferentes pc's)
=== escenario 2 ===
cuando se trata de varios libros que estan vinculados entre si, (es importante) tener en cuenta que...
1) la base de la maquinaria de excel (como hoja "de calculo") es guardar la pista (rastro o tracking) de...
cuales celdas dependen de cuales otras (lo que incluye donde estan ubicadas -hojas y libros-)
2) cada libro maneja (a voluntad del usuario) algunas opciones para el recalculo del modelo, p.e.
en (menu) herramientas / opciones / (ficha) calcular -> (seccion) opciones del libro...
existen 2 que (especificamente) se encargan de la forma de manejar los vinculos externos:
- actualizar referencias remotas
- guardar valores de vinculos externos
toma nota que, al ser a voluntad del usuario... varios libros (de esos varios libros) pudieran ser diferentes
3) si no quieres abrir todos los libros (en la misma sesion) o guardarlos como area de trabajo (que es lo mismo)
tendras que ASEGURARTE que el orden de las precedencias (de vinculos) se actualiza apropiadamente
incluso (te sugiero) disenar la estructura de las dependencias entre los varios libros (un diagrama de flujos ?)
un sencillo ejemplo (que se me ocurre) seria si suponemos 3 libros que...
A toma de B y C
B toma de C
a) abres solamente C, modificas, grabas los cambios y... cierras C
b) abres solamente A... que te imaginas que pasa en A con las referencias de B que... NO se han actualizado?
ahora imaginate si el caso fuera en que C TAMBIEN toma referencias de A ???
4) ahora imaginate si los ibros los cambias/mueves/... de rutas, carpetas y sub-carpetas por sistema operativo...
mientras los libros vinculados estan cerrados (creo que) sera logico que... ni se enteraran de lo que esta pasando
podrias visitar la pagina de Charles Williams, que es una de las que tratan con mayor profundidad este tipo de problemas
toma en cuanta que dentro de las mismas paginas, existen enlaces a otros sitios y articulos con informacion pertinente
-> http://www.decisionmodels.com/optspeedd.htm
esta seccion trata de los cuellos de botella segun el arbol de dependencias en formulas y vinculos en hojas de excel
=== escenario 'n' ===
-> algun detalle que hubieras podido dejar "en el tintero" ?
H�ctor Miguel
>> ... problema con los vinculos en excel... en unidades de red ...
>> ... algunas veces guarda el vinculo con el path H:\UnArchivoExcel.xls
>> y otras veces lo guarda como \\servidor\directorio\UnArchivoExcel.xls.
>> El problema surge cuando muevo el directorio ya que se pierden los vinculos y dejan de funcionar.
>> En este momento cuento con una macro que busca los vinculos que comienzan con \\
>> lo que necesitaria saber es como poder modificar el camino \\servidor\directorio\ por H:\ con VBA...
no hagas caso de la macro (si de todo lo demas) :D
otras aternativas...
- buscar y reemplazar el origen de los vinculos externos por menus y comandos (depende de tu version de excel)
- buscar y reemplazar texto en las formulas de tus hoja (solo no te equivoques con alguna ruta inexistente)
saludos,
hector.
Claudia Alvarez
Te cuento un poco más sobre este asunto...
Según me han comentado, el que se guarden los vínculos de distintas formas
es un bug de excel. Esto no sucede en Office 97 y es lo que se está usando
para crear vínculos a partir de la detección del problema. Pero todo lo que
se hizo hasta ese momento está como comenté en el post anterior. Estamos
hablando de cientos de archivos excel con muchos vínculos, no sé si decir
miles...
La razón de este cambio es por una migración del servidor en el que se
encuentran estos archivos, tanto los archivos a los que se referencia en los
vínculos (hijos) como los que son referenciados (padres).
El nombre de la unidad de red se mantiene sin cambio, lo que va a cambiar en
este caso es el principio del camino, en lugar de ser \\servidor\directorio
va a ser \\nuevo_servidor\directorio y ambos se llamarán H.
Si la carpeta se mueve dentro del mismo servidor, también se pierden los
vinculos. Es decir que para que todo funcione bien dentro del mismo servidor
también debería poder cambiar el camino de los vínculos.
Ya que heredé esta macro que solamente busca los vínculos, sería de mucha
utilidad para mi que cuando los encuentre los pueda modificar. Mis
conocimientos de VBA son escasos por lo que mi búsqueda se centra en alguna
función de VBA que permita modificar el path de los vínculos.
Las siguientes líneas sacadas de tu respuesta hacen lo que quiero?
If Salto.TextToDisplay = Salto.Address Then Salto.TextToDisplay = Ahora
Salto.Address = Application.Substitute(LCase(Salto.Address), LCase(Antes),
Ahora)
End If
Por qué debo olvidarme de la macro?
Gracias!!
H�ctor Miguel
_____
> Por que debo olvidarme de la macro?
porque la macro es para corregir "hi-per-vin-cu-los" (los que aparecen en azul y subrayados para "saltar a... otro documento")
y NO las referencias en formulas que se "apuntan" a celdas/rangos/nombres/... en otros libros/hojas/... de excel :-((
_____
> Las siguientes lineas sacadas de tu respuesta hacen lo que quiero?
>
> If Salto.TextToDisplay = Salto.Address Then Salto.TextToDisplay = Ahora
> Salto.Address = Application.Substitute(LCase(Salto.Address), LCase(Antes), Ahora)
> End If
nop !!! (por la misma razon anterior): la variable "Sato" hace referencia a "hi-per-vin-cu-los" en el documento
_____
> Te cuento un poco mas sobre este asunto...
> Segun me han comentado, el que se guarden los vinculos de distintas formas es un bug de excel.
(creo que...) mas que tratarse de un (posible) "bug" del programa (pareciera que) tiene sus origenes en lo siguiente...
_____
> Esto no sucede en Office 97 y es lo que se esta usando para crear vinculos a partir de la deteccion del problema.
> Pero todo lo que se hizo hasta ese momento esta como comente en el post anterior.
> Estamos hablando de cientos de archivos excel con muchos vinculos, no se si decir miles...
desde la version 97 (la que usan para "crear" los vinculos) hasta la version 2007 (donde se presenta un cambio de comportamiento)
han pasado varias versiones y... si con cada cambio de version hay cambios en las prioridades de los comportamientos... (???)
_____
> La razon de este cambio es por una migracion del servidor en el que se encuentran estos archivos
> tanto los archivos a los que se referencia en los vinculos (hijos) como los que son referenciados (padres).
> El nombre de la unidad de red se mantiene sin cambio, lo que va a cambiar en este caso es el principio del camino
> en lugar de ser \\servidor\directorio va a ser \\nuevo_servidor\directorio y ambos se llamaran H.
- si la unidad "logica" sigue siendo "H" cuando abres los libros vinculados (los libros "hijos" que toman datos de los "padres")
a) podrias identificar el momento en que las referencias (vinculos) cambian su nomenclatura de H:\ a convencion UNC: "servidor\..." ?
b) podrias confirmar/correjir... si EN la version 97 los vinculos quedan con que tipo de referencia (H:\ ? 0 "servidor\..." ?)
c) que se tiene establecido en ambas versiones (97 y 2007) para la propiedad del archivo en "base del hipervinculo: ..."
- para 97 buscarlo en (menu:) archivo / propiedades / (ficha) resumen / -> Base del hipervinculo
- en 2007 en (boton) office / (ficha) preparar / (comando) propiedades / (desplegable) propiedades avanzadas / (ficha) resumen ...
_____
> Si la carpeta se mueve dentro del mismo servidor, tambien se pierden los vinculos.
> Es decir que para que todo funcione bien dentro del mismo servidor tambien deberia poder cambiar el camino de los vinculos.
(hasta donde se...) los vinculos son por lo general "relativos" a la ubicacion de los archivos (es decir...)
si los libros (padres e hijos) estan en el mismo "nivel de directorio", la ruta debiera mantenerse
> Ya que herede esta macro que solamente busca los vinculos
> seria de mucha utilidad para mi que cuando los encuentre los pueda modificar.
> Mis conocimientos de VBA son escasos por lo que mi busqueda se centra en alguna funcion de VBA
> que permita modificar el path de los vinculos.
prueba exponiendo la macro que heredaste (probablemente ande "muy cerca" ?)
y/o comentar (o probar si no lo has hecho) si te funciona alguna de las sugerencias del mensaje anterior...