Macro gets lost
 

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("Generate d 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

Macro gets lost
 

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.

Ron Olcott wrote:

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("Generate d 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

Macro gets lost
 

This is a prominent give-away:

' Keyboard Shortcut: Ctrl+Shift+K

<vbg

<<snipped

Macro gets lost
 

Sure enough, taking the Shift away fixed it.

Thanks for your help.

-- Ron O.

"Dave Peterson" wrote:

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.

Ron Olcott wrote:

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("Generate d 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