Keep xlsx and delete xlsm version from folder

[GraphWS]

First of all I’ve been lurking reading copy and pasting for past 3-4 months. Big Thanks Bill Jelen for the videos, ebooks and this forum. It’s gold when a question in google shows up with links to MrExcel

I have a Work Order xlsm file that opens, grabs next number, saves and closes then opens a new xlsm file called WorkOrder (with number).

Private Sub Workbook_Open()
Dim ws1 As Worksheet
Dim WS2 As Worksheet
Set ws1 = Worksheets("WorkOrder")
Set WS2 = Worksheets("Register")
'If ActiveWorkbook.Name = "WorkOrder.xls" Then
Worksheets("WorkOrder").Range("F4").Value = Range("F4").Value + 1
Range("F4").Value = Range("F4").Value
Workbooks("WorkOrder.xlsm").Save
'End If
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\WO_excel" & "\WorkOrder" & Range("F4").Value & ".xlsm", FileFormat:=52

End Sub

After file is filled out I have a shape with this macro assigned.


Sub P2RP()

Dim ws1 As Worksheet
Dim WS2 As Worksheet
Set ws1 = Worksheets("WorkOrder")
Set WS2 = Worksheets("Register")
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox NextRow
WS2.Cells(NextRow, 1).Resize(1, 13).Value = Array(ws1.Range("F3"), ws1.Range("F4"), ws1.Range("F6"), ws1.Range("F7"), ws1.Range("F8"), ws1.Range("F10"), _
ws1.Range("F11"), ws1.Range("F13"), ws1.Range("F14"), ws1.Range("F15"), ws1.Range("F17"), ws1.Range("B28"), ws1.Range("Rep"))

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\WO_PDF" & "\WorkOrder" & Range("F4").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
From:=1, To:=1, OpenAfterPublish:=True

OldName = ThisWorkbook.FullName
Newname = Left(OldName, InStrRev(OldName, ".")) & "xlsx"

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Newname, xlOpenXMLWorkbook
ThisWorkbook.Close SaveChanges:=True
Application.DisplayAlerts = True


ActiveWorkbook.SaveAs Filename & "xlsx", FileFormat:=xlOpenXMLWorkbook
SetAttr MyPath & Files, vbNormal
ActiveWorkbook.Close SaveChanges:=True
Kill OldName
End Sub

Everything works.
It post to register on hidden Worksheet
A PDF is published, saved and left open on screen.
The new xlsx version of file is saved and closed
What I need is to make the Kill /Delete the WorkOrder(with number).xlsm file work so when they remember they forgot something they can’t open xlsm version causing number to change.

Thanks

 

[Logit]

.
See if this accomplishes your goal :

Option Explicit


Sub P2RP()


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim NextRow 'As Range
Dim OldName As String
Dim NewName As String
Set ws1 = Worksheets("WorkOrder")
Set ws2 = Worksheets("Register")
NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1


ws2.Cells(NextRow, 1).Resize(1, 13).Value = Array(ws1.Range("F3"), ws1.Range("F4"), ws1.Range("F6"), ws1.Range("F7"), ws1.Range("F8"), ws1.Range("F10"), _
ws1.Range("F11"), ws1.Range("F13"), ws1.Range("F14"), ws1.Range("F15"), ws1.Range("F17"), ws1.Range("B28"), ws1.Range("F18"))


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "WOPDF WorkOrder " & Range("F4").Value, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, ignorePrintAreas:=False, _
from:=1, to:=1, OpenAfterPublish:=False


OldName = ThisWorkbook.FullName
NewName = Left(OldName, InStrRev(OldName, ".")) & "xlsx"


Application.DisplayAlerts = False
ThisWorkbook.SaveAs NewName, xlOpenXMLWorkbook
Application.DisplayAlerts = True


Kill "C:\Users\My\Desktop\Book1.xlsm"      '<---- Change path to workbook here.
       
Application.Quit


End Sub

The above assumes you are using the Desktop as the location for the workbook.

 

[GraphWS]

Thanks so much Logit!!!!

adding

Option Explicit
'at top &
Dim OldName As String

Dim NewName As String

'then using 


     Kill OldName 'no path because about 6 other people use this file in a dropbox folder & a couple on different network.
     Application.Quit
End Sub

Anyway I applied same changes to 2 other files Quote and Contract and works like a charm. No more xlsm files for them to reopen and whack the numbers out of order. Thanks Again!!!!

 

[Logit]

.
You are welcome.

Glad to assist.