Como reducir el tamaño de un archivo

[mjrofra]

¡Hola a todos!

He visto que una pregunta frecuente es acerca de cómo reducir el tamaño de un archivo. Bueno, hay una causa en específico que puede afectar considerablemente el tamaño de un archivo: El rango de celdas que Excel guarda como usado en cada hoja.

Se puede encontrar fácilmente la última celda del rango usado presionando Ctrl+Fin en cualquier hoja. El problema es que en muchos casos la 'última celda' está mucho más allá del rango 'real' de celdas usadas... esto por lo general se debe a aplicar formatos a celdas que luego no se van a usar, lo que hace que luego Excel guarde esas celdas como parte del rango usado y así crece 'misteriosamente' el tamaño del archivo.

Este problema en particular me llamó la atención porque he visto muchos archivos que se suben en servidores por fuera del foro porque son demasiado grandes para adjuntarlos acá, pero al abrirlos en algunos casos he visto que son archivos con pocos datos, pero con tamaños descomunales... esto debido a que se está guardando como rango usado en cada hoja una cantidad considerable de celdas que no hacen parte del rango 'real' usado.

Bueno, no sé si la explicación sea clara. En todo caso, pensando en algunos de esos casos, escribí esta macro que deberá ayudar a reducir considerablemente el tamaño de dichos archivos 'limpiando' el rango usado y dejando sólo del tamaño del rango 'real' usado.

Esta es el código:

Edito: La macro que propuse inicialmente, gracias a los aportes de quienes han participado en este tema, ha sido mejorado. Para evitar confusiones, copio acá el código final:

Sub Limpiar_rangos()



    Dim hj As Excel.Worksheet

    Dim copia$, ffin&, cfin&, TI&, TF&



    copia = crear_copia(ActiveWorkbook)

    MsgBox "Se ha creado una copia: " & vbLf & copia, vbInformation



    With ActiveWorkbook


        TI = VBA.FileLen(.FullName)


        For Each hj In .Worksheets


            ffin = 1

            cfin = 1


            With hj


                On Error Resume Next

                ffin = .UsedRange.Find(what:="*", _

                                       LookIn:=xlFormulas, _

                                       SearchOrder:=xlByRows, _

                                       SearchDirection:=xlPrevious).Row

                cfin = .UsedRange.Find(what:="*", _

                                       LookIn:=xlFormulas, _

                                       SearchOrder:=xlByColumns, _

                                       SearchDirection:=xlPrevious).Column

                On Error GoTo 0


                If .ProtectContents Then

                    If MsgBox("La hoja " & .Name & " se encuentra protegida." & vbLf & vbLf & _

                              "No se podrán limpiar los rangos de esta hoja a menos que se desproteja." _

                              & vbLf & vbLf & "¿Desea desprotegerla antes de continuar?", vbYesNo, _

                              "¡Hoja protegida!") = vbYes Then

                        If Desproteger(hj) Then

                            Limpiar hj, ffin, cfin

                        Else

                            MsgBox "No se ha desprotegido la hoja.", vbCritical, "¡Clave incorrecta!"

                        End If

                    End If

                Else

                    Limpiar hj, ffin, cfin

                End If


            End With


        Next hj


        .Save


        TF = VBA.FileLen(.FullName)


    End With



    MsgBox "Tamaño original: " & VBA.Format(TI, "#,##0") & " bytes." & vbLf & vbLf & _

           "Tamaño final: " & VBA.Format(TF, "#,##0") & " bytes." & vbLf & vbLf & _

           "El archivo se redujo en: " & VBA.Format(TI - TF, "#,##0") & " bytes" & _

           " (" & VBA.FormatPercent(Abs(TI / TF - 1), 2) & ")."



End Sub

Private Sub Limpiar(ByVal hj As Excel.Worksheet, ByVal ffin As Long, ByVal cfin As Long)


    With hj


        With .Range(.Cells(ffin + 1, 1), .Cells(.Rows.Count, 1)).EntireRow

            If .MergeCells = False Then .Clear

        End With


        With .Range(.Cells(1, cfin + 1), .Cells(1, .Columns.Count)).EntireColumn

            If .MergeCells = False Then .Clear

        End With


    End With


End Sub

Private Function crear_copia(ByVal Libro As Excel.Workbook) As String


        With Libro

            .Save

            crear_copia = .Path & Application.PathSeparator & VBA.Format(VBA.Now, "d-m-yy h-mm ") & .Name

            .SaveCopyAs crear_copia

        End With


End Function

