Cambiar una fórmula con referencias a otro libro

Te aconsejo que nos pongas el código que estás utilizando para asignarle a la celda su fórmula.
Un saludo.


Fernando Arroyo
MS MVP - Excel


"Esther Colero" escribió en el mensaje news:

Hola:

Tengo un libro llamado semana 50.xls que voy cambiando cada semana. Puedo
acceder a una celda que contiene una fórmula del tipo:

­14+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF14:AF15

y así es como se ve en la barra de fórmulas.

O sea, que cada libro contiene una referencia al libro de la semana
anterior. En realidad muchas, porque esto se extiende a lo largo de una
columna.

Pero si intento modificar esa cadena, y volver a asignarla a la propiedad
Formula, me da un error definido por la aplicación o definido por el objeto.
No se deja, y no entiendo porqué. En general, quisiera cambiar alguno de los
elementos que están entre las comillas simples. La parte que está fuera de
esas comillas permanece igual entre libros.

También he visto que si el libro que contiene los vínculos está abierto, la
parte que se muestra en la barra de fórmulas no incluye la ruta al libro, y
si está cerrado, la ruta completa sí aparece. No sé si esto tendrá algo que
ver.

Yo ya no sé cómo enfocar este asunto para verlo claro.

Lo que quiero es poder modificar la propiedad .Formula y a mano lo puedo
hacer y no protesta, pero lo que quiero precisamente es automatizar toda
esta historia, que es un quebradero de cabeza cada semana.

Saludos y besitos para todos, a ver si alguien me puede iluminar un poco.
Esther.

Bien pues aquí lo pongo. Está sin terminar, porque estoy atascada en esa
historia:

Primero una explicación con palabras. Tengo un libro que se llama semana
50.xls, que contiene una hoja llamada 05-11 11-11. Antes que éste tuve un
libro que se llamaba semana 49.xls con una hoja llamada 29-10 04-11, y mi
objetivo es generar un libro llamado semana 51.xls que contenga una hoja
llamada 12-11 18-11. Así para resumir, que me encanta enrrollarme toda: en
semana 50.xls hay referencias al libro semana 49.xls, y cuando intento crear
semana 51.xls (primera vez que intento hacerlo automáticamente con esta
historia), tendrá que haber referencias a semana 50.xls.

En el libro semana 50.xls, tomo la fórmula actual, que es muy larga debido a
la referencia al otro libro, y detecto las dos comillas simples. Lo que está
a la izquierda y a la derecha, lo conservo en Prefijo y Sufijo. Lo de dentro
de las comillas simples es lo que tengo que recomponer, con lo que contiene
FormulaDestino, que ya la traigo hecha de otra parte del programa.
Prácticamente el cambio consiste en que si la fórmula original pone

'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'
la modifico para que diga
'C:\PERSONAL\HORARIOS\[semana 50.xls]05-11 11-11'


Ahora un ejemplo de cómo son las fórmulas en las celdas. Ésta está en AF12:
­12+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF12:AF13


Y aquí el procedimiento:

Private Sub btnAceptar_Click()
Dim Prefijo As String
Dim Sufijo As String
Dim Original As String
Dim Nueva As String
Dim PosFinal As Integer

ActiveSheet.Copy
For n% = 11 To 217
Original = Cells(n%, "AF").Formula
PosFinal = Len(Original)
If Original <> "" And PosFinal > 20 Then
Prefijo = Left(Original, InStr(Original, "'") - 1)
PosFinal = InStr(10, Original, "'")
Sufijo = Right(Original, Len(Original) - PosFinal)
Nueva = Prefijo & FormulaDestino & Sufijo
'Cells(n%, "AF").Formula = Nueva
'como la de arriba fallaba, probé esta
'Range("AF" & CStr(n%)).Formula = Nueva
'como también fallaba, probé una fórmula sencillita, metiendo lo
primero que se me ocurrió
'y esa sí que la coge bien
'Range("AF" + CStr(n%)).Formula = "¡*D2"
'esta línea la usaba para comprobar que las dos fórmulas
'cambiaban justo como yo quería, y sí
MsgBox Original & Chr$(13) & Nueva
End If
Next n%

'esto de aquí para abajo está sin terminar, queda fuera del objeto de mi
consulta
ActiveSheet.Name = PestayaPropuesta
'cambiarle el nombre a la hoja
'cambiarle el nombre al libro
'guardarlo
'cerrarlo
End Sub



