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

 

[Alex Blakenburg]

That snippet of code is not giving much insight.
1) Are you using the FSO.OpenText method because you have a tried other methods and this was fastest ?
If so what else have you tried ?

2)
In the below, you have 3 different sheet names.
Are you adding a Temp sheet on each loop ?
Later on you clear the Temp sheet so why the need to add a new one each time ?

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

3) If you are using the Temp sheet then after clearing add the line:

ActiveSheet.UsedRange

To reset the Used Range

4) How are you getting the data from the Temp Sheet to the final destination ?
Are you using Copy Paste ? In which case we might need to clear the windows clipboard.

5) Clearing the variant arr
change it from Set arr to

arr = Null

I don't think 3 & 5 will make much difference but may as well try it.

 

[aster123]

Thanks for the suggestions Alex:

1) I used FSO.OpenText just due to my lack of vba knowledge, without knowing if there is a better alternative. My ideal would be to create a 2-d array as it would avoid the need for the text to columns line of code, but I wasn't able to achieve it. Do you know if there's potentially a better method?

2) I only add the temp sheet on the first loop via the use of the count variable. (Before the main loop starts, I have count = 0, then at the start of the main loop I have count = count + 1)

3) I added ActiveSheet.UseRange but it didn't fix the issue as you'd suspected.

4) Yes I use Selection.PasteSpecial Paste:=xlPasteValues to paste from the Temp sheet to the final destination. I've just tried adding the following, but unfortunately it didn't solve the issue:
Application.CutCopyMode = False
wsdatatemp.UsedRange.Clear
ActiveSheet.UsedRange

5) I changed to arr = Null but it didn't fix the issue as you'd suspected

 

[Alex Blakenburg]

What happens is you take the copy paste out of the equation and use an assignment ?

Since you haven't shown the actual code and have 3 different names for your temp sheet, I can only give an example:
Note: CurrentRegion assumes that the data in the Temp file does not have any entirely empty rows or column within the data range.

Sub AssignInsteadOfCopy()

    Dim wsdatatemp  As Worksheet, wsDest As Worksheet
    Dim nextrowDest As Long
    
    Set wsdatatemp = Worksheets("Temp")
    
    Set wsDest = Worksheets("Destination")
    nextrowDest = wsDest.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    
    With wsdatatemp.Range("A1").CurrentRegion
        wsDest.Range("A" & nextrowDest).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
End Sub

 

[aster123]

Thanks Alex, I gave that a go, but still the same issue unfortunately.

Would it help any if I add the whole code? It's around c.300 lines, not sure what the policy is on the forum for adding so much code.

 

[Alex Blakenburg]

That's a lot of code for one module.
How much code is in the loop that reads in all the files ?
eg do you have
' Start of import loop
Import files
' End of import loop

Alternatively can you share a desensitised copy of your workbook through a sharing platform ie dropbox, google drive, onedrive etc.
(make sure the permissions allow anyone with the link to access it)

 

[aster123]

Hi Alex

I've gone through the complete loop line-by-line and have identified the following two blocks of code which have been causing the issue.

With Block 1, I had to replace with your assignment piece of code.
With Block 2, I had to place it outside of the main loop i.e. to do this process once all of the text files have been imported and pasted to the main data worksheet 'wsdata'.

Block 1
'copy to data worksheet
With wsdata
last_row = .Cells(.Rows.count, "B").End(xlUp).Row 'find last row of data
End With

wsdatatemp.Select
ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count).Select
Selection.Copy

wsdata.Select
Range("data_headers").Offset(last_row - headers_row + 1).Select
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.DisplayAlerts = True

Block 2
wsdata.Select
With ActiveSheet
headers_row = .Range("headers").Row
last_row = .Cells(.Rows.count, "B").End(xlUp).Row 'find last row of data
.Range("formula_cells").Copy
.Range("formula_headers").Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
With Selection
.Borders.LineStyle = Continuous
.HorizontalAlignment = Left
End With
Selection.AutoFill Destination:=Range("formula_headers").Resize(last_row - headers_row).Offset(1)
End With


Do you by any chance know why those blocks would have been causing issues? Is it maybe because of all the 'Selection' lines?

Thanks

 

[Alex Blakenburg]

Selection lines will slow it down but I wouldn't have thought that it would cause the issue you are having.
The copy-paste could cause the issue. If you hit Windows+V do you see any paste history after you run your original macro ?
If you do try turning off the functionality to keep history by:
" In Settings, navigate to System > Clipboard. Under "Clipboard History," flip the switch to the "Off" position."
Ref: How to Enable and Use Clipboard History on Windows 11

Copy the code below into a new module.
Then where you have "Application.CutCopyMode = False" add the line
Call small_20202024_ClearOfficeClipBoard_
Then run the original macro again and see if that makes any difference.

Source: How to Clear Office Clipboard with VBA
Alan Elston

Option Explicit
    #If VBA7 Then
     Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    #Else
     Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    #End If
Sub small_20202024_ClearOfficeClipBoard_()  ' https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=24879&viewfull=1#post24879
Dim avAcc, bClipboard As Boolean, j As Long
Dim MyPain As String
    If CLng(Val(Application.Version)) <= 11 Then  '                   Case 11: "Excel 2003" Windows    "Excel 2004" mac
     Let MyPain = "Task Pane"
    Else
     Let MyPain = "Office Clipboard"
    End If
Set avAcc = Application.CommandBars(MyPain)   '
Let bClipboard = avAcc.Visible      '   bClipboard will be false if the viewer pain is not open
    If Not bClipboard Then
     avAcc.Visible = True           '   This opens the Viewer pain. The coding won't work if it is not open
     DoEvents: DoEvents
    Else
    End If
'   coding change for Office versions at  --  Office 2016  ==
    If CLng(Val(Application.Version)) < 16 Then
' --For Office versions 2003 2007 2010 2013 ----------------------------------------
        For j = 1 To 4         '      J =    1  2  3  4
         AccessibleChildren avAcc, Choose(j, 0, 3, 0, 3), 1, avAcc, 1
        Next
     avAcc.accDoDefaultAction 2&  '           This seems to do the clearing   It will NOT error if viewer pain is already  Cleared                  1& for paste
' ----------------------------------------------------------------------------------
    Else
' ==For Office versions 2016 and higher ==============================================
        For j = 1 To 7      '           J =  1  2  3  4  5  6  7
         AccessibleChildren avAcc, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, avAcc, 1
        Next
     avAcc.accDoDefaultAction 0& '            This seems to do the clearing   It WILL error if viewer pain is already  Cleared
    End If ' =======================================================================
 Let Application.CommandBars(MyPain).Visible = bClipboard      '   Puts the viewer pain back as it was, open or closed
End Sub