Optimize VBA to Change File Format

[redbaron06]

Hi All,

I am using the below code to open a series of workbooks and change their file type to .xls (file type 56). The code takes a fusterating 35 minutes to run, and is part of a series of other modules. Anyone have any suggestions on how I could make this faster/more efficient? It seems that when each workbook opens, and after it is renamed it runs calculations despite ".Calculation = xlManual."

Thanks in advance!

Sub SaveTheFile()
Dim C As Range
Dim wb As Workbook
Dim wbName As String
Dim Tango1 As String
Dim Whiskey1 As String
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
 
For Each C In Range("W2:W69")
Tango1 = Range("C18").Value
Whiskey1 = "[****_***_*******]_POST_" & C
Workbooks.Open filename:=Tango1 & "[****_***_*******]_POST_" & C & ".xls"
Workbooks(Whiskey1).Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Tango1 & C & ".xls", FileFormat:=56
Application.DisplayAlerts = True
ActiveWorkbook.Close
Next C
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

 

[tweedle]

You can check the following attributes;

ThisWorkbook.ForceFullCalculation
ActiveSheet.EnableCalculation
ThisWorkbook.Application.CalculateBeforeSave

I'm pretty sure the CalculateBeforeSave = True fires the Calc even when .Calculation = xlManual.

 

[redbaron06]

You can check the following attributes;

ThisWorkbook.ForceFullCalculation
ActiveSheet.EnableCalculation
ThisWorkbook.Application.CalculateBeforeSave

Thanks tweedle,

So what would be the inverse of the above scripts. I want to stop the calcuations from occuring on workbookopen, so maybe

ActiveSheet.DisableCalculation ?

 

[tweedle]

This will disable the calculations for every sheet, irrespective of the Application.CalcMode.

Private Sub Workbook_Open()
For Each Worksheet In ActiveWorkbook.Sheets
    Worksheet.EnableCalculation = False
Next
End Sub

Edit: chgd ActiveSheet to Worksheet :: ActiveSheet is incorrect reference
This may cause confusion with users if they have any expectation to alter items and have them recalc. Also disables F9 - Recalc Now.

 

[redbaron06]

Thanks tweedle!! This works awesome.

Sub SaveTheFile()
Dim C As Range
Dim wb As Workbook
Dim wbName As String
Dim Tango1 As String
Dim Whiskey1 As String
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For Each C In Range("W2:W69")
Tango1 = Range("C18").Value
Whiskey1 = "[****_***_*******]_POST_" & C
Workbooks.Open filename:=Tango1 & "[****_***_*******]_POST_" & C & ".xls"
Workbooks(Whiskey1).Activate
[B]For Each Worksheet In ActiveWorkbook.Sheets
    Worksheet.EnableCalculation = False
Next
[/B]Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Tango1 & C & ".xls", FileFormat:=56
Application.DisplayAlerts = True
ActiveWorkbook.Close
Next C
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub