Why does code execution stop? HELP!
 

This issue has me totally stumped. All the breakpoints are cleared. The IDE
window does NOT pop-up. No lines are highlighted if the IDE window is
already open. No error message appears or msgbox pops-up. The code just
jumps to the Timekeepers.csv and stops(?) executing.

Per Oli's suggestion I deleted the original module and pasted the code into a
new one, but that did not help. I've tried adding Wait times into the code,
I've tried three different ways of selecting the desired cell and inserting
the formula, but it always stops at the point that the new document opens.
The line (flagged below) is as follows:

Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE

Could it be because its a CSV file instead of an Excel file? I don't
know... Please note it runs perfectly if I step through it one line at a
time.


Sub TimekeeperUpdate()

' TimekeeperUpdate Macro
' Macro recorded 2/15/2005 by Damian Carrillo
' Keyboard Shortcut: Ctrl+Shift+T

Dim FileScan As Variant
Dim FilePresence As String
Set FileScan = Application.FileSearch

With FileScan
.LookIn = "F:\Importer\CMSDISB1\Disbursements\zAutomatio n Files"
.Filename = "Timekeepers.csv"
If .Execute 0 Then
'Prevent file from being overwritten if already exists
Let FilePresence = True
Else
Let FilePresence = False
'Duplicate Source Timekeeper file
Dim SourceFile, DestinationFile
SourceFile = "W:\ERS\SERVER\Manager\Validation\Timekeeprs.c sv"
'Define source file name.
DestinationFile = "F:\Importer\CMSDISB1\Disbursements\zAutomatio n
Files\Timekeepers.csv" 'Define target file name.
FileCopy SourceFile, DestinationFile 'Copy source to target.
End If
End With

'Clear Old Data from Timekeeper.xls
Windows("Timekeepers.xls").Activate
Range("D2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents

'Prepare new copy of Timekeeper.csv file for data transfer to
Timekeeper.xls
Workbooks.Open Filename:= _
"F:\Importer\CMSDISB1\Disbursements\zAutomatio n Files\Timekeepers.csv"
Windows("Timekeepers.csv").Activate
Application.Wait (Now + TimeValue("0:00:01"))

On Error Resume Next
Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]" 'Merge Data into a single
column
Selection.AutoFill Destination:=Range("C1",
ActiveCell.SpecialCells(xlLastCell)), Type:=xlFillDefault 'Expand formula
Range("C1", ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Windows("Timekeepers.xls").Activate 'Paste new data
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10,
1), Array(11, 1)), _
TrailingMinusNumbers:=True

Range("A1", ActiveCell.SpecialCells(xlLastCell)).Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers 'Resort new dataset

On Error GoTo 0 'Stop subroutine if unable to save file
ActiveWorkbook.Save 'Save new dataset
Windows("Timekeepers.csv").Activate
ActiveWindow.Close SaveChanges:=True 'Close source document
Kill "F:\Importer\CMSDISB1\Disbursements\zAutomatio n
Files\Timekeepers.csv"
End Sub



The following is code I am using to re-populate a spreadsheet with updated
data. I tested it extensively in the Editor and it runs fine, but when I try
to run the macro, it stops at the same exact spot every time and I don't know
why. Can someone please help me? The line (flagged below) is as follows:

Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE

I must be missing something obvious about the statement that halts the
macro, but why doesn't it stop it from working when I'm manually stepping
through the program in the Visual Basic Editor?

Why does code execution stop? HELP!
 

You can give this a try. It apears as if you have activated the window but
not a sheet, Change your code to:

activesheet.Range("C1").Select

HTH

"Damian" wrote:

This issue has me totally stumped. All the breakpoints are cleared. The IDE
window does NOT pop-up. No lines are highlighted if the IDE window is
already open. No error message appears or msgbox pops-up. The code just
jumps to the Timekeepers.csv and stops(?) executing.

Per Oli's suggestion I deleted the original module and pasted the code into a
new one, but that did not help. I've tried adding Wait times into the code,
I've tried three different ways of selecting the desired cell and inserting
the formula, but it always stops at the point that the new document opens.
The line (flagged below) is as follows:

Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE

Could it be because its a CSV file instead of an Excel file? I don't
know... Please note it runs perfectly if I step through it one line at a
time.


Sub TimekeeperUpdate()

' TimekeeperUpdate Macro
' Macro recorded 2/15/2005 by Damian Carrillo
' Keyboard Shortcut: Ctrl+Shift+T

Dim FileScan As Variant
Dim FilePresence As String
Set FileScan = Application.FileSearch

With FileScan
.LookIn = "F:\Importer\CMSDISB1\Disbursements\zAutomatio n Files"
.Filename = "Timekeepers.csv"
If .Execute 0 Then
'Prevent file from being overwritten if already exists
Let FilePresence = True
Else
Let FilePresence = False
'Duplicate Source Timekeeper file
Dim SourceFile, DestinationFile
SourceFile = "W:\ERS\SERVER\Manager\Validation\Timekeeprs.c sv"
'Define source file name.
DestinationFile = "F:\Importer\CMSDISB1\Disbursements\zAutomatio n
Files\Timekeepers.csv" 'Define target file name.
FileCopy SourceFile, DestinationFile 'Copy source to target.
End If
End With