Private Function Desproteger(ByVal hj As Excel.Worksheet) As Boolean


    On Error Resume Next

    hj.Unprotect

    Desproteger = Not VBA.CBool(Err.Number)

    On Error GoTo 0


End Function[/CODE]

Reducir el tamaño de un archivo de Excel

Reducir tamaño archivo.zip

[Invitado luigi]

Re: Reducir tamaño de archivo

Hola Mauricio:

Fenomenal aporte el tuyo.

He probado lo que has enviado y a mí me funciona perfectamente me redujo un par de archivos que tenemos en el trabajo y los enviamos por correo electrónico a diario y los reduce en peso casi a la mitad.

Muchas Gracias por tus aportes.

Un saludo:

José Luis

[solido]

Re: Reducir tamaño de archivo

Hola Mauricio.

Realmente excelente...

Hace mucho que se del problema de rangos de excel, y no imaginé que se podría solucionar.

Felicidades.

[mjrofra]

Re: Reducir tamaño de archivo

...He probado lo que has enviado y a mí me funciona perfectamente me redujo un par de archivos que tenemos en el trabajo y los enviamos por correo electrónico a diario y los reduce en peso casi a la mita..

Luis, gracias por tu comentario y gracias por tomarte el tiempo de probar la macro, me alegra que sea útil.

...Hace mucho que se del problema de rangos de excel, y no imaginé que se podría solucionar...

solido, igualmente, gracias por tu comentario. La macro intenta hacer lo que yo hago de forma manual con los archivos que presentan este problema. Si lo quieres hacer de forma manual, el 'truco' consiste en eleminar las celdas que están más allá del rango 'real' usado. Primero identificar si el rango usado que está guardando Excel sobrepasa el rango realmente usado de la hoja, esto lo hago presionando Ctrl+Fin. Luego voy a la última fila con datos y elimino las filas de ahí para abajo y luego voy a la última columna con datos y elimino todas las columnas de ahí en adelante. Finalmente guardo el archivo así excel guarda un nuevo rango usado en el que están sólo las celdas con datos... bueno, no sé si sea claro... eso es lo que 'intenta' hacer la macro hoja por hoja del libro activo.

Bueno, y cuando subí la macro todavía estaba peleando con las hojas protegidas, pues si están protegidas no se pueden eliminar celdas (que es lo que hace la macro)... he 'mejorado' el código para que en caso de haber hojas protegidas, pida la clave y las desproteja y ahí pase a limpiar esa hoja. Si no se desproteje o la clave es incorrecta continúa con la siguiente hoja. En el código anterior este paso había quedado empezado pero no estaba resuelto.

Editado: Para evitar confusiones, he dejado un sólo código en el post inicial de este tema: Como reducir el tamaño de un archivo

[rolano]

Re: Reducir tamaño de archivo

Hola Mauricio:

Excelente aporte me quito el sombrero, bueno y de paso para saludarte.

[Jamel]

Re: Reducir tamaño de archivo

Si que lo reduce, gracias por el aporte.

[Gerson Pineda]

Re: Reducir tamaño de archivo

Hola Mauricio

No queda mas que decirte el código esta genial y gracias... realmente nos demuestra la gran generosidad con el foro! y lo digo aquí públicamente eres un gran tipo

Este aporte es muy bueno, al igual que todos los que nos regalas, cada día

Nota: Mauricio por el gran valor y genialidad de este aporte, lo voy a adherir, claro sino te molesta?

Saludos desde Honduras

[mjrofra]

Re: Reducir tamaño de archivo

Rolano, me alegra saludarte, gracias por tu comentario, acá seguimos aprendiendo.

Jamel, gracias por tomarte el tiempo de probar la macro y por la retroalimentación, me alegra que hasta ahora todo esté funcionando bien, jeje, esperemos que siga así.

Gerson, muchas gracias por la generosidad de tu comentario. Yo antes estaré muy halagado si decides adherir este tema, la idea es que sea de utilidad quien lo necesite así que puedes disponer de él según lo que mejor te parezca para sacarle provecho .

Igual, aún está en prueba y agradezco a quienes se tomen el tiempo de probarlo para verificar que funciona correctamente en diversas condiciones y cualquier idea para mejorar la macro es bienvenida .

Y como está en construcción, jajaja, pues viendo que ha tenido una buena demanda se me ocurrió que de pronto sea mejor crear una copia del archivo y trabajar con la copia, así no se toca el archivo original... este sería el nuevo código:

Editado: Para evitar confusiones, he dejado un sólo código en el post inicial de este tema: https://www.ayudaexcel.com/foro/showthread.php?t=11566&p=55247&viewfull=1#post55247

