DELETE ALL Files in a folder. How?

[Mister H]

Hi All:

I am playing around with a code that Trevor G (THANKS) posted for someone. I am wondering if something can be added to this code to DELETE ALL Excel Files in the folder after the code runs OR dlete the file after it has been printed. Here is the code:

Sub Print_ADI_Files()
Dim MyFile As String
MyPath = "C:\ADIs To Be PRINTED\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
Sheets(Journal).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
ActiveWorkbook.Close True
 
[B][COLOR=#ff0000]'Now I would like to DELETE the printed file and move [/COLOR][/B]
[B][COLOR=#ff0000]'onto the next one[/COLOR][/B]
 
 
 
 
End If
MyFile = Dir
Loop
 
[COLOR=red][B]'Now I would like to DELETE ALL Excel files in the folder:[/B][/COLOR]
[B][COLOR=red]'C:\ADIs To Be PRINTED\[/COLOR][/B]
 
 
 
End Sub

Can this be done?

Any assistance would be GREATLY appreciated.

THANKS,
Mark

 

[VoG]

Hi Mark. Try before End Sub

Kill "C:\ADIs To Be PRINTED\*.xls"

 

[shg]

Be a little careful with that; the deleted files don't go to the recycle bin.

 

[Mister H]

THANKS to BOTH of you. For some reason my code is not working. I must have messed it up as I have not yet even tried VoG's code

I keep getting the message "Subscript out of range"

I had it working earlier but accidentally deleted the code so I redid it using the code I posted. If I get rid of the line

Sheets(Journal).Select

then it works but I want to make sure that the sheet titled Journal is the one being printed.

Regarding deleting the files, that is not a problem becuase I still have the orignal excel file attached to an email in Outlook.

How can I make sure that the sheet Jouranl is the one printed. In most cases the file will open directly to that sheet but not always.

ONE MORE THINGS (of course). Can I easily get the file name to print in the header or footer?

THANKS,
Mark

 

[VoG]

I think (just about to pop out)

Sheets("Journal").Select

 

[Mister H]

THANKS VoG.

Here is the final code for anyone that is looking. I figured out the Header Footer part.

Have a GREAT day ALL,
Mark

Sub Open_Print_and_DELETE_My_Files()
Dim MyFile As String
MyPath = "C:\ADIs To Be PRINTED\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
Sheets("Journal").Select
'ADD Header and Footer"  Filename in Arial, 16pt, Bold
    With ActiveSheet.PageSetup
        .CenterHeader = "&""Arial,Bold""&16&F"
        .CenterFooter = "&""Arial,Bold""&16&F"
    End With
 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
 
[B]'DELETE the files Now CAUTION:  These files are not recoverable as they 'Do Not Go To the Recycle Bin[/B]
Kill "C:\ADIs To Be PRINTED\*.xls"
End Sub