Keep xlsx and delete xlsm version from folder

GraphWS

New Member
Joined
Aug 22, 2019
Messages
5
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

Well-known Member
Joined
Aug 31, 2016
Messages
2,920
.
See if this accomplishes your goal :

Code:
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

New Member
Joined
Aug 22, 2019
Messages
5
Thanks so much Logit!!!!

adding
Code:
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!!!!
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top