Need to delete a workbook

[TraceyH]

Hi all - I'm fairly new to VBA and am struggling to work out how to get it to delete a workbook. I've discovered the KILL function, but this seems to be linked to knowing the path and filename, and I can't quite get my head round how to incorporate that in my VBA?!

Basically, I have a macro (SaveDocumentPending) that saves an Expenditure Form to an Approval Pending folder, using username_date_PendingApproval.xlsm. The Budget Holder then goes in and uses a different macro (SaveDocumentApproved) to save that file to an Approved Purchase folder using username_date_ApprovedExpenditure.xlsm

What I would like to do is have the SaveDocumentApproved macro delete the version of the file in the Approval Pending folder.

My code currently looks like this:

Sub SaveDocumentApproved()
    Dim username As String
    Dim nowFormated As String
    Dim path As String
    Dim filename As String
    Dim extention As String


    username = Environ("Username") & "_" 'gets the username
    nowFormated = CStr(Format(Date, "dd-mmm-yy")) 'or whatever format you like
    path = "M:\Budgets Family Centre Service\2019-20\Approved Expenditure" 'wherever you want to save the file
    filename = "_ApprovedExpenditure" 'final part of file name
    extention = ".xlsm" 'for example (with macros, else you have to change the FileFormat too)


    ActiveWorkbook.SaveAs filename:=path & username & nowFormated & filename & extention, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

I've attached a copy of the Expenditure Form as well - any help would be MASSIVELY appreciated!!

Many thanks!

[Bob Phillips]

Is this what you want

Sub SaveDocumentApproved()
    Dim username As String
	Dim currentFile As String
    Dim nowFormated As String
    Dim path As String
    Dim filename As String
    Dim extention As String

	currentFile = ActiveWorkbook.Fullname
    username = Environ("Username") & "_" 'gets the username
    nowFormated = CStr(Format(Date, "dd-mmm-yy")) 'or whatever format you like
    path = "M:\Budgets Family Centre Service\2019-20\Approved Expenditure" 'wherever you want to save the file
    filename = "_ApprovedExpenditure" 'final part of file name
    extention = ".xlsm" 'for example (with macros, else you have to change the FileFormat too)


    ActiveWorkbook.SaveAs filename:=path & username & nowFormated & filename & extention, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
		
	Kill currentFile
	
    ActiveWorkbook.Close
End Sub

[TraceyH]

WOW! Yes, that works - thank you for getting back to me so promptly!

Just one concern. If the Budget Holder enters the details and decides to skip the Request Approval stage, and just goes straight to pressing Approve Expenditure, will then then delete the original Expenditure Form and, if so, is there any way around that?

[Bob Phillips]

Where is the code that determines that action?

[TraceyH]

There are 2 buttons on the Expenditure Form - one is for "Request Approval for Expenditure" and is linked to Module1 "SaveDocumentPending" and looks like this:

Sub SaveDocumentPending()
    Dim username As String
    Dim nowFormated As String
    Dim path As String
    Dim filename As String
    Dim extention As String
username = Environ("Username") & "_" 'gets the username
    nowFormated = CStr(Format(Date, "dd-mmm-yy")) 'or whatever format you like
    path = "M:\Budgets Family Centre Service\2019-20\Pending Approval" 'wherever you want to save the file
    filename = "_PendingApproval" 'final part of file name
    extention = ".xlsm" 'for example (with macros, else you have to change the FileFormat too)


    ActiveWorkbook.SaveAs filename:=path & username & nowFormated & filename & extention, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

Then there's a button below it that saves "Authorise Expenditure" and is linked to Module2 and the VBA you have modified for me.

In the majority of cases, someone would click on the "Request Approval for Expenditure" first and then someone else (ie, the Budget Holder) would come along and authorise it. In reality, there are times when a Budget Holder will complete the Expenditure Form and feel that they can, therefore, skip the "Request Approval" stage. If they did this, then they would delete the original Expenditure Form.

Does that make sense?

Is there a more efficient way of me setting the form up? I attached the whole spreadsheet to my original thread, if you can see that?

Thank you so so much!!

[Paul_Hossler]

This may not be perfect - I left in the test code to check it out and comment out the 'real code' for now

Since Format() returns a string, there's no need for the CStr()

Option Explicit


Const sDocumentPath As String = "M:\Budgets Family Centre Service\2019-20\" 'wherever you want to save the file
Const sPendingFolder As String = "Pending Approval\"
Const sApprovedFolder As String = "Approved Purchase\"

