Saving a file to a newly created folder

knighttrader

New Member
Joined
Apr 3, 2010
Messages
11
Hi

I have a macro that makes a new folder (on a MAC) each day based on the days date.
VBA Code:
MkDir "/Users/Steve/Dropbox/Files/BA " & Format(Today(), "yyyy (mm) mmm dd ")
This creates a new folder in a folder called Files e.g. Files/BA 2021 (02) Feb 19

During the day a workbook is periodically saved (on a PC) using the days date and current time
Code:
ActiveWorkbook.SaveAs ("C:\Users\Administrator\Dropbox\Files\BA " & Format(Now(), "YYYY (MM) MMM DD hhmm ") & ".xlsm")
This creates a a new file in the folder called Files. e.g. Files/BA 2021 (2) Feb 19 1348


Question. How do I write the SaveAs file path so that the new file is saved in a folder based on the days date "BA " & Format(Today(), "yyyy (mm) mmm dd ")

E.g. Files\ BA 2021 (02) Feb 19 \BA 2021 (2) Feb 19 1348
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049
Question. How do I write the SaveAs file path so that the new file is saved in a folder based on the days date "BA " & Format(Today(), "yyyy (mm) mmm dd ")

E.g. Files\ BA 2021 (02) Feb 19 \BA 2021 (2) Feb 19 1348

Try this:
VBA Code:
    Dim folder As String
    folder = "C:\Users\Administrator\Dropbox\Files\BA " & Format(Date, "yyyy (mm) mmm dd ") & "\"
    If Dir(folder, vbDirectory) = vbNullString Then MkDir folder
    ActiveWorkbook.SaveAs folder & Format(Now(), "YYYY (MM) MMM DD hhmm ") & ".xlsm"
 
Solution

knighttrader

New Member
Joined
Apr 3, 2010
Messages
11
Try this:
VBA Code:
    Dim folder As String
    folder = "C:\Users\Administrator\Dropbox\Files\BA " & Format(Date, "yyyy (mm) mmm dd ") & "\"
    If Dir(folder, vbDirectory) = vbNullString Then MkDir folder
    ActiveWorkbook.SaveAs folder & Format(Now(), "YYYY (MM) MMM DD hhmm ") & ".xlsm"
Many thanks John
 

Forum statistics

Threads
1,144,370
Messages
5,723,959
Members
422,529
Latest member
mbilal429

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