"Fernando Arroyo" escribió en el mensaje
news:
Te aconsejo que nos pongas el código que estás utilizando para asignarle a
la celda su fórmula.
Un saludo.


Fernando Arroyo
MS MVP - Excel


"Esther Colero" escribió en el mensaje
news:

Hola:

Tengo un libro llamado semana 50.xls que voy cambiando cada semana. Puedo
acceder a una celda que contiene una fórmula del tipo:

­14+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF14:AF15

y así es como se ve en la barra de fórmulas.

O sea, que cada libro contiene una referencia al libro de la semana
anterior. En realidad muchas, porque esto se extiende a lo largo de una
columna.

Pero si intento modificar esa cadena, y volver a asignarla a la propiedad
Formula, me da un error definido por la aplicación o definido por el

objeto.

No se deja, y no entiendo porqué. En general, quisiera cambiar alguno de

los

elementos que están entre las comillas simples. La parte que está fuera de
esas comillas permanece igual entre libros.

También he visto que si el libro que contiene los vínculos está abierto,

la

parte que se muestra en la barra de fórmulas no incluye la ruta al libro,

y

si está cerrado, la ruta completa sí aparece. No sé si esto tendrá algo

que

ver.

Yo ya no sé cómo enfocar este asunto para verlo claro.

Lo que quiero es poder modificar la propiedad .Formula y a mano lo puedo
hacer y no protesta, pero lo que quiero precisamente es automatizar toda
esta historia, que es un quebradero de cabeza cada semana.

Saludos y besitos para todos, a ver si alguien me puede iluminar un poco.
Esther.

Si tienes que cambiar la fórmula de todo el rango AF11:AF217, pienso que es mucho mejor que sea Excel quien se encargue de hacer el trabajo, para lo que sería necesaria una sola instrucción:

ActiveSheet.[AF11:AF217].Replace _
What:="49.xls]29-10 04-11", _
Replacement:="50.xls]05-11 11-11", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False

Un saludo.


Fernando Arroyo
MS MVP - Excel


"Esther Colero" escribió en el mensaje news:%

Bien pues aquí lo pongo. Está sin terminar, porque estoy atascada en esa
historia:

Primero una explicación con palabras. Tengo un libro que se llama semana
50.xls, que contiene una hoja llamada 05-11 11-11. Antes que éste tuve un
libro que se llamaba semana 49.xls con una hoja llamada 29-10 04-11, y mi
objetivo es generar un libro llamado semana 51.xls que contenga una hoja
llamada 12-11 18-11. Así para resumir, que me encanta enrrollarme toda: en
semana 50.xls hay referencias al libro semana 49.xls, y cuando intento crear
semana 51.xls (primera vez que intento hacerlo automáticamente con esta
historia), tendrá que haber referencias a semana 50.xls.

En el libro semana 50.xls, tomo la fórmula actual, que es muy larga debido a
la referencia al otro libro, y detecto las dos comillas simples. Lo que está
a la izquierda y a la derecha, lo conservo en Prefijo y Sufijo. Lo de dentro
de las comillas simples es lo que tengo que recomponer, con lo que contiene
FormulaDestino, que ya la traigo hecha de otra parte del programa.
Prácticamente el cambio consiste en que si la fórmula original pone

'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'
la modifico para que diga
'C:\PERSONAL\HORARIOS\[semana 50.xls]05-11 11-11'


Ahora un ejemplo de cómo son las fórmulas en las celdas. Ésta está en AF12:
­12+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF12:AF13


Y aquí el procedimiento:

Private Sub btnAceptar_Click()
Dim Prefijo As String
Dim Sufijo As String
Dim Original As String
Dim Nueva As String
Dim PosFinal As Integer

ActiveSheet.Copy
For n% = 11 To 217
Original = Cells(n%, "AF").Formula
PosFinal = Len(Original)
If Original <> "" And PosFinal > 20 Then
Prefijo = Left(Original, InStr(Original, "'") - 1)
PosFinal = InStr(10, Original, "'")
Sufijo = Right(Original, Len(Original) - PosFinal)
Nueva = Prefijo & FormulaDestino & Sufijo
'Cells(n%, "AF").Formula = Nueva
'como la de arriba fallaba, probé esta
'Range("AF" & CStr(n%)).Formula = Nueva
'como también fallaba, probé una fórmula sencillita, metiendo lo
primero que se me ocurrió
'y esa sí que la coge bien
'Range("AF" + CStr(n%)).Formula = "¡*D2"
'esta línea la usaba para comprobar que las dos fórmulas
'cambiaban justo como yo quería, y sí
MsgBox Original & Chr$(13) & Nueva
End If
Next n%