Sub SaveDocumentPending()
    Dim sFilename As String
    sFilename = Environ("Username") & "_" & Format(Date, "dd-mmm-yy") & "_PendingExpenditure.xlsm"

    '------------------------------------------------------------------------------------------------------------------
    'for testing
    'this is named something like
    '   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
    MsgBox sDocumentPath & sPendingFolder & sFilename
    '------------------------------------------------------------------------------------------------------------------

'    ActiveWorkbook.SaveAs filename:=sDocumentPath & sPendingFolder & sFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'    ActiveWorkbook.Close
End Sub



'this is originally named something like
'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm

Sub SaveDocumentApproved()
    Dim sOldFilename As String, sNewFileName As String
    '------------------------------------------------------------------------------------------------------------------
    'for testing - this is the workbook name on opening
    sOldFilename = sDocumentPath & sPendingFolder & "TheUser\06-01-2019_PendingExpenditure.xlsm"
    MsgBox "Old Name = " & vbCrLf & vbCrLf & sOldFilename
    sNewFileName = Replace(sOldFilename, "_PendingExpenditure", "_ApprovedPurchase")
    MsgBox "New Name = " & vbCrLf & vbCrLf & sNewFileName
    '------------------------------------------------------------------------------------------------------------------
    
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Kill sOldFilename
    Application.DisplayAlerts = True
    On Error GoTo 0

'    sFilename = Replace(ThisWorkbook.Name, "_PendingExpenditure", "_ApprovedPurchase")
'    ActiveWorkbook.SaveAs filename:=sDocumentPath & sApprovedFolder & sFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'    ActiveWorkbook.Close
End Sub

[Bob Phillips]

Why do you not want to delete the original expenditure form if the approval stage is skipped, but you want to delete it if both steps are taken. That is just not clear to me.

[TraceyH]

Hi - sorry for the delay! The orginal expenditure form is the blank form that everyone uses to enter their details, they then (should) click on the "Request Approval" button, which then saves the file as theirusername_todaysdate_pendingapproval, then the Budget Holder comes along and opens the Pending Approval form, clicks on "Approve Expenditure" and it saves the file as theirusername_todaysdate_approvedexpenditure and deletes the pending approval version. This all works perfectly.

The issue is if a Budget Holder completes the original expenditure form and does not click on "Request Approval" but goes straight for "Approve Expenditure" - the macro would then delete the original expenditure form, leaving us without a blank form for anyone else to complete.

Does that make sense?

[TraceyH]

Hi Paul - it almost works - Pending returns the right name but Approved returns TheUser as a folder?

[Paul_Hossler]

BTW, I put both macros in one module so that they can share the Const

This is a little cleaner

the Test sub shows the paths generated

Since they use Thisworkbook.FullName, there's a bit of test code to fake it since I didn't have your folder structure

Option Explicit


Const sDocumentPath As String = "M:\Budgets Family Centre Service\2019-20\" 'wherever you want to save the file
Const sPendingFolder As String = "Pending Approval\"
Const sApprovedFolder As String = "Approved Purchase\"

Sub test()
    MsgBox PendingPath & vbCrLf & vbCrLf & ApprovedPath
End Sub


'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
Function PendingPath() As String
    PendingPath = sDocumentPath & sPendingFolder & Environ("Username") & "_" & Format(Date, "dd-mmm-yy") & "_PendingExpenditure.xlsm"
End Function


'Assumes Thisworkbook.FullName =
'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
Function ApprovedPath() As String
    Dim sPath As String
    Dim i As Long
    
    sPath = ThisWorkbook.FullName
    
    sPath = PendingPath     '   for testing --------------------------------------------------------<<<<<
    
    sPath = Replace(sPath, sPendingFolder, sApprovedFolder)
    sPath = Replace(sPath, "PendingExpenditure", "ApprovedPurchase")
    
    i = InStrRev(sPath, ".")
    sPath = Left(sPath, i) & "xlsx"
    
    ApprovedPath = sPath
End Function

Sub SaveDocumentPending()
    ActiveWorkbook.SaveAs Filename:=PendingPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

'assumes workbook name something like
'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
Sub SaveDocumentApproved()
    Dim sNewPath As String, sOldPath As String
    
    sOldPath = ThisWorkbook.FullName
    sNewPath = ApprovedPath
    
    ActiveWorkbook.SaveAs Filename:=sNewPath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    On Error Resume Next
    Application.DisplayAlerts = False
    Kill sOldPath
    Application.DisplayAlerts = True
    On Error GoTo 0
    ActiveWorkbook.Close
    
