VBA - Save WIP file with file name from cells and change file name using additional cells once completed

[quidar]

Hi All,

I am completely new at using VBA and am struggling to write the code for two specific tasks which I have listed below. Any assistance would be much appreciated.

TASK 1 - I want to use a button called 'Save as WIP' to save a file (using cell values for the filename) to a specific folder within the 'Documents' folder called KPI's. If this folder does not exist before the user attempts to save then it must be created and the file then saved in this folder. The code I have listed below carries this function out but it always saves the file in the 'Documents' folder not the KPI's folder. The file path will change depending on which user is trying to save the file.

My Current code is listed below.

Sub SaveAsWIP()
'
' Macro3 Macro
'

'
Dim strFolderPath As String
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
strFolderPath = Environ("USERPROFILE") & "\Documents\KPI's"
CheckDir (strFolderPath)
Path = "\Documents\KPI's"
FileName1 = Range("P7")
FileName2 = Range("G4")
ActiveWorkbook.SaveAs Filename:=FileName1 & "- " & FileName2, FileFormat:=52, CreateBackup:=False
End Sub
Function CheckDir(Path As String)

    If Dir(Path, vbDirectory) = "" Then
        MkDir (Path)
    'End If
    Else
    End If

End Function

TASK 2 - I want to use a button called "Save As Final" which saves the file (with a different file name to the 'Save As WIP' button) and if the 'Save as WIP' file is located in the 'KPI' folder (and could be open as the current document) then it is deleted once the 'Save As Final' task has been executed. It must also create the 'KPI' folder within the 'Documents' folder if it does not already exist. So far I have been able to create the 'KPI' folder and update the filename but the file still saves in 'Documents' instead of the 'KPI' folder and I'm not sure how to delete the old file once I have saved using the 'Save as Final' button.

The current code is listed below.

Sub SaveAsFinal()
'
' Macro2 Macro
'

'
Dim strFolderPath As String
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Dim FileName4 As String
strFolderPath = Environ("USERPROFILE") & "\Documents\KPI's"
CheckDir (strFolderPath)
Path = "\Documents\KPI's"
FileName1 = Range("P7")
FileName2 = Range("G4")
FileName3 = Range("N7")
FileName4 = Range("O7")
ActiveWorkbook.SaveAs Filename:=FileName1 & "- " & FileName2 & " - " & FileName3 & " " & FileName4, FileFormat:=52, CreateBackup:=False
End Sub

Function CheckDir(Path As String)

    If Dir(Path, vbDirectory) = "" Then
        MkDir (Path)
    'End If
    Else
    End If

End Function

 

[Leith Ross]

Hello quidar,

You need to specify the full path in the SaveAs method.

    ActiveWorkbook.SaveAs Filename:=strFolderPath & "\" & FileName1 & "- " & FileName2 & " - " & FileName3 & " " & FileName4, FileFormat:=52, CreateBackup:=False

To remove a folder use RmDir followed by the path to the folder.