Macro crashes/freezes when I run it

bradyj7

Board Regular
Joined
Mar 2, 2011
Messages
106
Hi All,

The macro below is suppose to loop through all the files in a folder called Test Results and preform a process on them by calling another macro called torque_kin. I have not included all the code for torque_kin as it is too long. However, at the end of the torque_kin code I want to save one of the worksheets (called kinematic) in the workbook to another folder called kinematic sheets. I've included the snippet of code that does this below under where I call the macro torque_kin. The 4 lines of code have an apostrophe in front of them just to differentiate them. However, the program crashes when I run it. Any ideas why this happens.

Thank you

John

Code:
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\MyDocuments\TestResults"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'I call another macro here to process the data
Call torque_kin
'Snippet of code to that save kinematic worksheet to a folder called kinematic sheets (part of macro torque_kin)


'Worksheets("kinematic").Activate
'ActiveSheet.Copy
'ActiveWorkbook.SaveAs Filename:="C:\kinematic sheets\" & Format(ActiveSheet.Range("B2"), "YYYYMMDDHHMMSS") & ".xls"
'ActiveWorkbook.Close


Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Code:
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Knowing which line the code crashes on might help to narrow it down. Do you know this?

Failing that, place a breakpoint in your code and step through the statements until the crash occurs, and identify the offending line in that way.

Generally, posting code between CODE tages - the # symbol in the editor toolbar - makes it easier to read and follow as it uses a fixed-width font and preserves indenting, and is more likely to attract responses.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,707
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

The macro below is suppose to loop through all the files in a folder called Test Results and preform a process on them by calling another macro called torque_kin. I have not included all the code for torque_kin as it is too long. However, at the end of the torque_kin code I want to save one of the worksheets (called kinematic) in the workbook to another folder called kinematic sheets. I've included the snippet of code that does this below under where I call the macro torque_kin. The 4 lines of code have an apostrophe in front of them just to differentiate them. However, the program crashes when I run it. Any ideas why this happens.

Thank you

John


Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\MyDocuments\TestResults"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'I call another macro here to process the data
Call torque_kin
'Snippet of code to that save kinematic worksheet to a folder called kinematic sheets (part of macro torque_kin)


'Worksheets("kinematic").Activate
'ActiveSheet.Copy
'ActiveWorkbook.SaveAs Filename:="C:\kinematic sheets\" & Format(ActiveSheet.Range("B2"), "YYYYMMDDHHMMSS") & ".xls"
'ActiveWorkbook.Close


Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Your using FileSearch which means you are running this on Excel 2003 or below, if you have upgraded to 2007 or 2010 the FileSearch has been removed, would this be the cause?
 

Forum statistics

Threads
1,141,730
Messages
5,708,147
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top