End Sub

[TraceyH]

Thanks for all your help Paul.

I'm struggling because it saves the filename to include a fixed date of the 06-01-2019? That means it will struggle to find the original file (sOldFileName) as the date might be different to when the PendingExpenditure version was created, plus it means it always saves the NewFileName with that same fixed date, and I can't see a way round it?

It's also saving the NewFileName to the sPendingFolder and it needs to be in the sApprovedFolder, but when I change the ActiveWorkbook.SaveAs I get a 400 error message?

Also, what would happen if the UserName differs between the _PendingExpenditure version and the _ApprovedPurchase version?

This is what I've done:

Sub SaveDocumentApproved()
    Dim sFileName As String, sOldFilename As String, sNewFileName As String
    
    sOldFilename = sDocumentPath & sPendingFolder & Environ("UserName") & "_06-01-2019_PendingExpenditure.xlsm" 'this is the workbook name on opening
    sNewFileName = Replace(sOldFilename, "_PendingExpenditure", "_ApprovedPurchase")
        
    On Error Resume Next
    Application.DisplayAlerts = False
    Kill sOldFilename
    Application.DisplayAlerts = True
    On Error GoTo 0
    sFileName = Replace(ThisWorkbook.Name, "_PendingExpenditure", "_ApprovedPurchase")
    ActiveWorkbook.SaveAs Filename:=sDocumentPath & sApprovedFolder & sNewFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

[TraceyH]

Oh, and I had to add "sFileName As String" because it came back "Compile error: Variable not defined"

Sorry, we are WAY out of my comfort zone now! Bet you wish you'd never got involved?!

[Paul_Hossler]

Sorry, we are WAY out of my comfort zone now! Bet you wish you'd never got involved?!

Not at all, and you're doing very well. Hang out here and you'll soon be an expert

Please look at the new and improved code in Post #10

I tried to remove any confusing Test code. The sub Test() displays the Pending and the Approved path and filenames

The path and name to save the Pending is calculated here:

    PendingPath = sDocumentPath & sPendingFolder & Environ("Username") & "_" & Format(Date, "dd-mmm-yy") & "_PendingExpenditure.xlsm"

The path and name to save the Approved is calculated here:

Normally the Approved path and name is based of the path and file name of the Pending, i.e. Thisworkbook.Fullname, which would have the dd-mmm-yy in it

I take the Pending folder and replace it with the Approved folder, the Pending suffix and replace it with the Approve suffix, and finally change the extension to plain XLSX

I did have to force one line, so remove or comment out the marked testing line

    sPath = ThisWorkbook.FullName
    
    sPath = PendingPath     '   for testing --------------------------------------------------------<<<<< to make the Test() sub
    
    sPath = Replace(sPath, sPendingFolder, sApprovedFolder)
    sPath = Replace(sPath, "PendingExpenditure", "ApprovedPurchase")
    
    i = InStrRev(sPath, ".")
    sPath = Left(sPath, i) & "xlsx"

Like I said, I really can't give it a solid test since I don't have your folder structure, and I didn't want to make a special version in case I forgot to clean up some testing code

[TraceyH]

Hi Paul - sorry, had a busy day today doing my actual job so have had to wait until I get home to look at this again.

I commented out the sPath=Pending Path bit and it ALMOST works!

I don't understand what the advantage is of making the Approved version save as plain XLSX? It means that the user gets the error message that "the following features cannot be saved in macro-free workbooks, click yes or no" - if they click NO they get a 400 error message, but if they click YES it saves it where you would hope and all is dandy. That error message (for a non-excel user especially) is kind of frightening and a bit off putting, though?

However, the issue really comes if your a Budget Holder and go straight for the Approved option in the original Expenditure form - if you click YES to the error message, it saves the Approved version but OVERWRITES the original Expenditure version as plain XLSX, thereby removing the macros etc for future users? I mean, please don't get me wrong, I'm impressed it no longer deletes it but really need the original Expenditure form to remain as an XLSM file, ready for the next person who needs to use it.

So, I have tried to fix it myself (are you impressed - I am!?!), by changing

sPath = Left(sPath, i) & "xlsx"

to

sPath = Left(sPath, i) & "xlsm"

and changing the FileFormat to MacroEnabled, and this means that, if the user follows the Pending then Approve path, then it works - no error messages, no warnings about file format. Brilliant! Unfortunately, though, if they go straight for Approve, all it does it save the original Expenditure form and nothing else - no Approved file?

