Saving files to same drive location as template but in different folders.

[Kayslover]

Hi All,

I have an Excel macro enable template that creates files in the same location during execution of macros.

It uses the command

 fPath = ThisWorkbook.Path & "\"

during the saving of files.

I now require some of the files to be saved to dedicated folders on the same drive that the template is on. (The template is opened to run the macro's and is saved).

The folders in question are (call them main folders):-

• Invoices;
• Till Takings;
• Buylist;

All the above folders should exist in the root folder of the drive that the template is in.

Under each of the above folders is a folder for a year (e.g. 2019, 2020, 2021...).

The macro initially asks a question as to what year files need to be created for by using an InputBox and assigning the answer to a field called myYear.

I would initially like to check if the main folders exist and if they don’t I would like them to be created.
Once the main folders have been created or if they exist, I would like to create a year folder using the myYear as the name (e.g.\Invoices\2021).

How can I achieve the above?

Additionally, how do I set variables within the macro to be main folder name and year so that I can use that variables to save files to the appropriate folder?

What I mean is say a variable name of InvoicePathDir to be set to root directory\Invoices\ and InvoiceFullPathDir to be set to root directory\Invoices\myYear.

I can then use the variable names to save the required files into the appropriate folders rather than ActiveWorkbook.SaveAs Filename:=fPath......

Any assistance offered will be appreciated.



Please note that on my PC the templates will be run from the D drive, someone will have the templates on their PC on a pen drive which could be mapped as Z drive and someone else could have the templates on the PC on the C drive.

 

[EFANYoutube]

What name are the individual files going to be saved as

 

[Kayslover]

EFANYoutube,

The files in the folder Invoices are currently saved using the code:-

ActiveWorkbook.SaveAs Filename:=fPath & "Invoices Issued.xlsx", FileFormat:=xlOpenXMLWorkbook

I would change the fPath aspect to the variable name of root directory\Invoices\myYear


The files in the folder Buylist are currently saved using the code:-

ActiveWorkbook.SaveAs Filename:=fPath & myYear & " Buylist.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

I would change the fPath aspect to the variable name of root directory\Buylist\myYear

There are files which will be be save in the same folder as the template using the code:-

ActiveWorkbook.SaveAs Filename:=fPath & myYear & " Bar Area Taking Print.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.SaveAs Filename:=fPath & myYear & " Yearly Club Accounts.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

I would change the fPath aspect to the variable name of root directory\Accounts\myYear

 

[EFANYoutube]

So... this is what I have so far, It will ask what type of file it is, then what year, it will check if the folder exists and if not it will create it then it saves the file as an .xlsx in that location the user chose
This does not work if you are saving to One Drive

Dim BasePath As String

Sub RunSave()
    Dim FolderAnswer As String
    Dim FolderName As String
    Dim FolderYearName As String
    'Need to find out what folder to save too
    FolderAnswer = InputBox("Please choose what folder this file should be saved to:" & vbNewLine & _
                            "1. Invoices" & vbNewLine & _
                            "2. Till Takings" & vbNewLine & _
                            "3. Buylist" & vbNewLine & _
                            "Please type a number", "Choose Folder")
    'Set folder string to variable
    Select Case FolderAnswer
        Case Is = "1"
            FolderName = "\Invoices"
        Case Is = "2"
            FolderName = "\Till Takings"
        Case Is = "3"
            FolderName = "\Buylist"
        Case Else
            'data entered was not acceptable
            MsgBox "Invalid Choice", vbCritical, "Please try again"
            GoTo EndThis
    End Select
    
    'Need to find out what year to save too
    FolderAnswer = InputBox("Please type what year this file should be saved in:" & vbNewLine & _
                            " format = 'yyyy'", "Choose Year")
    'Error check for correct format
    If Len(FolderAnswer) <> 4 Then
            MsgBox "Year should be 'yyyy'", vbCritical, "Please try again"
            GoTo EndThis
    End If
    If IsNumeric(FolderAnswer) = False Then
            MsgBox "Year must be 4 numbers", vbCritical, "Please try again"
            GoTo EndThis
    End If
    FolderYearName = "\" & FolderAnswer
    
    'Check if FolderName exists
    If FolderExists(ThisWorkbook.Path & FolderName) = False Then
        'Create FolderName
        MkDir ThisWorkbook.Path & FolderName
    End If
    
    'Check if FolderYearName exists
    If FolderExists(ThisWorkbook.Path & FolderName & FolderYearName) = False Then
        'Create FolderName
        MkDir ThisWorkbook.Path & FolderName & FolderYearName
    End If
    
    'Now we know everything exists we save the current template as xlsx?
    ThisWorkbook.SaveAs ThisWorkbook.Path & FolderName & FolderYearName & "\" & Format(Now(), "yymmdd-hhmm"), xlWorkbookDefault
    
    
EndThis:
    
End Sub


Function FolderExists(strFolderName) As Boolean

Dim strFolderExists As String

    strFolderExists = Dir(strFolderName, vbDirectory)

    If strFolderExists = "" Then
        FolderExists = False
    Else
        FolderExists = True
    End If

End Function

 

[EFANYoutube]

As for your file names, we could set another variable once the file type is chosen to use in the final save as

 

[EFANYoutube]

The files in the folder Invoices are currently saved using the code:-

ActiveWorkbook.SaveAs Filename:=fPath & "Invoices Issued.xlsx", FileFormat:=xlOpenXMLWorkbook

So this says each time the save is done it will overwrite the current file in there?

 

[Kayslover]

EFANYoutube,

I accept you comment in post # 6, that if the files exists, it will be overwritten.

As the macro currently creates files and saves then in the same folder as the Template is in, asking the question for FolderAnswer would need to loop

Your variable FolderAnswer for the year you are defining for the year I already have code to deal with that as I ask what year we are going to create the files for (format yyyy) at the start of the macro.

Rather than asking the question “Please choose what folder this file should be saved to:” as there are 4 files that need to saved different folders and most will be save in folder Till Takings.

Can we not hard code variables as:-

nvoicePathDir = "\Invoices"
InvoiceFullPathDir = "\Invoices\" &myYear

BuylistPathDir = "\Buylist"
BuylistFullPathDir = "\Buylist\" &myYear

TillTakingsPathDir = "\Till Takings"
TillTakingsFullPathDir = "\Till Takings\" &myYear

The save as would then be:-

ActiveWorkbook.SaveAs Filename:= InvoiceFullPathDir & "Invoices Issued.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.SaveAs Filename:= BuylistFullPathDir & myYear & " Buylist.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.SaveAs Filename:= TillTakingsFullPathDir & "01 January.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
 ActiveWorkbook.SaveAs Filename:= TillTakingsFullPathDir & "02 February.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[EFANYoutube]

I don't understand?
You have 1 template but save it(duplicate it) 4 times to 4 different file locations but different file formats & names?
With Till Takings how do you know what the name will be?

 

[Kayslover]

EFANYoutube,

Apologies, for not been clearer in my starting post.

The macro asks the user to enter a year (and validates it). It then opens several other templates (all located in the same folder as the existing open template) on a one by one basis.
It then populates specific cells and then does a saveas to either a workbook or macro enabled workbook.

Your question regarding "With Till Takings how do you know what the name will be?". The Open template is a template that is used to record till taking. It creates 12 files (one for each month of the year) and names them 01 January, 02 February ...

I hope the above clarifies things.

 

[EFANYoutube]

Ok, makes sense
I have given you the code not for validating and creating and saving to sub folders, are you good from there?