'Clear Old Data from Timekeeper.xls
Windows("Timekeepers.xls").Activate
Range("D2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents

'Prepare new copy of Timekeeper.csv file for data transfer to
Timekeeper.xls
Workbooks.Open Filename:= _
"F:\Importer\CMSDISB1\Disbursements\zAutomatio n Files\Timekeepers.csv"
Windows("Timekeepers.csv").Activate
Application.Wait (Now + TimeValue("0:00:01"))

On Error Resume Next
Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]" 'Merge Data into a single
column
Selection.AutoFill Destination:=Range("C1",
ActiveCell.SpecialCells(xlLastCell)), Type:=xlFillDefault 'Expand formula
Range("C1", ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Windows("Timekeepers.xls").Activate 'Paste new data
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10,
1), Array(11, 1)), _
TrailingMinusNumbers:=True

Range("A1", ActiveCell.SpecialCells(xlLastCell)).Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers 'Resort new dataset

On Error GoTo 0 'Stop subroutine if unable to save file
ActiveWorkbook.Save 'Save new dataset
Windows("Timekeepers.csv").Activate
ActiveWindow.Close SaveChanges:=True 'Close source document
Kill "F:\Importer\CMSDISB1\Disbursements\zAutomatio n
Files\Timekeepers.csv"
End Sub



The following is code I am using to re-populate a spreadsheet with updated
data. I tested it extensively in the Editor and it runs fine, but when I try
to run the macro, it stops at the same exact spot every time and I don't know
why. Can someone please help me? The line (flagged below) is as follows:

Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE

I must be missing something obvious about the statement that halts the
macro, but why doesn't it stop it from working when I'm manually stepping
through the program in the Visual Basic Editor?

Why does code execution stop? HELP!
 

Jim, I gave that code a try but it had no effect. It stops at the same exact
spot. How can I make sure it is focused in the right place? Maybe I should
not be worrying about changing the focus of the windows and just write the
changes directly? Any other ideas?

"Jim Thomlinson" wrote:

You can give this a try. It apears as if you have activated the window but
not a sheet, Change your code to:

activesheet.Range("C1").Select

HTH

"Damian" wrote:

This issue has me totally stumped. All the breakpoints are cleared. The IDE
window does NOT pop-up. No lines are highlighted if the IDE window is
already open. No error message appears or msgbox pops-up. The code just
jumps to the Timekeepers.csv and stops(?) executing.

Per Oli's suggestion I deleted the original module and pasted the code into a
new one, but that did not help. I've tried adding Wait times into the code,
I've tried three different ways of selecting the desired cell and inserting
the formula, but it always stops at the point that the new document opens.
The line (flagged below) is as follows:

Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE

Could it be because its a CSV file instead of an Excel file? I don't
know... Please note it runs perfectly if I step through it one line at a
time.


Sub TimekeeperUpdate()

' TimekeeperUpdate Macro
' Macro recorded 2/15/2005 by Damian Carrillo
' Keyboard Shortcut: Ctrl+Shift+T

Dim FileScan As Variant
Dim FilePresence As String
Set FileScan = Application.FileSearch

With FileScan
.LookIn = "F:\Importer\CMSDISB1\Disbursements\zAutomatio n Files"
.Filename = "Timekeepers.csv"
If .Execute 0 Then
'Prevent file from being overwritten if already exists
Let FilePresence = True
Else
Let FilePresence = False
'Duplicate Source Timekeeper file
Dim SourceFile, DestinationFile
SourceFile = "W:\ERS\SERVER\Manager\Validation\Timekeeprs.c sv"
'Define source file name.
DestinationFile = "F:\Importer\CMSDISB1\Disbursements\zAutomatio n
Files\Timekeepers.csv" 'Define target file name.
FileCopy SourceFile, DestinationFile 'Copy source to target.
End If
End With

'Clear Old Data from Timekeeper.xls
Windows("Timekeepers.xls").Activate
Range("D2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents

'Prepare new copy of Timekeeper.csv file for data transfer to
Timekeeper.xls
Workbooks.Open Filename:= _
"F:\Importer\CMSDISB1\Disbursements\zAutomatio n Files\Timekeepers.csv"
Windows("Timekeepers.csv").Activate
Application.Wait (Now + TimeValue("0:00:01"))

On Error Resume Next
Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]" 'Merge Data into a single
column
Selection.AutoFill Destination:=Range("C1",
ActiveCell.SpecialCells(xlLastCell)), Type:=xlFillDefault 'Expand formula
Range("C1", ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Windows("Timekeepers.xls").Activate 'Paste new data
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10,
1), Array(11, 1)), _
TrailingMinusNumbers:=True

Range("A1", ActiveCell.SpecialCells(xlLastCell)).Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers 'Resort new dataset

On Error GoTo 0 'Stop subroutine if unable to save file
ActiveWorkbook.Save 'Save new dataset
Windows("Timekeepers.csv").Activate
ActiveWindow.Close SaveChanges:=True 'Close source document
Kill "F:\Importer\CMSDISB1\Disbursements\zAutomatio n
Files\Timekeepers.csv"
End Sub



The following is code I am using to re-populate a spreadsheet with updated
data. I tested it extensively in the Editor and it runs fine, but when I try
to run the macro, it stops at the same exact spot every time and I don't know
why. Can someone please help me? The line (flagged below) is as follows:

Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE

I must be missing something obvious about the statement that halts the
macro, but why doesn't it stop it from working when I'm manually stepping
through the program in the Visual Basic Editor?