Fell like I'm going round and round in circles!

This is what it looks like now (you'll see I've set this up to go work on my PC at home, so it's now pointing at my C drive, just so I can keep trying whilst I'm not at work!

Option Explicit




'Const sDocumentPath As String = "M:\Budgets Family Centre Service\2019-20\" 'wherever you want to save the file
Const sDocumentPath As String = "c:\users\trace\onedrive\documents\work\" 'wherever you want to save the file
Const sPendingFolder As String = "Pending Approval\"
Const sApprovedFolder As String = "Approved Purchase\"


'Sub test()
 '   MsgBox PendingPath & vbCrLf & vbCrLf & ApprovedPath
'End Sub




'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
Function PendingPath() As String
    PendingPath = sDocumentPath & sPendingFolder & Environ("Username") & "_" & Format(Date, "dd-mmm-yy") & "_PendingExpenditure.xlsm"
End Function




'Assumes Thisworkbook.FullName =
'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
Function ApprovedPath() As String
    Dim sPath As String
    Dim i As Long
    
    sPath = ThisWorkbook.FullName
    
    'sPath = PendingPath     '   for testing --------------------------------------------------------<<<<<
    
    sPath = Replace(sPath, sPendingFolder, sApprovedFolder)
    sPath = Replace(sPath, "PendingExpenditure", "ApprovedPurchase")
    
    i = InStrRev(sPath, ".")
    sPath = Left(sPath, i) & "xlsm"
    
    ApprovedPath = sPath
End Function


Sub SaveDocumentPending()
    ActiveWorkbook.SaveAs Filename:=PendingPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
End Sub


'assumes workbook name something like
'   M:\Budgets Family Centre Service\2019-20\Pending Approval\TheUser\06-01-2019_PendingExpenditure.xlsm
Sub SaveDocumentApproved()
    Dim sNewPath As String, sOldPath As String
    
    sOldPath = ThisWorkbook.FullName
    sNewPath = ApprovedPath
    
    ActiveWorkbook.SaveAs Filename:=sNewPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    On Error Resume Next
    Application.DisplayAlerts = False
    Kill sOldPath
    Application.DisplayAlerts = True
    On Error GoTo 0
    ActiveWorkbook.Close
    
End Sub

[Paul_Hossler]

Good fixes

I'm not clear about the workflow. Is this correct?


Case 1 -- this works

There is a blank WB (e.g. Purchase_Request.xlsm) that has the macros

A Requester (TheUser) opens it, fills it out, and saves it using macro SaveDocumentPending so it has "...\Pending Approval\TheUser_06-01-2019_PendingExpenditure.xlsm"

An Approver opens that and saves it using macro SaveDocumentApproved so it has "...\Approved Purchase_TheUser\06-01-2019_ApprovedPurchase.xlsm"

Case 2 -- this is missing, but this is what you want

There is a blank WB (e.g. Purchase_Request.xlsm) that has the macros

A Requester/Approver (TheUser2) opens it, fills it out, and saves it using macro SaveDocumentApproved so it has "...\ Approved Purchase\TheUser2_06-01-2019_ApprovedPurchase.xlsm"

[TraceyH]

Hope this helps clarify - I've highlighted my changes in blue:


Case 1 -- this works

There is a blank WB (i.e. ExpenditureForm.xlsm) that has the macros

A Requester (TheUser) opens it, fills it out, and saves it using macro SaveDocumentPending so it has "...\Pending Approval\TheUser_TodaysDate_PendingExpenditure.xlsm" and closes the file.

An Approver opens that and saves it using macro SaveDocumentApproved so it has "...\Approved Purchase\TheUser2_TodaysDate_ApprovedPurchase.xlsm" - SaveDocumentApproved also deletes the PendingExpenditure version and closes the file.

Case 2 -- this is missing, but this is what I want:

There is a blank WB (i.e. ExpenditureForm.xlsm) that has the macros

A Requester/Approver (TheUser2) opens it, fills it out, and saves it using macro SaveDocumentApproved so it has "...\ Approved Purchase\TheUser2_TodaysDate_ApprovedPurchase.xlsm" - SaveDocumentApproved closes the file, leaving the blank WB (i.e. Expenditure Form.xlsm) intact.

[Paul_Hossler]

Ahh, it's always the workflow

Try this


1. BTW, I'd make the ExpenditureForm xlsm ReadOnly before I'd trust any of my users with it


Edit --

2. Don't forget to change the documents folder in the macro

3. Something to think about ...

Since the Documents folder tree is probably something like

"M:\Budgets Family Centre Service\2019-20"

……...Pending Approval

…... Approved Purchases


you could put the ExpenditureForm.xlsm in the 2019-20 and the macros could just save to the lower level using the Path of the Expenditure workbook, whatever it might be

[TraceyH]

OMG - IT WORKS!! You are officially my hero!!!

LOVE the form - haven't had a go at those yet!!

I have saved a copy of the Expenditure Form in a hidden spot, only accessible by me, for the (inevitable) times someone accidentally deletes the one on the shared drive! I've also protected it to within an inch of its life!!

The only thing I haven't been able to test yet is how it copes with different users requesting and approving and on different days, so I may come back to you yet, but thank you, thank you, thank you!

Now all I need to do is work out how to get it to send the Budget Holder an email to let them know there's a form waiting for their approval!

Thank you for all your help and I'm sure we'll talk again soon!

[TraceyH]

Me again (so soon!!)! So I've found this bit of code - can you help me work out how I can add it to my Expenditure Form?

Sub SendEmail()
'
' SendEmail Macro
'
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


    Dim olMail As Outlook.MailItem
    Set olMail = olApp.CreateItem(olMailItem)
    
    olMail.To = "joe.bloggs@gmail.com"
    olMail.Subject = "Expenditure Request waiting to be authorised"
    olMail.Body = "There is an Exprenditure Request waiting for you to authorise in m drive"
    olMail.Send
    


End Sub

Ideally, the workflow would look like this:

Case 1

There is a blank WB (i.e. ExpenditureForm.xlsm) that has the macros

A Requester (TheUser) opens it, fills it out, and saves it using macro SaveDocumentPending so it has "...\Pending Approval\TheUser_TodaysDate_PendingExpenditure.xlsm" and closes the file. At this point I would love it to send an email to the person who needs to authorise the spend to let them know that there's a Pending Expenditure waiting for their approval. In an ideal world, the email would include a link to the document they need to authorise. I can't imagine how to let the system know who to email, as there are so many variables, but even if it could open an email with the right subject and text (with link) in, so that all the user has to do is enter the email address, then this would be a massive advantage.

An Approver opens that and saves it using macro SaveDocumentApproved so it has "...\Approved Purchase\TheUser2_TodaysDate_ApprovedPurchase.xlsm" - SaveDocumentApproved also deletes the PendingExpenditure version and closes the file. At this point I would love it to send an email to the Admin or Finance Assistant to let them know that there's an Approved Expenditure waiting for them to process. In an ideal world, the email would include a link to the document they need to authorise.

Case 2

There is a blank WB (i.e. ExpenditureForm.xlsm) that has the macros

A Requester/Approver (TheUser2) opens it, fills it out, and saves it using macro SaveDocumentApproved so it has "...\ Approved Purchase\TheUser2_TodaysDate_ApprovedPurchase.xlsm" - SaveDocumentApproved closes the file, leaving the blank WB (i.e. Expenditure Form.xlsm) intact. At this point I would love it to send an email to the Admin or Finance Assistant to let them know that there's an Approved Expenditure waiting for them to process. In an ideal world, the email would include a link to the document they need to authorise. This version is slightly easier as there are limited options available, and they are directly linked to the Budget Holder who approves it, so I could include some lookup somewhere in the form that says who to email, depending on which name has "Authorised" it.

Hoping you can help again! Thanks. Tracey.
Expenditure Form Fingers Crossed with email.xlsm

[Paul_Hossler]

Try something like this

I added Outlook code to the Pending macro

Also had to change your form to add an Approver (row 33) so that I could loop up their email address

 
Sub SaveDocumentPending()
    Dim eMail As String
    Dim sPath As String
    Dim sApprover As String
    
    sPath = PendingPath
    
    ActiveWorkbook.SaveAs Filename:=sPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
    'get email address for Approver
    sApprover = Worksheets("Expenditure Form").Range("C33").Value
    If Len(sApprover) > 0 Then
        eMail = vbNullString
        On Error Resume Next
        eMail = Application.WorksheetFunction.VLookup(sApprover, Worksheets("Lists").Range("E:G").Value, 3, False)
        On Error GoTo 0
    End If
    
    If Len(eMail) > 0 Then
        Call SendWithOutlook("Expenditure Request waiting to be authorised", "", eMail, _
            "There is an Exprenditure Request waiting for you to authorise in M: drive" & vbCrLf & vbCrLf & sPath)
    End If
    
    ActiveWorkbook.Close
End Sub