VBA Kill file

[Viperuk]

Afternoon all.

I have been struggling with this for a while now and I wonder if you can help.


I have a userform which enters data to the database, it then creates a folder, renames it then PDF's all of the worksheets into the new folder.

The folder and PDF's change file name based on the last entry of the database.

What I want to do is delete the Data (Last name entry which is J1) exp (Last date entry which is K1 text).PDF which isnt relevant.



My code below.



create folder and pdf individual certs



Private Sub PDFButton_Click()


Worksheets("Data").Select






MkDir ThisWorkbook.Path & "\Induction Certs" & " " & Range("J1").Value & " " & "exp" & " " & Range("K1").Text





For Each ws In ActiveWorkbook.Worksheets




strPDFName = ws.Name


strDir = ThisWorkbook.Path & "\Induction Certs" & " " & Range("J1").Value & " " & "exp" & " " & Range("K1").Text
ChDir ThisWorkbook.Path & "\Induction Certs" & " " & Range("J1").Value & " " & "exp" & " " & Range("K1").Text






fileSaveName = ws.Name & " " & Range("J1").Value & " " & "exp" & " " & Range("K1").Text


ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False




Next ws




'Delete Data files


'Dim aFile As String
'KillFile = ThisWorkbook.Path & "\Induction Certs & " " & Range("J1").Value & " " & "exp" & " " & Range("K1").Text \ Data.& " " & Range("J1").Value & " " & "exp" & " " & Range("K1").Text.PDF"
'Check that file exists
'If Len(Dir$(KillFile)) > 0 Then
'First remove readonly attribute, if set
'SetAttr KillFile, vbNormal
'Then delete the file
'Kill KillFile


' End If







End Sub




Thank you

 

[Worf]

Re: VBA Kill file help please

Hello

Private Sub CommandButton1_Click()
Dim ws As Worksheet, fsname$, kfile$, pth$
Worksheets("Data").Activate
pth = ThisWorkbook.Path & "\Induction Certs" & " " & [J1] & " " & "exp" & " " & [K1].Text
MkDir pth
ChDir pth
For Each ws In ActiveWorkbook.Worksheets
    fsname = ws.Name & " " & [J1] & " " & "exp" & " " & [K1].Text
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fsname, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
Next
kfile = pth & " \Data\" & [J1] & " " & "exp" & " " & [K1].Text & ".PDF"
MsgBox kfile
If Len(Dir$(kfile)) > 0 Then
    SetAttr kfile, vbNormal
    Kill kfile
End If
End Sub

 

[Viperuk]

Re: VBA Kill file help please

Hi Worf.

Thank you for your help.

I have only just got around to trying the code.

Whilst the sheets PDF as they did, the Data PDF is still not deleting?



Code below (not sure how to create the link)


Private Sub Submitbutton_Click()


Dim emptyRow As Long

'Make Sheet1 active
Worksheets("Data").Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = Range("A4").FormulaR1C1
Cells(emptyRow, 2).Value = FirstNameBox.Value
Cells(emptyRow, 3).Value = DivisionCombo.Value
Cells(emptyRow, 4).Value = TrainingVenueCombo.Value
Cells(emptyRow, 5).Value = CDate(Datetextbox.Value)
Cells(emptyRow, 6).Value = Range("F4").FormulaR1C1
Cells(emptyRow, 7).Value = Range("G4").FormulaR1C1
Cells(emptyRow, 8).Value = NameofTrainerCombo.Value





'Generate and PDF


Dim ws As Worksheet, fsname$, kfile$, pth$


Worksheets("Data").Activate


pth = ThisWorkbook.Path & "\Induction Certs" & " " & [J1] & " " & "exp" & " " & [K1].Text


MkDir pth
ChDir pth



For Each ws In ActiveWorkbook.Worksheets


fsname = ws.Name & " " & [J1] & " " & "exp" & " " & [K1].Text

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fsname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False




Next




'Delete Data files


kfile = pth & " \Data" & [J1] & " " & "exp" & " " & [K1].Text & ".PDF"
MsgBox kfile




'Check that file exists
If Len(Dir$(kfile)) > 0 Then
'First remove readonly attribute, if set
SetAttr kfile, vbNormal
'Then delete the file
Kill kfile


End If


UserForm1.FirstNameBox.Value = ""
UserForm1.DivisionCombo.Value = ""
UserForm1.TrainingVenueCombo.Value = ""
UserForm1.Datetextbox.Value = ""
UserForm1.NameofTrainerCombo = ""




UserForm1.FirstNameBox.SetFocus



ThisWorkbook.Save

End Sub



Any thoughts?

 

[Worf]

Re: VBA Kill file help please

I am assuming you want to delete only one PDF file.
The code has a message box informing the file path; check if it is correct.