PDA

View Full Version : Reduce the size of macro enable file



sindhuja
03-28-2012, 02:34 AM
Hi,

I have Created Excel File Which conntains Macros (104MB)

I need to know how can i reduce the size of excel.

Please assist

-Sindhuja

Bob Phillips
03-28-2012, 02:39 AM
Depends on why it is so big. Is it data? Is it lots of flashy visuals? Is it file bloat?

Paul_Hossler
03-28-2012, 05:22 AM
One thing that I've found that sometimes gives an indication as to the huge file size is to rename the .XLSM to .ZIP and open it as a zip file.

I can see the size of each WS, styles.xml, etc.

Paul

sindhuja
03-28-2012, 06:30 AM
It was macro enable file.. how to save it as .xlxs..

it was empty file with no visuals.. only coding available infile

i have used the below coding for saving
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Destwb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case Destwb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End With

Dim filename As String
Dim Reportdate As Variant
filename = InputBox("Enter the report name")
Reportdate = InputBox("Enter the reporting period")
TempFilePath = "C:\documents'"
TempFileName = (filename) & " " & (Reportdate)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.Close SaveChanges:=False
End With

Bob Phillips
03-28-2012, 07:31 AM
I am not sure what the point of that last post was. Was it to tell us that you have solved your problem by saving as an xlsx file? If not, then what exacty are you saying?

sindhuja
03-29-2012, 12:35 AM
The file is currently saved as macro enable file. I just want to know if the file can be saves as .xlsx file so that size can be reduced.Since the file contains coding and as per the vba code given in my last post it is saving as xlsm file.
Tried saving the file as .xlsx but its not allowing me to save. Please assist with this issue

Bob Phillips
03-29-2012, 01:35 AM
Why not save it manually as an xlsx, once the code is gone it isn't going to come back.

Paul_Hossler
03-29-2012, 11:11 AM
I have Created Excel File Which conntains Macros (104MB)


If most of the 104MB is macro or macro-related, I think that would be a record breaker

Paul