Running VBA script causes excel to stop responding

Question

I have a VBA script that adds sheets to around 500 excel files. I had no problems running the VBA script and adding simple sheets, but when I try to add a sheet with VBA script in it and graphs and buttons, it works for a while and than freezes.

Here is the code. I know it does not have error handling - any suggestions how to tackle this problem or maybe what is causing excel to freeze?

Sub FindOpenFiles()

Const ForReading = 1
Set oFSO = New FileSystemObject

Dim txtStream As TextStream

Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file, wb As Workbook, sh As Worksheet
Dim directory As String

'The path for the equipement list. - add the desired path for all equipement or desired value stream only.
Set txtStream = oFSO.OpenTextFile("O:\SiteServices\Maintenance\Maintenance Support Folder\Maintenance Department Information\HTML for Knowledgebase\Excel for Knowledgebase\Equipement paths-all.txt", ForReading)

Do Until txtStream.AtEndOfStream
    strNextLine = txtStream.ReadLine
    If strNextLine <> "" Then

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folder = FSO.GetFolder(strNextLine)


    For Each file In folder.Files
        If Mid(file.Name, InStrRev(file.Name, ".") + 1) = "xls" Then
            Workbooks.Open strNextLine & Application.PathSeparator & file.Name

        Set wb = Workbooks("Equipment Further Documentation List.xls")
    For Each sh In Workbooks("Master File.xls").Worksheets
        sh.Copy After:=wb.Sheets(wb.Sheets.Count)
    Next sh

     ActiveWorkbook.Close SaveChanges:=True
     ActiveWorkbook.CheckCompatibility = False

        End If


    Next file
    End If

    Loop
txtStream.Close

End Sub

Add as a first line to your sub: Application.ScreenUpdating = false and add the other line right before End Sub : Application.ScreenUpdating = true — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 9, 2013 at 10:13
I do not have a chance to check where does it crush... It will add the sheet to the first 4 or 5 files and then crash without a chance to check where did it fail... — Saint
– Saint, Commented Aug 9, 2013 at 10:17
Is there anything in the sheets that you are copying that may be triggering automatically? If yes, please can you also post the VBA in the sheets that you are copying. — ChrisProsser
– ChrisProsser, Commented Aug 9, 2013 at 10:18
no nothing there there are two macros that are triggered by a button - one to send a graph by email and one to print it out. — Saint
– Saint, Commented Aug 9, 2013 at 10:19

Kazimierz Jawor · Accepted Answer · 2013-08-09 12:31:49Z

9

So, some tips for you:

1st. (according to comment)

Add as a first line to your sub: Application.ScreenUpdating = false and add the other line right before End Sub : Application.ScreenUpdating = true

2nd. Move this line (it's setting constance reference):

Set wb = Workbooks("Equipment Further Documentation List.xls")

before:

Do Until txtStream.AtEndOfStream

3rd is just a tip.

To see the progress of your sub add the following line:

Application.StatusBar = file.Name

after this line:

Workbooks.Open strNextLine & Application.PathSeparator & file.Name

Before the End Sub add additionally this code:

Application.StatusBar = false

As a result you can see in Excel app, in the status bar, file name which is currently in process.

Keep in mind that working with 500 files must be time-consuming.

edited Aug 9, 2013 at 12:31

answered Aug 9, 2013 at 10:19

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Kazimierz Jawor Over a year ago

3rd point is important- it'll give you idea if your sub is running.

Cor_Blimey Over a year ago

also, add in DoEvents before each loop. This will ensure excel continues to refresh and remain somewhat responsive.

Saint Over a year ago

@Cor_Blimey - will try this out later on... and give you a shout what is happening... Thanks

Kazimierz Jawor Over a year ago

@Saint, is this sub running? can you see file names changing in statusbar?

Saint Over a year ago

@KazJaw - Yep it is running... But same thing happens, after 4 files the macro freezes and I have to reset the PC to close the excel, not even in the task manager I can close it.

|

Saint · Accepted Answer · 2013-08-12 11:26:25Z

9

I have finally solved my problem...

The solution was to add a line of code:

Application.Wait (Now + TimeValue("0:00:01"))

after the line:

sh.Copy After:=wb.Sheets(wb.Sheets.Count)

which allowed time to copy the sheet to the new excel file.

So far it has been working like a charm.

I want to thank everyone that helped me with this issue.

Many Thanks.

answered Aug 12, 2013 at 11:26

Saint

4801 gold badge7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Running VBA script causes excel to stop responding

2 Answers 2

22 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

22 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related