Excel resource issue

[Kaps_mr2]

Hi,

Excel is saying that it does not have enough resources when I run the following code. The routine essentially opens a workbook called Nirvana multiple times, applies some parameters to the file, and then saves it as an output workbook.

The Nirvana workbook is very large 120 Mb in size. Currently I am able to perform 30 iterations (taking around 2 hours in total - with each iteration taking just 5 minutes) before Excel runs out of resources to open the file.

I am using a 64 bit machine with an I5 processor. Any thoughts ? thanks

Kaps

Public Sub acheive_nirvana()
Dim sheet_name As String
Dim current_country_name As String
Dim current_area_name As String
Dim current_region_name As String
Dim parameters_file_name As String
Dim parameters_workbook As Workbook
Dim branch_name As String
Dim i As Long
Dim copy_range As Range
Dim paste_range As Range
Dim range_to_copy As String
Dim output_workbook As Workbook
Dim output_path As String
Dim start_time As Date
Dim end_time As Date
 

range_to_copy = "A1:bb2000"
'open the nirvana workbook

current_country_name = main_workbook.Sheets("front sheet").Range("b" & top_row_of_bootstrap).Value
For i = top_row_of_bootstrap To last_row_of_bootstrap
     Application.Calculation = xlCalculationManual
  
    Workbooks.Open (nirvana_file_name)
    
      
    Set nirvana_workbook = ActiveWorkbook
    
    
    start_time = Now
    
    main_workbook.Sheets("front sheet").Range("k" & i).Value = start_time
    
    current_area_name = main_workbook.Sheets("front sheet").Range("c" & i).Value
    current_region_name = main_workbook.Sheets("front sheet").Range("d" & i).Value
    parameters_file_name = nirvana_path & "\" & current_country_name & "\" & current_area_name & "\" & current_region_name & "\" & parameters_workbook_name
    branch_name = main_workbook.Sheets("front sheet").Range("e" & i).Value
    
    
    'open the parameters file
    Workbooks.Open (parameters_file_name)
    Set parameters_workbook = ActiveWorkbook
    
    'define the copy range (copy FROM the parameters workbook)
    
    Set copy_range = parameters_workbook.Sheets(branch_name).Range(range_to_copy)
    
    'define the paste range (copy to the NIRVANA workbook)
    
    Set paste_range = nirvana_workbook.Sheets("Inputs").Range("a1")
    
    
    'copy over the data
    
    copy_range.Copy
    
    'now paste it
    
    paste_range.PasteSpecial , Paste:=xlPasteColumnWidths
    paste_range.PasteSpecial , Paste:=xlPasteFormulas
    paste_range.PasteSpecial , Paste:=xlPasteFormats
    
    'clear the clipboard
    
    
    
    Application.CutCopyMode = False
    
   
    
    'close the parameters file
    
    Application.DisplayAlerts = False
        parameters_workbook.Close
    Application.DisplayAlerts = True
    
    Set parameters_workbook = Nothing
    
  
    'now save the Nirvana workbook - to ensure that the calculations are done correctly
    
    
    output_path = nirvana_path & "\" & current_country_name & "\" & current_area_name & "\" & current_region_name & "\" & "outputs " & branch_name & ".xlsx"
    
    Application.DisplayAlerts = False
    
    ' MsgBox "here 1"
    With nirvana_workbook
    .Save
    End With
    
   ' MsgBox "here 2"
    
    'now convert the sheets to values - start with the input sheet
    
    Application.Calculation = xlCalculationManual
    
    nirvana_workbook.Sheets("Inputs").Range("a1:az3000").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
  '  MsgBox "here 3"
    'now the Hot shots
    
   ' nirvana_workbook.Sheets("hot shots").Range("a1:az300").Copy
   ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
   ' MsgBox "here 4"
  
    'now the financial summary
    
'     nirvana_workbook.Sheets("financial summary").Range("a1:az3000").Copy
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
   ' MsgBox "here 5"
    
     'now the tech detail
    
    nirvana_workbook.Sheets("tech detail").Range("a1:az3000").Copy
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    '  MsgBox "here 6"
    
    With nirvana_workbook
    .Sheets("Tech Hours").Delete
    End With
    
     'MsgBox "here 7"
    
    With nirvana_workbook
    .SaveAs output_path
    .Close
    End With
    
   ' MsgBox "here 8"
 
    Set nirvana_workbook = Nothing
       
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
      
    end_time = Now
    main_workbook.Sheets("front sheet").Activate
    
    
    main_workbook.Sheets("front sheet").Range("j" & i).Value = "YES"
    main_workbook.Sheets("front sheet").Range("l" & i).Value = end_time
    
     Application.ScreenUpdating = True
     
    
    
Next i

End Sub

 

[Derek Brown]

As you show the code, I am surprised that it runs as there appears to be a number of things that have not been 'initialised'.
If you use "Option Explicit", you will be made aware of missing declarations when you compile the code.
Below is a copy of your code with some suggestions but I guess that you will still have more to do:

Option Explicit '<=====
Public Sub acheive_nirvana()
Dim sheet_name As String
Dim current_country_name As String
Dim current_area_name As String
Dim current_region_name As String
Dim parameters_file_name As String
Dim parameters_workbook As Workbook
Dim branch_name As String
Dim i As Long
Dim copy_range As Range
Dim paste_range As Range
Dim range_to_copy As String
Dim output_workbook As Workbook
Dim output_path As String
Dim start_time As Date
Dim end_time As Date
'
'
'
'
' Not declared or initialised:
Dim top_row_of_bootstrap As Long '<=====
Dim last_row_of_bootstrap As Long '<=====
Dim main_workbook As Workbook
Dim nirvana_file_name As String '<=====
Dim nirvana_workbook As Workbook '<=====
Dim nirvana_path As String '<=====
Dim parameters_workbook_name As String '<=====
'
'
'
'
'
'
range_to_copy = "A1:bb2000"
'open the nirvana workbook
'
    ' set main_workbook = ???
    ' open main_workbook ???
