Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failure on MacroRun but not EditorStepInto
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? Damian Carrillo 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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failure on MacroRun but not EditorStepInto
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failure on MacroRun but not EditorStepInto
Whoops!
What I meant to write was, is there an error? Or does the IDE window pop up higlighting the line of code in yellow? If the latter, try Ctrl+Shift+F9 to clear all breakpoints; failing that copy all the code to a new module and try to run it again (removing the old module) If there is an error post the number and message recieved... Hth Oli -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failure on MacroRun but not EditorStepInto
Oli,
No, no, and no. That's the part that has me totally stumped. All the breakpoints are cleared. The IDE window does not pop-up whether its already open or not. No lines are highlighted if its already open. No error message appears. The code just jumps to the Timekeepers.csv and stops(?) executing. Per your 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. Could it be because its a CSV file instead of an Excel file? I don't know... Damian Carrillo "Oliver Ferns via OfficeKB.com" wrote: Whoops! What I meant to write was, is there an error? Or does the IDE window pop up higlighting the line of code in yellow? If the latter, try Ctrl+Shift+F9 to clear all breakpoints; failing that copy all the code to a new module and try to run it again (removing the old module) If there is an error post the number and message recieved... Hth Oli -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup failure & ctrl-f failure? | Excel Discussion (Misc queries) | |||
failure of web query | Excel Discussion (Misc queries) | |||
IRTDUpdateEvent failure | Excel Programming | |||
add in failure | Excel Programming | |||
If .,AND ... Then failure | Excel Programming |