Iterative consolidation macro can't complete - memory overload?

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

The following code works fine on one two or maybe three passes through... and then Excel crashes.

It simply loops through a folder of excel files one by one. It opens one, opens the new template, and copies and pastes a bunch of data from the old to the new template. Then it saves the new template in a new folder, with the same filename as the original. Then it repeats for the next original file..

Each file, and the template, is about 1mb in size (xlsx), and there 50 or so files in the set, i.e. the code needs to complete 50 loops without crashing!

I don't know why it crashes, as the code works for one loop, so why not allloops?! It just hangs with the regular "RESTART EXCEL?" dialog box.

A memory problem? Can anyone advise of a better way to do this, or how to manage the memory issue properly if that is indeed the problem?

Thanks in advance for any life saving help!!

Stuart




Sub UpgradeFiles()
Dim strFile As String
Dim strPath As String
Dim strOriginalsPath As String
Dim strSaveToPath As String
Dim strPW As String
Dim strSheet As String
Dim strFrom As String
Dim strRange As String
Dim strFromFolder As String
Dim strToFolder As String
Dim wbkOriginal As Workbook
Dim wbkTemplate As Workbook
Dim strTemplate As String
Dim wksTarget As Worksheet
Dim wksCopied As Worksheet
Dim calcstate As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

calcstate = Application.Calculation
Application.Calculation = xlCalculationManual

strFromFolder = ThisWorkbook.Names("FROM").RefersToRange.Value
strToFolder = ThisWorkbook.Names("TO").RefersToRange.Value
strTemplate = ThisWorkbook.Names("NEW").RefersToRange.Value

strPath = ThisWorkbook.Path & "\"
strOriginalsPath = strPath & strFromFolder & "\"
strSaveToPath = strPath & strToFolder & "\"
strPW = ThisWorkbook.Names("PW").RefersToRange.Value

strFile = Dir(strOriginalsPath)

Do While Len(strFile) > 0

If strFile = ThisWorkbook.Name Then GoTo nxt
Debug.Print strFile
' Stop
On Error Resume Next
Set wbkOriginal = Application.Workbooks.Open(strOriginalsPath & strFile, Password:=strPW, UpdateLinks:=False)
Err.Clear
If wbkOriginal Is Nothing Then
Set wbkOriginal = Application.Workbooks.Open(strOriginalsPath & strFile, UpdateLinks:=False)
If wbkOriginal Is Nothing Then
If MsgBox("The file failed to open - cancel the upgrade?", vbYesNo) = vbYes Then
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
End If
GoTo nxt
End If
End If

Set wbkTemplate = Application.Workbooks.Open(strPath & strTemplate, UpdateLinks:=False)

''Upgrade
Upgrade wbkOriginal, wbkTemplate

Calculate
Err.Clear
On Error GoTo 0
wbkOriginal.Close SaveChanges:=False
wbkTemplate.Close SaveChanges:=True, Filename:=strSaveToPath & strFile

Err.Clear
Set wbkOriginal = Nothing
Set wbkTemplate = Nothing
On Error GoTo 0
nxt:
strFile = Dir
Loop

Application.Calculation = calcstate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub




Sub Upgrade(ByRef wbkOld As Workbook, ByRef wbkNew As Workbook)

'Does a bunch of copying and pasting between wbkOld and wbkNew, multiple versions along these lines

wbkOld.Activate
Range("G32:G34").Select ''Or some other range
Application.CutCopyMode = False
Selection.Copy
wbkNew.Activate
Range("G32").Select ''Or some other destination
ActiveSheet.Paste

Application.CutCopyMode = False

''And repeat multiple times for multiple ranges....

End Sub
 
The following code works fine on one two or maybe three passes through... and then Excel crashes.

It simply loops through a folder of excel files one by one. It opens one, opens the new template, and copies and pastes a bunch of data from the old to the new template. Then it saves the new template in a new folder, with the same filename as the original. Then it repeats for the next original file.

Each file, and the template, is about 1mb in size (xlsx), and there 50 or so files in the set, i.e. the code needs to complete 50 loops without crashing!

I don't know why it crashes, as the code works for one loop, so why not all loops?! It just hangs with the regular "RESTART EXCEL?" dialog box.
Click to expand...

I have seen something a bit like this because Excel file handling didn't
like the name of one file. Another time it failed on a magic boundary
through missing out the 256th file or something like that.
A memory problem? Can anyone advise of a better way to do this, or how to manage the memory issue properly if that is indeed the problem?

Thanks in advance for any life saving help!!
Click to expand...

All I can suggest is add debug.print waypoints to the code so that you
at least know which line it fails at. I have my suspicions that on error
handling is hiding something nasty that later causes trouble.

It would help to know which version of XL you are on. VBA on XL2007 was
pretty unreliable unless patched up to the eyeballs with updates.

It would be interesting to know if it still crashes if you step through
it line by line. Some of the race conditions present in XL2007 lead to a
situation where using VBA at full speed the execution thread might or
might not fail due to data structures not being fully initialised.
 
Thanks :) It's XL2010.

I seem to have got it working, touch wood, through a combination of:

1) Storing the list of filenames in an array, i.e. doing the DIR loop first not during the main code.

2) Putting in application.waits of 1 second where files are being opened, closed or saved, to give Excel and the network drive time to sync

It seems to be working so far.
 
Thanks :) It's XL2010.
I seem to have got it working, touch wood, through a combination of:

1) Storing the list of filenames in an array, i.e. doing the DIR loop
first not during the main code.

2) Putting in application.waits of 1 second where files are being
opened, closed or saved, to give Excel and the network drive time to
sync

It seems to be working so far.
Click to expand...

You can do all this using ADODB without having to open the files being
updated. Have a look here for how to...

http://www.appspro.com/conference/DatabaseProgramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Thanks :) It's XL2010.

I seem to have got it working, touch wood, through a combination of:

1) Storing the list of filenames in an array, i.e. doing the DIR loop first not during the main code.
Click to expand...

It is probably a race condition in the filesystem somewhere. Likely to
be related to the one that caused another way of iterating through a
directory of files to be discontinued in XL2007.
2) Putting in application.waits of 1 second where files are being opened, closed or saved, to give Excel and the network drive time to sync

It seems to be working so far.
Click to expand...

My guess with these is that they are an unfortunate effect of multiCPU
systems running code that isn't entirely proofed against reaching a
point where the consumer thread is reading uninitialised or partially
initialised structures. It is especially bad in the graphing/charts.
 
It is probably a race condition in the filesystem somewhere. Likely
to be related to the one that caused another way of iterating through
a directory of files to be discontinued in XL2007.

My guess with these is that they are an unfortunate effect of
multiCPU systems running code that isn't entirely proofed against
reaching a point where the consumer thread is reading uninitialised
or partially initialised structures. It is especially bad in the
graphing/charts.
Click to expand...

This is also accentuated if there's an explorer window open at the
target folder! I haven't experienced this since replacing explorer with
PowerDesk Pro!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top