File has grown to 300,000 KB on Disk
I am attempting to Reduce the File size of a given file (present size above)
and
am this far in the process. I have one small problem with the output of
this code,
For example in the Big File (ScrWB) in a cell I have =+Daily!B35 << which
is Fine
I am getting in the Finalized Small File (DesWB) the same cell
=+'[2007 11 CompressMacro.xls]Daily'!B35
How Can I eliminate the +'[2007 11 CompressMacro.xls]
so that I get only the =+Daily!B35
Thanks In Advance for any assistance..
Sub CompressFile()
Dim ScrWB As Workbook
Dim DesWB As Workbook
Dim DesFileName As String
Dim ScrShCount As Integer
Application.ScreenUpdating = False
Set ScrWB = ActiveWorkbook
ScrShCount = ScrWB.Sheets.Count
DesFileName = ScrWB.Name
If Right(DesFileName, 4) = ".xls" Then
DesFileName = Left(DesFileName, Len(DesFileName) - 4)
End If
With Workbooks.Add
.SaveAs DesFileName & Format(Date, "mmddyyyy")
End With
Set DesWB = ActiveWorkbook
For CurSh = 1 To ScrShCount
ScrWB.Sheets(CurSh).Copy After:=DesWB.Sheets(CurSh)
Next CurSh
DesWB.Sheets(1).Activate
DesWB.Sheets(1).Delete
Application.ScreenUpdating = True
Ans = MsgBox("Do You Wish to ReSave of this Newly Compressed Workbook?",
vbYesNo)
If Ans = vbYes Then
DesWB.Save
Else
Exit Sub
End If
End Sub
|