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

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

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 & "\"
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

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
EFANYoutube

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

To get the drive letter I used the code:-

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

VBA 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.
 

Forum statistics

Threads
1,137,059
Messages
5,679,369
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top