memory leak open/save/close

[Ssignore08]

Pretty simple code. I have daily files (dy and mo) for multiple years (yr) for different areas (ucode) that are in .txt format. I want them in excel so that I can use them later for other vba macros.

So there are four "For" loops, then I declare the file name and the path name. When the file is opened, I use the wizard to delineate the values correctly. Then I was to save as an excel file and close the file.

There is a memory leak somewhere as the memory usage in the processes tab goes 5x within running 3 months of files. No extra files are open when I stop the code. The memory allocation does not immediately recover either. I had this problem before putting in cutcopymode and screenupdateing =false

bonus if you have a better way of running through all of the files other than my current system w/ the format(mo,00) I would appreciate that, but this is secondary I believe.

Sub DailyData()
'
' Macro1 Macro
' open and extract files
'
'Dim mo As String
On Error GoTo Etrapper
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
For i = 23 To 31
      ucode = Cells(i, 1)
      ucap = UCase(ucode)
      
      For yr = 2000 To 2015
      
    
      'Application.ScreenUpdating = True
         
     Application.StatusBar = "Current iteration: " & i & "_" & yr
     
      For mo = 1 To 12
      For dy = 1 To 31
     
      
      mo = Format(mo, "00")
      dy = Format(dy, "00")
      fname1 = yr & mo & dy & ucode
      fname = yr & mo & dy & "." & ucode
    
 
    Pathname = "C:\Users\sg8\Documents\data\_" & ucode & "_Daily_Data_Request\" & ucode & yr & "\"
       
     
      'This is all one line of code that extracts everything
      'it changes from fname to fnam based on if the folder name includes .osch or not... open both, one results in an error, the other works
     '  Workbooks.OpenText Filename:= _
      '  "C:\Users\sg8\Documents\TVA data lab\_" & ucode & "_schedules\" & ucap & "_" & yr & "\" & fname & "\" & fname _
    Workbooks.OpenText Filename:= _
        "C:\Users\sg8\Documents\TVA data lab\_" & ucode & "_Daily_Data_Request\" & ucode & yr & "\" & fname _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, TrailingMinusNumbers:=True
      
        'make sure to save in excel format
        Application.DisplayAlerts = False
        Application.CutCopyMode = False
        Set wkb = Workbooks(fname)
        'Saving the Workbook
        wkb.SaveAs Filename:=Pathname & fname1, FileFormat:=xlOpenXMLWorkbook
        
        wkb.Close savechanges:=False
        Set wkb = Nothing
        

Next dy
Next mo
Next yr
Next I

Etrapper:
    Resume Next
End Sub

 

[ShawnPCooke]

Since you're not declaring most of your variables, everything you don't declare is of type Variant. Variants definitely use up more memory. Also, I see that you are actually changing your loop variables mo and dy from integer values into strings (following the Format statement). Presumably, they are changed back implicitly when you close your For loop, only to be changed back again immediately.

I can't promise that's it, but I'm not seeing anything else right offhand that should do it.

 

[Ssignore08]

Shawn,
I copied the wrong version I guess b/c I do have more variables dim'd than that. I think you could be right on it being the format w/ dy and mo that is the leak. Is using an if mo <10 then _____
and
if dy < 10 then _____

the best thing to do? Such that if its lower I can manually add in a 0 where need be and otherwise run the code normally?

The only other way around this that I see is if there's a better way to loop through every day in a year.

 

[Ssignore08]

changes made, but there is still a leak. it seems to be a bit slower of a memory leak than before though.

Option Explicit
Sub DailyData()
'
' Macro1 Macro
' open and extract files
'
'Dim mo As String
Dim mo, i, dy, yr As Integer
Dim ucode, ucap, ex, ext, fname1, fname, pathname As String
Dim wkb As Workbook
On Error GoTo Etrapper
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
For i = 24 To 31
      ucode = Cells(i, 1) 'others cth
      ucap = UCase(ucode)
      
      For yr = 2000 To 2015
      
      If Dir("C:\Users\sg8\Documents\data\_" & ucode & "Data\" & ucode & yr & "\", vbDirectory) <> "0" Then
      'Application.ScreenUpdating = True
         
     Application.StatusBar = "Current iteration: " & i & "__" & yr
     
      For mo = 1 To 12
      For dy = 1 To 31
     
      If mo < 10 Then
      ex = 0
      Else
      ex = ""
      End If
      
      If dy < 10 Then
      ext = 0
      Else
      ext = ""
      End If
      

      fname1 = yr & ex & mo & ext & dy & ucode
      fname = yr & ex & mo & ext & dy & "." & ucode
    
    pathname = "C:\Users\sg8\Documents\data\_" & ucode & "Data\" & ucode & yr & "\"
       
     
      'This is all one line of code that extracts everything
    Workbooks.OpenText Filename:= _
        "C:\Users\sg8\Documents\TVA data lab\_" & ucode & "Data\" & ucode & yr & "\" & fname _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, TrailingMinusNumbers:=True
       
        'make sure to save in excel format
        Application.DisplayAlerts = False
        Application.CutCopyMode = False
        Set wkb = Workbooks(fname)
        'Saving the Workbook
        wkb.SaveAs Filename:=pathname & fname1, FileFormat:=xlOpenXMLWorkbook
        
        wkb.Close savechanges:=False
        Set wkb = Nothing
        
Next dy
Next mo
End If
Next yr
Next i
Etrapper:
    Resume Next
End Sub

 

[Ssignore08]

This is possibly solved. The memory usage per the processes tab hits a max about 15,000K less than it did before. It's still not nearly as fast as a similar code I used for .osch files. Thanks Shawn

 

[SpillerBD]

Curious, which version of Excel are you using? Year and bits.

 

[Ssignore08]

excel 2010 and 32