C
Carroll
All,
This is recent behavior that I haven't been able to figure out. When
running a macro (the macro below was just recorded in a brand new
spreadsheet), the macro stops most of the time, with the error
message, "Code execution has been interrupted". I have Microsoft
Office Excel 2003. Basically, this macro just clears the data that's
already in the sheet, and reimports the data anew to the same location
in the sheet. I have done this many, many times in the past, and it's
never done this before. I should note that I now have a new PC which
is faster than the old one.
Sometimes when I run this, it runs right through. Other times, it
stops on the "End With" statement. If I hit the F8 key, it might
continue on but hang up with the same error message on "End Sub".
Another time it might stop on ".TextFileStartRow = 2". I should note
that the macro ultimately does what it's supposed to do, but just with
these halts in the execution.
Any ideas out there? One suggestion was to download a code cleaner,
which I have done but it did nothing for me.
One thought out in this communitiy was that on occasion, Excel moves
too fast for a macro or visa versa, and that in the time it takes to
process one statement and goes onto the next, it's possible that the
workbook is still in the process of doing something that causes the
subsequent statement to fail. The suggestion that was offered to slow
down the macro was to add this code somewhere in the VBA:
"Application.Wait (Now + TimeValue("00:00:01))". I have added this
code before the "WITH" statement, but it still hangs up on the "END
WITH".
Does anyone have a clue how I might fix this problem? As I said, this
is new, and we are not talking about a file or code corruption
problem, as I just recorded this macro in a brand new spreadsheet.
Sub Macro5()
' Macro5 Macro
' Macro recorded 12/4/2007 by 678601
Columns("A:L").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:\BRIO\FTP678601\REMAND08.TXT",
Destination:=Range("A1"))
.Name = "REMAND08_24"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(4, 8, 8, 10, 10, 10, 10,
12, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
thanks,
Carroll Rinehart
This is recent behavior that I haven't been able to figure out. When
running a macro (the macro below was just recorded in a brand new
spreadsheet), the macro stops most of the time, with the error
message, "Code execution has been interrupted". I have Microsoft
Office Excel 2003. Basically, this macro just clears the data that's
already in the sheet, and reimports the data anew to the same location
in the sheet. I have done this many, many times in the past, and it's
never done this before. I should note that I now have a new PC which
is faster than the old one.
Sometimes when I run this, it runs right through. Other times, it
stops on the "End With" statement. If I hit the F8 key, it might
continue on but hang up with the same error message on "End Sub".
Another time it might stop on ".TextFileStartRow = 2". I should note
that the macro ultimately does what it's supposed to do, but just with
these halts in the execution.
Any ideas out there? One suggestion was to download a code cleaner,
which I have done but it did nothing for me.
One thought out in this communitiy was that on occasion, Excel moves
too fast for a macro or visa versa, and that in the time it takes to
process one statement and goes onto the next, it's possible that the
workbook is still in the process of doing something that causes the
subsequent statement to fail. The suggestion that was offered to slow
down the macro was to add this code somewhere in the VBA:
"Application.Wait (Now + TimeValue("00:00:01))". I have added this
code before the "WITH" statement, but it still hangs up on the "END
WITH".
Does anyone have a clue how I might fix this problem? As I said, this
is new, and we are not talking about a file or code corruption
problem, as I just recorded this macro in a brand new spreadsheet.
Sub Macro5()
' Macro5 Macro
' Macro recorded 12/4/2007 by 678601
Columns("A:L").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:\BRIO\FTP678601\REMAND08.TXT",
Destination:=Range("A1"))
.Name = "REMAND08_24"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(4, 8, 8, 10, 10, 10, 10,
12, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
thanks,
Carroll Rinehart