Funcion VB4 Excel ARCHIVO (FILES)

Son muchas las novedades y funciones que Excel ha puesto a nuestra disposición en las últimas semanas (revisa los post anteriores a este sobre fórmulas desbordadas o dynamics arrays)... pero nunca debemos olvidar el pasado u origen de Excel, y en particular las funciones macro vb 4.0, reminiscencias del arcaico origen de la programación.

Hoy veremos una función interesante que nos devuelve un listado / matriz de los nombres de los ficheros incluidos en una carpeta o directorio especificado. Hablo de la función
=ARCHIVO(ruta as text)

Algo interesante es que el argumento de la función admite los comodines estándar de Excel (* y ?) para recuperar rutas.
Si no informáramos de ruta alguna, la función devolverá el listado de ficheros de la ruta corriente/actual donde se encuentre nuestro fichero de trabajo.

Para poder usar esta función deberemos crear un Nombre definido (que llamaré 'ListadoFicheros') con la función indicada:

No olvides guardar tu fichero como '.xlsm Libro habilitado para macros'.

Algunos usos...

En la celda A5 indicamos nuestra ruta:
E:\excelforo\Cambios\*
el asterisco final fuerza a listar una matriz con TODOS los ficheros contenidos.
Para recuperar este listado, en el rango B5:K5 añadimos
=SI.ERROR(INDICE(ListadoFicheros;1;COLUMNAS($B$4:B4));"-")

con la función INDICE recuperamos cada elemento de la matriz de ficheros...

Otro ejemplo en A7 donde insertamos la ruta:
E:\excelforo\Cambios\*.xls*
los comodines finales recuperarán únicamente los ficheros de Excel (.xls*).
Lo que hacemos en B7:K7 con la misma fórmula anterior:
=SI.ERROR(INDICE(ListadoFicheros;1;COLUMNAS($B$6:B6));"-")

Similar caso en A9 con la ruta
E:\excelforo\Cambios\*Facturas*
que permite recuperar cualquier archivo que contenga la palabra 'Facturas'.
Idéntica función en B9:K9
=SI.ERROR(INDICE(ListadoFicheros;1;COLUMNAS($B$8:B8));"-")

Otro uso sería convertir en vínculo cada elemento recuperado para, en un futuro, abrir el fichero en particular.
En A12 añadimos una ruta cualquiera, con o sin comodines
E:\excelforo\Cambios\*.xls*

y en B12:K12 añadimos la fórmula que incorpora el hipervínculo, empleando la función HIPERVINCULO:
=SI.ERROR(HIPERVINCULO(IZQUIERDA($A12;LARGO($A12)-6)&INDICE(ListadoFicheros;1;COLUMNAS($B$4:B4));INDICE(ListadoFicheros;1;COLUMNAS($B$4:B4)));"-")

Un último uso para el post de hoy nos responderá a una pregunta habitual... ¿Existe tal fichero en esa ruta???.
En A15 incluimos la ruta de la carpeta y en A16 el nombre del fichero...
Así en B15 insertamos:
=SI.ND(SI(COINCIDIR(A16;ListadoFicheros;0)>0;"Sí existe");"No existe")

donde se lee fácilmente que con los condicionales SI.ND y SI, junto con COINCIDIR, determinamos si existe o no el fichero en la ruta dada.