Saving dialog box hanging after running macro

[aster123]

Hi I have some vba code that runs successfully. However when I save the Excel workbook, the "Saving" dialog box hangs and I have to either press Cancel on the dialog box or press the Escape key. The workbook does however save in the background regardless.

The overall aim of my code is to:
- import a text file into a 1-d array
- paste this array into a temporary Excel worksheet
- convert it to 2d via text to columns
- paste the resulting block of data into another Excel worksheet that consolidates results
- delete the temporary Excel worksheet

This process loops through up to 25 text files. The issue only arises once I start processing 13 text files and above. Once the issue has started in that instance of Excel, even if I revert to importing only 1 text file, the issue is there. I have to exit Excel and reopen the workbook to get rid of the issue.

I'm happy to post the bits of code that have started to make this issue happen if that helps.

Thanks

 

[Skyybot]

Can the Arrays be cleared after each loop? Being able to see the code would really help me understand where hang up is occurring.

 

[aster123]

Thanks for the response Skybot. This problem started to occur after I added the following bits of code:

Dim FSO As Object
Dim MyFIle As Object
Dim arr As Variant
Dim wsdatatemp As Worksheet

...
Then within the loop:
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Myfile = FSO.OpenTextFile({location of text file},1)
arr = Split(MyFile.ReadAll, vbCrLf)
Set MyFile = Nothing
Set FSO = Nothing

If count = 1 Then
Set ws = worksheets.Add
wsadd.Name = "Temp"
Set wsdatatemp = wb0.Sheets("Temp")
End If

wsdatatemp.Range("A1").Resize(UBound(arr)+1,1).Value = Application.Transpose(arr)
Set arr = Nothing

wsdatatemp.Range("A:A").TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Space:=False
...
wsdatatemp.UsedRange.Clear

...
Then outside the loop:
Application.DisplayAlerts = False
wsdatatemp.Delete
Set wsdatatemp = Nothing
Application.DisplayAlerts = True


It feels like it's something to do with Excel memory e.g. I just tried it once with 13 text files and fine, again with 14 text files and fine. Then tried with 20 text files and the issue has arisen. Then tried multiple times with 1 file and the issue is still there each time.

Thanks

 

[aster123]

*Skyybot

 

[Skyybot]

There does not appear to be anything in that bit of code that would be holding memory. Are there other Subroutines, UDF's, or Classes being used? Does it seem to work well after closing Excel and restarting?

 

[aster123]

There are a couple of variables passed from another subroutine, but this approach was being used ok before I added those bits of code above.

Yes if I close Excel and restart, it works fine initially, until I try to import a larger number of text files at which point the issue occurs and I have to restart Excel etc

 

[Skyybot]

Try this.
Then within the loop:

Application.ScreenUpdating = False  'Added line

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Myfile = FSO.OpenTextFile({location of text file},1)
arr = Split(MyFile.ReadAll, vbCrLf)
Set MyFile = Nothing
Set FSO = Nothing

If count = 1 Then
Set ws = worksheets.Add
wsadd.Name = "Temp"
Set wsdatatemp = wb0.Sheets("Temp")
End If

wsdatatemp.Range("A1").Resize(UBound(arr)+1,1).Value = Application.Transpose(arr)
Set arr = Nothing

wsdatatemp.Range("A:A").TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Space:=False
'...
wsdatatemp.UsedRange.Clear

'...

Then outside the loop:

Applcication.ScreedUpdating = True ' Added Line

 

[aster123]

Thanks, unfortunately I already have screen updating turned off at the beginning of the wider subroutine and then back on right at the end of the wider subroutine.

 

[Skyybot]

Ok. Well without being able to see the full process, that's the extent of my help.

 

[aster123]

Thanks anyway for the help. The code above increases efficiency substantially, so it’s a frustrating issue this one!