Override the last saved sheet as paf file

[Ramadan]

I have found a code and modified it to perform my need which is to cycle the drop down list in a sheet and save the filtered data as PDFs in external folder with the currant date after the file name. but because the curranet date is changable of course, the folder still keeping the last time saved files and I have to delete them manually - what I need please is to edit the code to clear the destination folder from the old saved files before saving the new files or to Override each file with the new one

here is my code... any Suggestions please

Sub myFiles()

Dim wb As Workbook
Dim ws As Worksheet
Dim nwb As Workbook
Dim nws As Worksheet
Dim rng As Range
Dim Path As String
Dim myDate As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Print")
Set rng = ws.Range("B2")
Path = "D:\Desktop\Docs\"
myDate = Format(Now(), "DD-MM-YYYY")


For i = 1 To 5
rng = ws.Range("A" & i)

ws.Copy

Set nwb = ActiveWorkbook
Set nws = nwb.Worksheets("Print")

With nws
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
End With
Application.DisplayAlerts = False
nwb.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & ws.Range("c2") & " " & myDate
nwb.Close
Application.DisplayAlerts = True

Next i

End Sub

 

[Kaper]

Try adding such snippet:

'declaration
Dim OldFile As String
'real code deleting all files in a specified folder with names like C2 _ space _ anything after (so probably old date) _ .pdf 
OldFile = Dir(Path & ws.Range("c2") & " *.pdf")
While OldFile <> ""
  Kill Path & OldFile
  OldFile = Dir
Wend

so the whole code could look like:

Sub myFiles()

Dim wb As Workbook
Dim ws As Worksheet
Dim nwb As Workbook
Dim nws As Worksheet
Dim rng As Range
Dim Path As String
Dim myDate As String
Dim OldFile As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Print")
Set rng = ws.Range("B2")
Path = "D:\Desktop\Docs\"
myDate = Format(Now(), "DD-MM-YYYY")

OldFile = Dir(Path & ws.Range("c2") & " *.pdf")
While OldFile <> ""
  Kill Path & OldFile
  OldFile = Dir
Wend

For i = 1 To 5
rng = ws.Range("A" & i)

ws.Copy

Set nwb = ActiveWorkbook
Set nws = nwb.Worksheets("Print")

With nws
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
End With
Application.DisplayAlerts = False
nwb.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & ws.Range("c2") & " " & myDate
nwb.Close
Application.DisplayAlerts = True

Next i

End Sub

 

[Ramadan]

Try adding such snippet:

'declaration
Dim OldFile As String
'real code deleting all files in a specified folder with names like C2 _ space _ anything after (so probably old date) _ .pdf
OldFile = Dir(Path & ws.Range("c2") & " *.pdf")
While OldFile <> ""
  Kill Path & OldFile
  OldFile = Dir
Wend

so the whole code could look like:

Sub myFiles()

Dim wb As Workbook
Dim ws As Worksheet
Dim nwb As Workbook
Dim nws As Worksheet
Dim rng As Range
Dim Path As String
Dim myDate As String
Dim OldFile As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Print")
Set rng = ws.Range("B2")
Path = "D:\Desktop\Docs\"
myDate = Format(Now(), "DD-MM-YYYY")

OldFile = Dir(Path & ws.Range("c2") & " *.pdf")
While OldFile <> ""
  Kill Path & OldFile
  OldFile = Dir
Wend

For i = 1 To 5
rng = ws.Range("A" & i)

ws.Copy

Set nwb = ActiveWorkbook
Set nws = nwb.Worksheets("Print")

With nws
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
End With
Application.DisplayAlerts = False
nwb.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & ws.Range("c2") & " " & myDate
nwb.Close
Application.DisplayAlerts = True

Next i

End Sub

Thanks for your help - but actually I get error "Bad file name or number" for this line ( Kill Path & OldFile)
I also tried to add kill sub before the rest of my code like this

Sub killfiles()

Kill "D:\Desktop\Docs\*.*"



End Sub

but Still I get the same error message bad file name or number. for your info. there are 5 files in the folder not only one based on name in "C2" + today date

 

[Ramadan]

@ Kaper I have found out what is the probelm with runnig the code

My files name have Arabic name date that's why vba cannot recognize the file and give error "Bad file name or number"

any suggessions please to solve this issue

 

[Kaper]

Sorry to say so, but I have no experience with arabic names and fonts.

Especially if

Kill "D:\Desktop\Docs\*.*"

works (deletes the file) with sample.pdf but not with arabicname.pdf