Reducir Archivo de Excel Pesado I

Varias veces me a sucedido que de repente un archivo que era de tan solo algunos kilobytes se convierte en un archivo muy grande de varios megabytes de tamaño y se vuelve muy lento y fastidioso trabajar con el y si se quiere enviar por correo no se puede.
Lo primero que yo hago para solucionar esto ver el tamaño del archivo, si es demasiado grande sigo utilizo un método un poco mas complejo que describo aquí, y si no es muy grande para ahorrar tiempo lo que hago es encontrar en que hoja u hojas esta el problema, la mayoría de las veces es solo una hoja que se ha vuelto muy pesada y para ello utilizo el siguiente código para encontrarla.

Se utilizan tres Apis de Windows, el primero para abrir el archivo, el segundo para cerrarlo y el tercero para obtener su tamaño en bytes.

Private Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function lclose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
Private Declare Function GetFileSize Lib "kernel32" (ByVal hFile As Long, lpFileSizeHigh As Long) As Long

Se declara una constante que especifica que el archivo se abra en modo de solo lectura

Private Const OF_READ = &H0&

Se utiliza una función para obtener el tamaño del archivo de kilobytes, esta función utiliza las tres Apis de Windows para obtener el tamaño del archivo

Public Function GetSize(path As String) As Single
    Dim Handle As Long
    Dim TamañoArchivo As Long
    Dim Libro As String
    Handle = lOpen(path, OF_READ)
    TamañoArchivo = GetFileSize(Handle,0)
    lclose Handle
    GetSize = TamañoArchivo / 1024
End Function

Y por ultimo la función que hace el trabajo principal que es lo siguiente

  1. Se especifica un incremento para encontrar la hoja pesada, por ejemplo si ponemos de incremento 1024 y el libro sin la hoja2 es de 100kb y con la hoja2 es de 1124kb entonces la hoja2 es la pesada porque tiene el incremento especificado
  2. Se especifica a partir de que hoja se va a empezar, esto por si ya se encontró una hoja pesada pero que su tamaño no es suficiente para ser la única responsable del tamaño del libro y se van a buscar mas hojas, entonces se empieza desde la siguiente hoja
  3. Se obtiene el numero de hojas del libro actual
  4. Se obtiene el nombre del libro actual
  5. Se crea y se guarda un nuevo libro que servirá para detectar el incremento del libro conforme se van agregando hojas
  6. Se copia una hoja del libro pesado al libro nuevo
  7. Se guarda el libro nuevo
  8. Se obtiene el tamaño del libro nuevo ya con la hoja
  9. Se revisa cual fue el incremento del libro y si fue mas grande que el especificado en el paso 1 muestra un mensaje de que se ha encontrado la hoja pesada
  10. Se repiten los pasos desde el paso 6 para cada hoja
  11. Se cierre el libro nuevo y se elimina
  12. Se muestra un mensaje de que se ha terminado el proceso

 

Sub DetectarHojaPesada()
    Application.ScreenUpdating = False 'Evita parpadeo de pantalla mientras se ejecuta el código actual
    Application.EnableEvents = False 'Evita que el código actual active otros códigos
    Application.Calculation = xlCalculationManual 'Evita que el código actual provoque re calcular las formulas
    Dim NombreLibro As String
    Dim NumeroHojas As Integer
    Dim HojaInicial As Integer
    Dim i As Integer
    Dim Tamaño1 As Long
    Dim Tamaño2 As Long
    Dim Temp As Workbook
    Dim Incremento As Integer
    Incremento = 1024 'Paso 1
    HojaInicial = 1 'Paso 2
    NumeroHojas = ThisWorkbook.Sheets.Count 'Paso 3
    NombreLibro = ThisWorkbook.Name 'Paso 2
    Set Temp = Workbooks.Add() 'Paso 5
    Temp.SaveAs Workbooks(NombreLibro).path & "\tempLibro.xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled
    For i = 1 To NumeroHojas
        Workbooks(NombreLibro).Sheets(i).Copy Before:=Temp.Sheets(1) 'Paso 6
        Temp.Save 'Paso 7
        Tamaño2 = CLng(GetSize(Temp.path & "\" & Temp.Name)) 'Paso 8
        If Tamaño2 - Tamaño1 >= Incremento Then 'Paso 9
            MsgBox "La hoja " & Workbooks(NombreLibro).Sheets(i).Name & " pesa mas de " & Incremento & " KB"
        End If
        Tamaño1 = Tamaño2
    Next 'Paso 10
    Temp.Close False 'Paso 11
    Kill Workbooks(NombreLibro).path & "\tempLibro.xlsm"
    MsgBox "Fin" 'Paso 12
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Estos codigos se ponen juntos en un modulo del libro pesado y se ejecuta la función DetectarHojaPesada.
codigo

Ejecutar

Resultado

Una vez que se ha encontrado la hoja pesaba se revisa para ver el porque de su tamaño y lo primero que se debe hacer es comprobar que solamente las celdas necesarias tengan formato y formulas ya que al utilizar formatos para demasiadas celdas y poner formulas a demasiadas celdas hace que el libro se vuelva muy pesado.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s