Macro gets lost

[Ron Olcott]

In Excel 2003, I am trying to take the contents of one sheet and save them as
a CSV.

I have written the code below. When I walk through it in debug, it works
fine. When I just run the macro, it get to the point of opening my copy and
then goes off into space. Its like the newly opened workbook of seizing
control from my macro.

Sub Save_To_CSV()
'
' ExportDateRangeForDateLoad Macro
' Macro recorded 2/6/2008 by rolcott
'
' Keyboard Shortcut: Ctrl+Shift+K
'
Dim channelNumber As Integer
Dim XLFile, CSVFile, TermString As String
Dim fs

TermString = Mid(Worksheets("Instructions").Range("C20").Value,
Len(Worksheets("Instructions").Range("C20").Value), 1)
If TermString <> "\" Then
Worksheets("Instructions").Range("C20").Value =
Worksheets("Instructions").Range("C20").Value & "\"
End If
XLFile = Worksheets("Instructions").Range("C20").Value &
"14_day_load_data.xls"
CSVFile = Worksheets("Instructions").Range("C20").Value &
"day_load_data.csv"
ActiveWorkbook.SaveCopyAs (XLFile)
Workbooks.Open Filename:=XLFile
Workbooks("14_day_load_data.xls").Sheets("Generated Dates").SaveAs
Filename:=CSVFile, FileFormat:=xlCSV
Workbooks("day_load_data.csv").Close
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile XLFile
Sheets("Instructions").Select

' channelNumber = Application.DDEInitiate(app:="Cmd", topic:="/h")
' Application.DDEExecute channelNumber, "sqlldr st/st@docdw9
control=14_day_load.ctl errors=1000"
' Application.DDETerminate channelNumber
End Sub

 

[Dave Peterson]

How do you start your macro?

Do you use a shortcut key? Does that shortcut key include the shiftkey?

Does the problem go away if you drop the shift from the shortcut key
combination?

If you have a workbook that has a workbook_open event or an auto_open procedure,
then if you open that file with the shift key held down, you stop those
procedures from running.

The shift-key in the shortcut combination confuses excel/vba to stop after you
open a file using one of those shortcut keys.

 

[Dave Peterson]

This is a prominent give-away:

' Keyboard Shortcut: Ctrl+Shift+K

<vbg>

<<snipped>>

 

[Ron Olcott]

Sure enough, taking the Shift away fixed it.

Thanks for your help.

-- Ron O.