'esto de aquí para abajo está sin terminar, queda fuera del objeto de mi
consulta
ActiveSheet.Name = PestayaPropuesta
'cambiarle el nombre a la hoja
'cambiarle el nombre al libro
'guardarlo
'cerrarlo
End Sub



"Fernando Arroyo" escribió en el mensaje
news:
Te aconsejo que nos pongas el código que estás utilizando para asignarle a
la celda su fórmula.
Un saludo.


Fernando Arroyo
MS MVP - Excel


"Esther Colero" escribió en el mensaje
news:
> Hola:
>
> Tengo un libro llamado semana 50.xls que voy cambiando cada semana. Puedo
> acceder a una celda que contiene una fórmula del tipo:
>
> ­14+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF14:AF15
>
> y así es como se ve en la barra de fórmulas.
>
> O sea, que cada libro contiene una referencia al libro de la semana
> anterior. En realidad muchas, porque esto se extiende a lo largo de una
> columna.
>
> Pero si intento modificar esa cadena, y volver a asignarla a la propiedad
> Formula, me da un error definido por la aplicación o definido por el
objeto.
> No se deja, y no entiendo porqué. En general, quisiera cambiar alguno de
los
> elementos que están entre las comillas simples. La parte que está fuera de
> esas comillas permanece igual entre libros.
>
> También he visto que si el libro que contiene los vínculos está abierto,
la
> parte que se muestra en la barra de fórmulas no incluye la ruta al libro,
y
> si está cerrado, la ruta completa sí aparece. No sé si esto tendrá algo
que
> ver.
>
> Yo ya no sé cómo enfocar este asunto para verlo claro.
>
> Lo que quiero es poder modificar la propiedad .Formula y a mano lo puedo
> hacer y no protesta, pero lo que quiero precisamente es automatizar toda
> esta historia, que es un quebradero de cabeza cada semana.
>
> Saludos y besitos para todos, a ver si alguien me puede iluminar un poco.
> Esther.
>
>

Hola Fernando:

Esther de nuevo, dando la lata.

Efectivamente, cuando vi tu respuesta, me pareció sensacional. Mucho más
sencillo que la historia del bucle que me había inventado yo. A veces las
soluciones están delante mismo de uno, y no te das cuenta.

El caso es que cuando fui a hacerlo de la forma que me sugeriste, seguía
saliendo un mensaje de error, y seguía sin dejarme hacerlo. La diferencia
fue que el mensaje de error era mucho más explícito. Decía algo así como que
la referencia no era válida, que no se encontraba la ruta del libro al que
se hacía referencia, que me asegurara de que existía y de que lo había
escrito bien. Con tantas pistas, una ya se da cuenta de lo que está pasando.

Normalmente estas hojas de cálculo referenciadas unas a otras no están en mi
ordenador, sino en otro. De ese otro me mandaron sólo uno de los libros, y
los demás no los tenía yo. Y al cambiar cualquier referencia, el Excel la
comprueba, y si no encuentra el libro y la hoja correspondientes, no te deja
hacer el cambio. Esto era lo que estaba pasando desde el principio.

Saludos, y muchísimas gracias por ponerme en la pista, y por tu idea.
Esther.


"Fernando Arroyo" escribió en el mensaje
news:erbrcs%
Si tienes que cambiar la fórmula de todo el rango AF11:AF217, pienso que es
mucho mejor que sea Excel quien se encargue de hacer el trabajo, para lo que
sería necesaria una sola instrucción:

ActiveSheet.[AF11:AF217].Replace _
What:="49.xls]29-10 04-11", _
Replacement:="50.xls]05-11 11-11", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False

Un saludo.


Fernando Arroyo
MS MVP - Excel


"Esther Colero" escribió en el mensaje
news:%

Bien pues aquí lo pongo. Está sin terminar, porque estoy atascada en esa
historia:

