Macro crashes/freezes when I run it

[bradyj7]

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

 

[Ruddles]

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]

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?