Hi All,
I have an Excel macro enable template that creates files in the same location during execution of macros.
It uses the command
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):-
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.
I have an Excel macro enable template that creates files in the same location during execution of macros.
It uses the command
VBA Code:
fPath = ThisWorkbook.Path & "\"
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;
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.