'
current_country_name = main_workbook.Sheets("front sheet").Range("b" & top_row_of_bootstrap).Value
For i = top_row_of_bootstrap To last_row_of_bootstrap
     Application.Calculation = xlCalculationManual
     '
     '
    ' nirvana_file_name = ???
    ' nirvana_path = ???
    ' parameters_workbook_name = ???

' Change:
    ' Workbooks.Open (nirvana_file_name)
    ' Set nirvana_workbook = ActiveWorkbook
' to:
    Set nirvana_workbook = Workbooks.Open(nirvana_file_name)
'
    
    start_time = Now
    
    main_workbook.Sheets("front sheet").Range("k" & i).Value = start_time
    
    current_area_name = main_workbook.Sheets("front sheet").Range("c" & i).Value
    current_region_name = main_workbook.Sheets("front sheet").Range("d" & i).Value
    parameters_file_name = nirvana_path & "\" & current_country_name & "\" & current_area_name & "\" & current_region_name & "\" & parameters_workbook_name
    branch_name = main_workbook.Sheets("front sheet").Range("e" & i).Value
    
    
    'open the parameters file
' Change:
    ' Workbooks.Open (parameters_file_name)
    ' Set parameters_workbook = ActiveWorkbook
' to:
    Set parameters_workbook = Workbooks.Open(parameters_file_name)
'
    'define the copy range (copy FROM the parameters workbook)
    
    Set copy_range = parameters_workbook.Sheets(branch_name).Range(range_to_copy)
    
    'define the paste range (copy to the NIRVANA workbook)
    
    Set paste_range = nirvana_workbook.Sheets("Inputs").Range("a1")
    
    
    'copy over the data
    
    copy_range.Copy
    
    'now paste it
    
    paste_range.PasteSpecial , Paste:=xlPasteColumnWidths
    paste_range.PasteSpecial , Paste:=xlPasteFormulas
    paste_range.PasteSpecial , Paste:=xlPasteFormats
    
    'clear the clipboard
    
    
    
    Application.CutCopyMode = False
    
   
    
    'close the parameters file
    
    Application.DisplayAlerts = False
        parameters_workbook.Close
    Application.DisplayAlerts = True
    
    Set parameters_workbook = Nothing
    
  
    'now save the Nirvana workbook - to ensure that the calculations are done correctly
    
    
    output_path = nirvana_path & "\" & current_country_name & "\" & current_area_name & "\" & current_region_name & "\" & "outputs " & branch_name & ".xlsx"
    
    Application.DisplayAlerts = False
    
    ' MsgBox "here 1"
    With nirvana_workbook
    .Save
    End With
    
   ' MsgBox "here 2"
    
    'now convert the sheets to values - start with the input sheet
    
    Application.Calculation = xlCalculationManual
    
    nirvana_workbook.Sheets("Inputs").Range("a1:az3000").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
  '  MsgBox "here 3"
    'now the Hot shots
    
   ' nirvana_workbook.Sheets("hot shots").Range("a1:az300").Copy
   ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
   ' MsgBox "here 4"
  
    'now the financial summary
    
'     nirvana_workbook.Sheets("financial summary").Range("a1:az3000").Copy
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
   ' MsgBox "here 5"
    
     'now the tech detail
    
    nirvana_workbook.Sheets("tech detail").Range("a1:az3000").Copy
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    '  MsgBox "here 6"
    
    With nirvana_workbook
    .Sheets("Tech Hours").Delete
    End With
    
     'MsgBox "here 7"
    
    With nirvana_workbook
    .SaveAs output_path
    .Close
    End With
    ' ???????
    ' you appear to be opening the above workbook outside the loop
    ' and closing it within the loop
    ' ???????
    
   ' MsgBox "here 8"
 
    Set nirvana_workbook = Nothing
    '
    ' ????
    ' need to release other resources here?
    ' eg copy_range; paste_range
    ' ????
       
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
      
    end_time = Now
    main_workbook.Sheets("front sheet").Activate
    
    
    main_workbook.Sheets("front sheet").Range("j" & i).Value = "YES"
    main_workbook.Sheets("front sheet").Range("l" & i).Value = end_time
    
     Application.ScreenUpdating = True
    
    
Next i

End Sub

Objects should be used where necessary and then later set to nothing to release the resource.

 

[Kaps_mr2]

Hi Derek,

Thanks. I have only shown one subroutine. There is other code.all the variables you mentioned have been declared as global variables at the top of this module.

In addition there is already option explicit at the start of the code. I believe I setting to nothing all objects after use.

Regards

Kaps

 

[Derek Brown]

   'open the parameters file
    Workbooks.Open (parameters_file_name)
    Set parameters_workbook = ActiveWorkbook
'
'   'open the parameters file
    Workbooks.Open (parameters_file_name)
    Set parameters_workbook = ActiveWorkbook

I cannot be certain, as I always do it the way that I suggested, but I wonder if the workbook resource is being released?
You can use Task Manager to see what is being used during the run.
You did not say which version of Excel is being used and, although not related to your problem, Microsoft recommends Office 32-bit on 64-bit machines:
http://office.microsoft.com/en-us/w...-version-of-microsoft-office-HA010369476.aspx
However, at work we use Windows 7 64-bit and Office 2010 32-bit but do experience printer problems (seems to be an issue with splwow64).

 

[Kaps_mr2]

Thanks. I had been thinking about rewriting part of the code into another subroutine to ensure the workbook resource was reset properly. I will have a go at this.

I have heard mixed reports about 2010. Thanks again