Kill file

[McCrunch]

Hi,
I've got a sheet that I need to convert certain rows into CSV for a payroll package. I'm using excel 2003 on XP.

I have worked out a macro to save it as a csv file with a different name. The only way I managed to do this was by creating an xls file with the data I wanted and then saving it as a csv file with a different name. But now I need to delete the intermediate xls file (called pre CSV in this example). I have the following code which seemed to work first time but is now giving me the error "Runtime error 53 - File not found" and highlights the Kill filename command. What am I doing wrong?

Sub Direct()
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Pre CSV.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
     Windows("Commission calc.xls").Activate
    Range("I3:M500").Select
    Selection.Copy
    Windows("Pre CSV.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
 
ActiveWorkbook.SaveAs filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Broker comm dir.csv", FileFormat _
:=xlCSV, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
 
Dim filename As String
filename = "Pre CSV"
Kill filename
End Sub

Thanks

 

[delaneyjm]

You need to supply the full path and the file extension when using the Kill command.

 

[McCrunch]

You need to supply the full path and the file extension when using the Kill command.

How would I write that since I'm saving the file on to the desktop? Am on network computers so desktop easiest place to save as everyone would have one of them.

Thanks for your help.

 

[delaneyjm]

Try:

Kill "C:\Documents and Settings\All Users\Desktop\Pre CSV.xls"

 

[GTO]

Hi there,

1. What wb does the code reside in; does it reside in 'Commission calc.xls' by chance?
2. Regardless of the above, what is the name of the worksheet in 'Commission calc.xls' that the range Range("I3:M500") is being selected and copied from.
3. Does Range("I3:M500") cover all the data within the source sheet? I am asking that last bit, as it may be easier to copy the sheet.
4. Does Range("I3:M500") include formulas that you only want to retain the vals thereof?

Mark

 

[McCrunch]

Hi there,

1. What wb does the code reside in; does it reside in 'Commission calc.xls' by chance?
2. Regardless of the above, what is the name of the worksheet in 'Commission calc.xls' that the range Range("I3:M500") is being selected and copied from.
3. Does Range("I3:M500") cover all the data within the source sheet? I am asking that last bit, as it may be easier to copy the sheet.
4. Does Range("I3:M500") include formulas that you only want to retain the vals thereof?

Mark

1 - The code is in commission calc. I have it linked to a button on this sheet to make it easier for the end user.
2 - The name of the worksheet is comm calc, there is a second worksheet that I use for vlookups
3 - No. There is further information in the comm calc sheet that I don't want/need copied over.
4 - Yes there are formulas in those cells and yes I only want the values.

I also tried delaneyjm's file path and that didn't work either. Came up with the same runtime error (53).

 

[delaneyjm]

Runtime error 53 means that the file was not found where specified. Perhaps navigate to the path where the file was saved through Windows Explorer and then copy the full path into your string.

 

[xenou]

Maybe this:

Sub Direct()
[COLOR="#0000ff"]Dim KillMeLater As String[/COLOR]

Workbooks.Add
ActiveWorkbook.SaveAs filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Pre CSV.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
[COLOR="#0000ff"]KillMeLater = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name[/COLOR]
Windows("Commission calc.xls").Activate
Range("I3:M500").Select
Selection.Copy
Windows("Pre CSV.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

ActiveWorkbook.SaveAs filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Broker comm dir.csv", FileFormat _
:=xlCSV, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

[COLOR="Blue"]Kill KillMeLater[/COLOR]

End Sub

 

[McCrunch]

Excellent Xenou. That does exactly what I wanted. Thanks everyone for your help.

 

[GTO]

To first saveas a regular wb and kill later, here is my take:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> Direct()<br>Dim _<br>wbNew           <SPAN style="color:#00007F">As</SPAN> Workbook, _<br>wksSource       <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>rngSource       <SPAN style="color:#00007F">As</SPAN> Range, _<br>strDeskTopPath  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    strDeskTopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wbNew = Workbooks.Add(xlWBATWorksheet)<br>    <SPAN style="color:#007F00">'// Use ThisWorkbook instead of the Windows caption.  This way, we are referencing  //</SPAN><br>    <SPAN style="color:#007F00">'// the object directly.                                                            //</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wksSource = ThisWorkbook.Worksheets("comm calc")<br>    <SPAN style="color:#00007F">Set</SPAN> rngSource = wksSource.Range("I3:M500")<br>    <br>    <SPAN style="color:#00007F">With</SPAN> wbNew<br>        <SPAN style="color:#007F00">'// Turn off alerts if you want to overwrite//</SPAN><br>        .SaveAs FileName:=strDeskTopPath & "\Pre CSV.xls", FileFormat:=xlNormal <SPAN style="color:#007F00">'xlCSV</SPAN><br>        <br>        .Sheets(1).Range("A1").Resize(rngSource.Rows.Count, _<br>                                      rngSource.Columns.Count).Value = rngSource.Value<br>        <br>        .SaveAs FileName:=strDeskTopPath & "\Broker comm dir.csv", FileFormat:=xlCSV<br>        <br>        .Close <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    Kill strDeskTopPath & "\Pre CSV.xls"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I may be missing something, but I believe you can most likely skip that step and simply save a new worksheet as the .csv you want to end up with. Try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Direct2()<br>Dim _<br>wbNew           <SPAN style="color:#00007F">As</SPAN> Workbook, _<br>wksSource       <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>rngSource       <SPAN style="color:#00007F">As</SPAN> Range, _<br>strDeskTopPath  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    strDeskTopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wbNew = Workbooks.Add(xlWBATWorksheet)<br>    <SPAN style="color:#007F00">'// Use ThisWorkbook instead of the Windows caption.  This way, we are referencing  //</SPAN><br>    <SPAN style="color:#007F00">'// the object directly.                                                            //</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wksSource = ThisWorkbook.Worksheets("comm calc")<br>    <SPAN style="color:#00007F">Set</SPAN> rngSource = wksSource.Range("I3:M500")<br>    <br>    <SPAN style="color:#00007F">With</SPAN> wbNew<br>        <br>        .Sheets(1).Range("A1").Resize(rngSource.Rows.Count, _<br>                                      rngSource.Columns.Count).Value = rngSource.Value<br>            <br>        <SPAN style="color:#007F00">'// Turn off alerts if you want to overwrite//</SPAN><br>        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        .SaveAs FileName:=strDeskTopPath & "\Broker comm dir.csv", FileFormat:=xlCSV<br>        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        .Close <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

In either case, when taking just the values, referencing the source range and grabbing just the vals should be quicker than copy/paste.

Mark