Este código hace lo mismo que el anterior, pero sobre un copia del archivo original evitando el peligro de pérdida de datos valiosos... recomendaría usar este código. El archivo creado se llamara "Reducido nombre_original" y se guardará en la misma carpeta y con el mismo formato (.xls, .xlsm .... ). Igual, si se quiere modificar directamente el archivo original se puede usar el anterior, pero me eximo de cualquier responsabilidad ante resultados catastróficos .

[Jamel]

Re: Reducir tamaño de archivo

Aunque tengo una consulta a un archivo que tenia imagenes me las desaparecio todas.

No se mucha de macros pero es por q no forma parte de alguna celda

[mjrofra]

Re: Reducir tamaño de archivo

Aunque tengo una consulta a un archivo que tenia imagenes me las desaparecio todas.

No se mucha de macros pero es por q no forma parte de alguna celda

Tienes razón Jamel, el problema es porque la macro elimina las celdas... creo que el problema se puede solucionar si en lugar de eliminar las filas (.delete), se borran (.clear):

Editado: Para evitar confusiones, he dejado un sólo código en el post inicial de este tema: https://www.ayudaexcel.com/foro/showthread.php?t=11566&p=55247&viewfull=1#post55247

Si puedes probar nuevamente a ver qué resultado te da... espero que esto solucione el problema que mencionas... y gracias por informar de ese problema .

[Gerson Pineda]

Re: Reducir tamaño de archivo

Hola Jamel

Lo probaste de nuevo (segun solicitud de Mauricio, nos comentas...)

Saludos desde Honduras

[supervoro]

Re: Reducir tamaño de archivo

MIs respetos Mjrofra....,como siempre..... sensacional.

[Jamel]

Re: Reducir tamaño de archivo

Hola lo estaba probando en el archivo donde justamente me salio el error, bueno insertando la codificacion la macro se detiene en:

Private Sub Limpiar(ByRef Hoja As Worksheet, ByVal ffin As Long, ByVal cfin As Long)

With Hoja

.Range(.Cells(ffin, 1), .Cells(.Rows.Count, 1)).EntireRow.Clear

.Range(.Cells(1, cfin), .Cells(1, .Columns.Count)).EntireColumn.Clear

End With

End Sub

Bueno hay adjunte el archivo la macro ya esta colocada.

ContraseñasPRUEBA1.zip

[mjrofra]

Re: Reducir tamaño de archivo

Gerson, una vez más, gracias .

Supervoro, muchas gracias por tu comentario... en realidad creo que la macro se puede pulir bastante (yo no soy más que un aficionado)... estoy tratando de evitar el máximo de complicaciones que se puedan presentar, acá seguimos esperando sugerencias y comentarios... He encontrado en la página de Debra Dalgleish una macro muy similar a la que he construido yo: http://www.contextures.com/xlfaqApp.html#Unused

Sin embargo, como menciono, estoy tratando de que la macro corra lo más 'limpia' posible, por eso he añadido una verificación en caso de que hayan páginas protegidas y ahora que Jamel menciona, primero, que se borran las imágenes, evitar eso, y ahora el error que presenta con las celdas combinadas... así que este último código que subo creo que lo hará bastante bien ... espero... al inicio, lo primero que hace es preguntar si se desea crear una copia del archivo (por seguridad) y luego si pasa a limpiar el rango usado de las hojas (si hay celdas combinadas, como en el archivo que sube Jamel, no podrá limpiar esas celdas):

Editado: Para evitar confusiones, he dejado un sólo código en el post inicial de este tema: https://www.ayudaexcel.com/foro/showthread.php?t=11566&p=55247&viewfull=1#post55247

[Carlos]

Mauricio;

Buen día, que gran aporte, me lo quedaré.... Y a petición de Gerson, el tema se queda como Importante (Siempre visible) ya que será de mucha ayuda para tanto el usuario que tenga duda como para quién ayudará.....

Yo también me quito el sombrero....

[Gerson Pineda]

Mauricio

Tal como te lo mencione antes (aqui mismo en el tema) y como ves te he cumplido (tambien lo veras en las normas del foro)

Tengo otras ideas en la cabeza se las voy a proponer a Proper a ver que le parecen... Carlos gusto saludarte

Saludos!

[Manfred450]

Este hilo resulta muy útil y de consulta permanente, por lo que me gustaría aportar. Espero no estar violando las reglas del foro, posteando nuevamente en este hilo.

