Suponha que ao receber um arquivo ele tivesse uns poucos Kbytes ou não mais do que algo entre 5 ou 6 MBytes. Aí, você efetuou algumas alterações pequenas, insignificantes, e salvou-o novamente na sua pasta. Neste momento você tem uma grande surpresa. Descobre que o tamanho da sua planilhas foi multiplicado entre 3 a 100 vezes mais!
Sim, isso é possível de acontecer no MS Excel.
Option ExplicitSub SHRINK_EXCEL_FILE_SIZE()
Dim WSheet As WorksheetDim CSheet As String 'New WorksheetDim OSheet As String 'Old WorkSheetDim Col As LongDim ECol As Long 'Last ColumnDim lRow As LongDim BRow As Long 'Last RowDim Pic As ObjectFor Each WSheet In WorksheetsWSheet.Activate'Put the sheets in a variable to make it easy to go back and forthLet CSheet = WSheet.Name'Rename the sheet to its name with _Delete at the end
LetOSheet = CSheet & "_Delete"
LetWSheet.Name = OSheet
'Add a new sheet and call it the original sheets nameSheets.Add
LetActiveSheet.Name = CSheet
Sheets(OSheet).Activate'Find the bottom cell of data on each column and find the further rowFor Col = 1 To Columns.Count 'Find the actual last bottom rowIf Cells(Rows.Count, Col).End(xlUp).Row > BRow Then
LetBRow = Cells(Rows.Count, Col).End(xlUp).RowEnd IfNext'Find the end cell of data on each row that has data and find the furthest oneFor lRow = 1 To BRow 'Find the actual last right columnIf Cells(lRow, Columns.Count).End(xlToLeft).Column > ECol Then
LetECol = Cells(lRow, Columns.Count).End(xlToLeft).ColumnEnd IfNext'Copy the REAL set of dataRange(Cells(1, 1), Cells(BRow, ECol)).CopySheets(CSheet).Activate'Paste Every ThingRange("A1").PasteSpecial xlPasteAll'Paste Column WidthsRange("A1").PasteSpecial xlPasteColumnWidthsSheets(OSheet).ActivateFor Each Pic In ActiveSheet.PicturesPic.CopySheets(CSheet).PasteSheets(CSheet).Pictures(Pic.Index).Top = Pic.TopSheets(CSheet).Pictures(Pic.Index).Left = Pic.LeftNext PicSheets(CSheet).Activate'Reset the variable for the next sheet
LetBRow = 0
LetECol = 0Next WSheet' Since, Excel will automatically replace the sheet references for you on your formulas,' the below part puts them back.' This is done with a simple replace, replacing _Delete with nothingFor Each WSheet In WorksheetsWSheet.ActivateCells.Replace "_Delete", ""Next WSheet'Roll through the sheets and delete the original fat sheetsFor Each WSheet In WorksheetsIf Not Len(Replace(WSheet.Name, "_Delete", "")) = Len(WSheet.Name) Then
LetApplication.DisplayAlerts = FalseWSheet.Delete
LetApplication.DisplayAlerts = TrueEnd IfNextEnd Sub
Tags: VBA, Excel,, size, small, reduce, file, archive, tamanho, planilha, reduzir
Nenhum comentário:
Postar um comentário