Primero una explicación con palabras. Tengo un libro que se llama semana
50.xls, que contiene una hoja llamada 05-11 11-11. Antes que éste tuve un
libro que se llamaba semana 49.xls con una hoja llamada 29-10 04-11, y mi
objetivo es generar un libro llamado semana 51.xls que contenga una hoja
llamada 12-11 18-11. Así para resumir, que me encanta enrrollarme toda: en
semana 50.xls hay referencias al libro semana 49.xls, y cuando intento

crear

semana 51.xls (primera vez que intento hacerlo automáticamente con esta
historia), tendrá que haber referencias a semana 50.xls.

En el libro semana 50.xls, tomo la fórmula actual, que es muy larga debido

a

la referencia al otro libro, y detecto las dos comillas simples. Lo que

está

a la izquierda y a la derecha, lo conservo en Prefijo y Sufijo. Lo de

dentro

de las comillas simples es lo que tengo que recomponer, con lo que

contiene

FormulaDestino, que ya la traigo hecha de otra parte del programa.
Prácticamente el cambio consiste en que si la fórmula original pone

'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'
la modifico para que diga
'C:\PERSONAL\HORARIOS\[semana 50.xls]05-11 11-11'


Ahora un ejemplo de cómo son las fórmulas en las celdas. Ésta está en

AF12:

­12+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF12:AF13


Y aquí el procedimiento:

Private Sub btnAceptar_Click()
Dim Prefijo As String
Dim Sufijo As String
Dim Original As String
Dim Nueva As String
Dim PosFinal As Integer

ActiveSheet.Copy
For n% = 11 To 217
Original = Cells(n%, "AF").Formula
PosFinal = Len(Original)
If Original <> "" And PosFinal > 20 Then
Prefijo = Left(Original, InStr(Original, "'") - 1)
PosFinal = InStr(10, Original, "'")
Sufijo = Right(Original, Len(Original) - PosFinal)
Nueva = Prefijo & FormulaDestino & Sufijo
'Cells(n%, "AF").Formula = Nueva
'como la de arriba fallaba, probé esta
'Range("AF" & CStr(n%)).Formula = Nueva
'como también fallaba, probé una fórmula sencillita, metiendo lo
primero que se me ocurrió
'y esa sí que la coge bien
'Range("AF" + CStr(n%)).Formula = "¡*D2"
'esta línea la usaba para comprobar que las dos fórmulas
'cambiaban justo como yo quería, y sí
MsgBox Original & Chr$(13) & Nueva
End If
Next n%

'esto de aquí para abajo está sin terminar, queda fuera del objeto de mi
consulta
ActiveSheet.Name = PestayaPropuesta
'cambiarle el nombre a la hoja
'cambiarle el nombre al libro
'guardarlo
'cerrarlo
End Sub



"Fernando Arroyo" escribió en el mensaje
news:
Te aconsejo que nos pongas el código que estás utilizando para asignarle a
la celda su fórmula.
Un saludo.


Fernando Arroyo
MS MVP - Excel


"Esther Colero" escribió en el mensaje
news:
> Hola:
>
> Tengo un libro llamado semana 50.xls que voy cambiando cada semana.

Puedo

> acceder a una celda que contiene una fórmula del tipo:
>
> ­14+'C:\PERSONAL\HORARIOS\[semana 49.xls]29-10 04-11'!AF14:AF15
>
> y así es como se ve en la barra de fórmulas.
>
> O sea, que cada libro contiene una referencia al libro de la semana
> anterior. En realidad muchas, porque esto se extiende a lo largo de una
> columna.
>
> Pero si intento modificar esa cadena, y volver a asignarla a la

propiedad

> Formula, me da un error definido por la aplicación o definido por el
objeto.
> No se deja, y no entiendo porqué. En general, quisiera cambiar alguno de
los
> elementos que están entre las comillas simples. La parte que está fuera

de

> esas comillas permanece igual entre libros.
>
> También he visto que si el libro que contiene los vínculos está abierto,
la
> parte que se muestra en la barra de fórmulas no incluye la ruta al

libro,

y
> si está cerrado, la ruta completa sí aparece. No sé si esto tendrá algo
que
> ver.
>
> Yo ya no sé cómo enfocar este asunto para verlo claro.
>
> Lo que quiero es poder modificar la propiedad .Formula y a mano lo puedo
> hacer y no protesta, pero lo que quiero precisamente es automatizar toda
> esta historia, que es un quebradero de cabeza cada semana.
>
> Saludos y besitos para todos, a ver si alguien me puede iluminar un

poco.

> Esther.
>
>