miércoles, 27 de marzo de 2013

Tamaño de fichero Excel excesivamente grande

Quizás os haya sucedido que trabajando con cualquier fichero Excel (versiones 2007 en adelante), que ocupa por ejemplo 5MB, sin darte cuenta, ha pasado a ocupar 15MB.

¿Por qué?

No hemos añadido nada fuera de lo normal como para que se triplique el tamaño (En mi caso contengo 50 hojas).

Bien, la explicación que da Microsoft al respecto es la siguiente:

Microsoft Excel sólo guarda la parte de cada hoja de cálculo que está en uso, lo que significa la sección que contiene datos o formato. A veces la última celda de una hoja de cálculo puede estar más allá del rango de datos utilizados realmente. Este problema puede hacer que tenga un tamaño de archivo mayor de lo necesario, que imprima páginas adicionales, que reciba mensajes de error "Memoria insuficiente" o que experimente otros comportamientos anómalos. El borrado de las filas y columnas sobrantes para restablecer la última celda puede ayudar a para resolver estos problemas.

Eso está muy bien, pero a veces cuesta encontrar las filas sobrantes.

Yo he cogido directamente el método 2, a priori más rápido y automático, pero con una salvedad, el complemento de Excel sólo se aplica a la versión en inglés de Excel.

No es más que un fichero xla, un Add-in de Excel, pero se comporta como un libro Excel y lo que contiene son macros.

Al abrir el complemento, se lanza el evento Workbook_Open, que intenta crear  una opción de menú. Pues bien el motivo por el cual sólo funciona en la versión en Excel en Inglés, es porque para crear la opción busca e menú “Chart Menu Bar” como literal, y evidentemente ese literal cambia en el resto de idiomas, con lo que al intentar abrirlo con otra versión de Excel, por ejemplo en Español  da un error.



Bien, este Add-in tiene dos partes, la creación de la opción de menú para llamar a la función que nos limpiará el código, y el código necesario para limpiar nuestro Excel.

Una manera de acceder, es darle a depurar y aparecerá el entorno de VBA, allí podréis ver un módulo llamado modprocs y dentro un procedimiento, ClearExcessRowsAndColumns.

La solución para por copiar dicho módulo el Excel que queremos limpiar. Si tenemos el Excel abierto, lo veremos en el visor de proyectos.

En la captura podemos ver los módulos del Excel a reparar y del Add-in.



Pues bien, arrastramos el modulo modprocs a la carpeta módulos de nuestro Excel, así obtendremos una copia.
Posteriormente, nos ubicamos sobre el Excel a reparar, por ejemplo seleccionando el módulo modprocs



Y desde la ventana inmediato llamamos al procedimiento ClearExcessRowsAndColumns.

Previamente, os aconsejo que desprotejáis todas aquellas hojas que dispongan de contraseña.

Luego no tenéis más que guardar de nuevo el fichero y veréis que ya se ha reducido de nuevo el tamaño a 5MB.

Otra cosa que os puede servir para conocer con un poco más de detalle qué hojas ocupan más espacio es el siguiente:
Realiza una copia de tu fichero .xlsx, .xlsm, etc. Y renombra la extensión a zip.
Si lo abres, verás la estructura interna del fichero Excel, esto es porque a partir de Excel 2007, los archivos están internamente comprimidos mediante ZIP.


Si entramos por ejemplo  al directorio xl, subdirectorio sheets, podemos ver el tamaño de cada hoja, así como la compresión aplicada.


Así podéis ver que hoja ocupa más, y tratar de optimizarla.

Espero que os haya servido de ayuda.

No hay comentarios:

Publicar un comentario