Como sabrán, el principal factor que influye en el tamaño ("peso") de un archivo Excel es (en las palabras de mjrofra):

El rango de celdas que Excel guarda como usado en cada hoja.

Pero además hay otro detalle que suma bytes y bytes: el código de VBA.

Para atacar este problema, hay 2 recomendaciones a tener en cuenta (por experiencia propia):

1) Buscar reducir lo más posible las líneas de código utilizadas. Esto es un delicado balance donde hay que ponderar la reiteración de procesos, dimensionamiento de variables, anotaciones (comentarios), etc, etc para no comprometer el funcionamiento (velocidad y estabilidad) por ahorrarse sólo unos pocos Kbytes.

2) Considerar que escribir código en varios módulos no afecta demasiado al tamaño final del archivo, pero sí beneficia enormemente alivianando la tarea del desarrollador.

2bis) Lo anterior es cierto tanto para Módulos como para Módulos de Clase... pero no así para Formularios! Cuantos más formularios haya, el incremento de tamaño será más notable. Mi recomendación es reutilizar cada formulario lo más posible: manipulando sus propiedades y las de sus objetos mediante instrucciones en el código, se pueden obtener formularios diferentes en tiempo de ejecución.

Pero lo más importante:

Recientemente encontré el add-in VBA Code Cleaner 5.0, que es gratuito y se descarga de VBA Code Cleaner

¿Qué es lo que hace? Según "Acerca de":

[...] "Durante el proceso de creación de programas VBA una gran cantidad de código basura se acumula en los libros de su proyecto. Este código no deseado no se puede ver, pero si no se lo limpia periódicamente, sus proyectos pueden comenzar a experimentar problemas extraños. Limpiar un proyecto consiste en exportar los contenidos de todos los componentes VBA a archivos de texto, eliminar dichos componentes y luego importarlos nuevamente en el proyecto a partir de los archivos de texto." [...]

Lo poco que he usado esta herramienta me ha dado excelentes resultados, reduciendo los tamaños de archivo entre un 7 y un 15%.

[Gerson Pineda]

Hola Manfred

Interesante informacion

Saludos

[JoaoM]

Muy buena la macro, probada y comprobada en positivo.

Solo una interrugante; si tengo cierto rango que selecioné y rellene de cierto color para llenar de datos a posteriori, tambien me quita el color de ese rango, cosa que tengo que volver a rellenarlo. Digame si son 25 hojas tener que rellenar todas nuevamente cada ves que ejecute la macro? ¿se podria hcer algo al respecto?.

Por lo demas GRACIAS y muchas gracias.

El archivo para optimisar el codigo tambien es bien venido, probado y efectivamente, hace una reducion, falta probar si todo va normal como antes despues de ejecutado

[Maximiliano_93]

Editado: Para evitar confusiones, he dejado un sólo código en el post inicial de este tema:https://www.ayudaexcel.com/foro/showthread.php?t=11566&p=55247&viewfull=1#post55247

mjrofra muuchas gracias por la macro, funciona de mil maravillas con mis otros libros, cito el link con la modificación que se hizo pero me sale que el archivo no existe, ya que tengo el mismo error que Jamel con un libro

With hj

With .Range(.Cells(ffin + 1, 1), .Cells(.Rows.Count, 1)).EntireRow

If .MergeCells = False Then .Clear

End With

With .Range(.Cells(1, cfin + 1), .Cells(1, .Columns.Count)).EntireColumn

If .MergeCells = False Then .Clear

End With

End With

de antemano muchas gracias desde Chile

[pajuma]

Excelente aporte @[uSER=14326]mjrofra[/uSER] me sera de muxa ayuda.

Saludos,

[JoaoM]

mjrofra, tienes alguna solución para mi post #19?

Lo vengo usando en las plantillas que tenia y tengo con muy buenos resultados pero el color de fondo en las celdas (aparentemente vacias). lo elimina y no quisiera

[homosapiens999]

Estoy que no me lo creo, de 21.1MB el archivo bajó a solo 550KB... realmente sorprendente.

[JoaoM]

mjrofra, tienes alguna solución para mi post #19?

Lo vengo usando en las plantillas que tenia y tengo con muy buenos resultados pero el color de fondo en las celdas (aparentemente vacias). lo elimina y no quisiera

Perdon por la repeticion. Solo pregunte, desearia una respuesta en lo posible.

Si se puede haceralgo, muy bien, si no, pues a seguir co nel actual que bien queda

[Pablomanterola]

Excelente aporte. Funciona a la perfeccion. Me soluciono un problema laboral enorme. Saludos desde Argentina