Esta consulta, la verdad, me dejó mal parado. Y quedé en ese estado por una simple razón: jamás me habían planteado semejante problema, al margen de que nunca necesité realizar esta tarea. Empezaré por explicarla, así nos "ponemos en sintonía", desde lo mas básico. Si bien es un problema muy puntual y probablemente muchos no necesiten este ejemplo, se los recomiendo igualmente, ya que se aprenderán cosas interesantes, que seguro les servirá en otros proyectos.
Todos sabemos que (salvo que hayamos definido referencias absolutas) al copiar y pegar una función en otro lado, la celda que pasamos como argumento se "corre". Si en A1 tengo: =B1*100, copio y pego esa función hasta A3, me quedan: =B2*100 y B3*100, respectivamente.
al pegar la funcion de A1 hacia abajo, la referencia a B1 se corrió hacia B2 y B3
[+/-] Ver el resto / OcultarEsto es muy útil, dado que nos evita tener que cambiar "a mano" las funciones.
Todos sabemos también que podemos ingresar una función que haga referencia a un celda que se encuentra en otro libro. Ahora le pediré a Excel que me muestre en la celda A1 de mi libro actual el valor que se encuentra en la celda B1 de otro libro:
vean la barra de fórmulas: el nombre del otro libro está entre llaves, luego viene el nombre de la hoja y por último el rango o celda que quiero reflejar en A1
Si al libro desde donde extraigo el dato lo cierro, la barra de formulas se vería:
aquí se agrega la ruta (path) del archivo.
Si tomo la formula que tengo en A1, la copio, y la pego hasta A3 las funciones quedarían:
='C:\Users\damian\Documents\blog\[graficosV.xls]Hoja3'!B1
='C:\Users\damian\Documents\blog\[graficosV.xls]Hoja3'!B2
='C:\Users\damian\Documents\blog\[graficosV.xls]Hoja3'!B3
O sea: la referencia a B1 se "corrió" sola hasta B3, como sucede con cualquier función común.
Pero mi lector me plantea algo verdaderamente distinto: que se vaya cambiando el nombre del archivo, no el del rango. El tiene estos archivos dentro de su directorio:
A4221.xls
A4222.xls
A4223.xls
Por cuanto, al copiar y pegar las funciones, debería quedar así:
='C:\Users\damian\Documents\blog\[A4221.xls]Hoja3'!B1
='C:\Users\damian\Documents\blog\[A4222.xls]Hoja3'!B1
='C:\Users\damian\Documents\blog\[A4223.xls]Hoja3'!B1
El necesita leer siempre la celda B1 de distintos archivos, pero quiere que Excel vaya incrementando, de forma automática, el nombre del archivo.
Lo primero que se me ocurrió para solucionar lo planteado fue la utilización de =INDIRECTO(). Si lograba concatenar los nombres y mezclarlos con números... luego le aplicaba dicha función y tema resuelto.
Algo así:
=INDIRECTO("'C:\Users\damian\Documents\blog\[A" & Variable & ".xls]Hoja3'!B1")
Pero =INDIRECTO() tiene un inconveniente: solo funciona con archivos abiertos. Y si nosotros, por ejemplo, obtenemos datos de 20, 30 o 50 archivos distintos.... realmente sería un incordio tener que abrirlos a todos. Chau indirecto(), esta vuelta no me arreglás nada.
Así que tuve que recurrir a las macros. Veremos un pequeño "paso a paso" antes de pasar al código, para luego comprenderlo mejor:
1) recorro el directorio en donde se encuentran los archivos
2) tomo todos los archivos con extensión "xls" o "xlsx"
3) con la ruta al directorio + nombre de archivo (obtenido en el paso anterior) + nombre de la hoja + celda puedo "armar" la función
4) pongo esa función en el libro y vuelvo al paso 1), hasta que no existan mas archivos por listar.
Mi procedimiento consta de 3 argumentos obligatorios:
Ruta: directorio en el cual se encuentran los archivos
Hoja: nombre de la hoja en donde está la celda que queremos leer
Rango: direccion (A1, B5, Z78 o cualquier otra) de la celda en cuestión.
¿Y por que pido argumentos? Por una razón muy sencilla: quizás el día de mañana, por una u otra razón, el origen de los datos cambie: se pueden llevar los archivos a otro directorio, o cambiar el nombre de la hoja... o bien necesitemos leer otra celda. Entonces, lo único que debemos modificar son los argumentos, logrando una buena modularización y portabilidad del código, el cual les detallo a continuación:
Aqui llamo a mi Sub, pasando los 3 argumento: ruta en donde se encuentran los archivos (en el caso de mi lector en otra pc de la red, por eso el path comienza con doble barra invertida), nombre de la hoja y celda desde la cual deseo leer el dato:
Y el que realiza el trabajo:
Una vez ejecutada la macro, este es el resultado:
Como dije mas arriba, quizás no utilicen este ejemplo, pero han visto como recorrer un directorio en busca de archivos. Utilicé el *.* para leer cualquier tipo de archivo, pero no olviden que esa búsqueda se podría refinar mas: *.doc para word, *.mdb para access, *.txt parar texto plano, y así de acuerdo a nuestras necesidades. También se vio como concatenar correctamente para evitar que el uso del apóstrofe ( ' ) genere problemas en la sintaxis de VBA.
Les dejo el link al archivo.
Gracias por los mails que periodicamente recibo y disculpen si me atraso un poco en contestar, pero el día debería tener un poco mas de 24 hs.
Aqui llamo a mi Sub, pasando los 3 argumento: ruta en donde se encuentran los archivos (en el caso de mi lector en otra pc de la red, por eso el path comienza con doble barra invertida), nombre de la hoja y celda desde la cual deseo leer el dato:
Sub IniciarPegadoDeFunciones() PegarIncrementandoLibro "\\WIN64\F_win64\damian\Excel\blog\" _ , "Hoja1", "B1" End Sub
Y el que realiza el trabajo:
Sub PegarIncrementandoLibro(Ruta As String, Hoja As String, Rango As String) Dim R, NuevaFormula As String Dim Fila, Columna As Integer 'si mi usuario omite poner la barra invertida al final, lo hago 'yo, para evitar errores: If Right(Ruta, 1) <> "\" Then Ruta = Ruta & "\" End If 'le agrego los comodines a la Ruta, para buscar cualquier archivo R = Dir(Ruta & "\*.*") Fila = ActiveCell.Row Columna = ActiveCell.Column 'recorro el directorio de Ruta While R <> "" 'si el nombre del archivo tiene cualquiera de las dos 'extenciones If Right(R, 3) = "xls" Or Right(R, 4) = "xlsx" Then 'y aquí armo la fórmula, por partes: 'primero el signo =, seguido por el apóstrofe. agrego la Ruta 'del directorio, nombre de archivo, nombre de hoja, apóstrofe 'signo de admiración (cierre) y rango NuevaFormula = "=" & "'" & Ruta & "[" & R & "]" & Hoja & "'" & "!" & Rango 'inserto la fórmula en la celda Cells(Fila, Columna) = NuevaFormula 'y aumento en 1 a Fila, para así continuar con la celda siguiente Fila = Fila + 1 End If R = Dir Wend End Sub
Una vez ejecutada la macro, este es el resultado:
noten como la macro siempre lee desde la Hoja1!B1, pero cambia en forma automática el nombre del libro.
Como dije mas arriba, quizás no utilicen este ejemplo, pero han visto como recorrer un directorio en busca de archivos. Utilicé el *.* para leer cualquier tipo de archivo, pero no olviden que esa búsqueda se podría refinar mas: *.doc para word, *.mdb para access, *.txt parar texto plano, y así de acuerdo a nuestras necesidades. También se vio como concatenar correctamente para evitar que el uso del apóstrofe ( ' ) genere problemas en la sintaxis de VBA.
Les dejo el link al archivo.
Gracias por los mails que periodicamente recibo y disculpen si me atraso un poco en contestar, pero el día debería tener un poco mas de 24 hs.
Comentarios
Publicar un comentario