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.

 

[Kayslover]

I have just been invited out for a pint or two of beer biggrin: ) and therefore I will try out your code and recommendations on Monday and let you know how I get on.

I appreciate the time you have given so far in helping.

 

[Kayslover]

EFANYoutube

The code you provide got me thinking and I did the following:-

To get the drive letter I used the code:-

CurrentFile = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".", -1, vbTextCompare) - 1))
Dim DriveLetter As String
DriveLetter = Left(CurrentFile, 1)

To Check if the folders for Invoices, Buylist and Till Takings exist or not I used the code:-

Dim InvoicePathDir As String
InvoicePathDir = "\Invoices"
Dim NewInvoicePath As String
Application.DisplayAlerts = False
ChDrive DriveLetter
On Error Resume Next
MkDir InvoicePathDir
On Error GoTo 0

I have used the MKDir code previously and if the folder exists, it doesn’t do anything, but if it doesn’t exist, it will create it.

I managed to achieve what I want with your assistance.

Thank you very much for your help, it pointed me in the right direction to get to the desired end point.