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


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!! :clap: 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.
24507

[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

[TraceyH]

:banghead:

[Paul_Hossler]

Can you be a little more specific?

[TraceyH]

LOL - apologies! I put a comment that I couldn't work out how to delete so had to edit it and leave 1 character in it. The head banging is because I had just spotted thatthe Approved save was carrying over the original requestors username and date but I managed to fix it all by myself, which I am fairly chuffed about.

ANYWAY - WOW! Thank you so much for the email addendum - can't believe how quickly you managed to work that out! Have been studying the code and there's a lot that is WAY over my head, but I can sort of understand the logic!?!

Not wanting to be greedy, or take the proverbial, but would there be any way to add a similar thing to the Approved macro, to email another address? I've updated the list so that the Pending Macro will send to the Budget Holders Email, but the Approved macro needs to go to the Processing Team's email, so I added an additional column to list.

Have a look and see what you think. Could I just copy what you have done to Pending to make it work in Approved?

[Paul_Hossler]

1. Glad you can handle some changes yourself.:thumb Sometimes I wish others would make the effort :clap::clap::clap:

2. The Outlook eMail is something I have in a 'toolkit' of macros I use a lot

3. It's basically a copy, but there are some changes that needed to be made. I think I understood, so check this out

[TraceyH]

Am now testing it at work (this is where the head banging emoji will REALLY be needed!)


When I run Pending, I get a "Compile error: can't find project or library" and it's highlighting the 'Function PendingPath() As String" in yellow and then Environ in blue?

When I run Approved, I get a "Compile error: can't find project or library" and it's highlighting the 'Sub SaveDocumentApproved()" in yellow and then Environ in blue?


What on earth am I doing wrong?!

[Paul_Hossler]

Probably nothing - compiles and works here

1. In the Editor, check Tools, References just in case

2. Restart the computer

3. Try this version - I ran it through Code Cleaner which just gives back clean code text -- no changes to macro


But my money is on #2


24521

[TraceyH]

Oh. My. At home, that's not an issue (I'm running Excel 2016) but at work I'm running Excel 2013 and so only have MS Office 16.0 Object Library - this applies to every user in the organisation as well, so now what?!?!

[TraceyH]

Ignore me - I just had to untick the MISSING notification in References and all is good.

Will every user have to do that before the form will work for them?

PS - is there any way to make the reference in the email a hyperlink, or am I expecting to much / being greedy?!?!?

PPS - have a grovelled adequately so far for all your help? I cannot express to you enough my gratitude!!!

[TraceyH]

Will every user have to do that before the form will work for them?

Ignore me again - worked this out for myself too!

Would still love to get it set up with a hyperlink, mind...

[Paul_Hossler]

Try this

Not 100% sure about fomatting since if you're using an Exchange server, it might add some text

[TraceyH]

Interestingly, the Approved one works but the Requesting on comes back with "We can't find 'HL-MGT-SVR02\ChildrenCentreGeneral\Budgets Family Centre Service\2019-20\test\PendingApproval\blah blah blah" - I'm guessing that the
HL-MGT-SVR02\ChildrenCentreGeneral part is something to do with how we're set up? Shame really as if it could just point to m:\\ instead of that, all the rest of it is OK.

I assume the Approved one works because it comes from a Pending version?

[Paul_Hossler]

Works for me

Make sure that there's no leading of trailing spaces in the folder names

[TraceyH]

Hi Paul

It's become apparent that I need to include a time in the file name, as there are cases where someone may complete or approve more than one request on the same day. I've had a look and thought I could perhaps just change the format part of the paths from Format(Date, "dd-mmm-yy") to Format(Now, "dd-mmm-yy_hh:mm") but I just get back a Run-time error '1004' Method "'SaveAs' of object '_Workbook' failed

Clearly I am doing something wrong and was hoping you could help me (again!!)

[Paul_Hossler]

You can't use a colon in a path

Try something like


Format(Now, "dd-mmm-yy-hh-nn")


although personally I like


Format(Now, "yyyymmdd-hhnn")

since it sorts better


NB: for 'minutes' the placeholder is n 'nancy' to differentiate it from months 'm' Mary

[TraceyH]

:clap:You're a star - thanks!