SaveAs VBA Question

[data808]

I have a SaveAs VBA code that is assigned to a button. Right now it saves to a directory folder that never changes and the file name looks like this:

JS_09-12-2022.xlsm

Here is the VBA that does this:

ActiveWorkbook.SaveAs Filename:="K:\DAT\John Smith\" _
& "TYO_" & Format(Now(), "MM-DD-YYYY") & ".xlsm"

So it saves it to John Smith's folder and I do this so that the directory is static and then later at the end of the year I will have to organize the files and throw it in a 2022 folder manually within John's folder.

Is there a way so that this file can go into the current year folder based off the file name year, which it will always pull the current because of the Format(Now() function, so that I won't have to keep organizing manually or make changes to the VBA every year so that it goes into the folder I want?

Thanks

 

[Joe4]

As long as the yearly folder has already been set-up, how about:

ActiveWorkbook.SaveAs Filename:="K:\DAT\John Smith\" _
    & Format(Now(), "YYYY") & "\" _
    & "TYO_" & Format(Now(), "MM-DD-YYYY") & ".xlsm"

 

[data808]

As long as the yearly folder has already been set-up, how about:

ActiveWorkbook.SaveAs Filename:="K:\DAT\John Smith\" _
    & Format(Now(), "YYYY") & "\" _
    & "TYO_" & Format(Now(), "MM-DD-YYYY") & ".xlsm"

Excellent! Works great. I actually have another spreadsheet that does the SaveAs in a similar way but was wondering if we could assign the search of the current year folder of where to save the file and reference it to cell G2 which is where the user would enter the year (YYYY). So basically the VBA will search where to save the file based on what year the user enters.

Right now this is the current SaveAs code which uses a Dim fname as string:

fname = "C:\Users\john.smith\Desktop\CITS_" & Format(DateValue(mnth & "-01"), "mm") _
& mnth & "_" & Range("G2") & ".xlsm"

Which makes the file save like this: "CITS_12SEP_2022.xlsm"

Then concludes it with this:

ActiveWorkbook.SaveAs Filename:=fname

 

[Joe4]

Excellent! Works great. I actually have another spreadsheet that does the SaveAs in a similar way but was wondering if we could assign the search of the current year folder of where to save the file and reference it to cell G2 which is where the user would enter the year (YYYY). So basically the VBA will search where to save the file based on what year the user enters.

Right now this is the current SaveAs code which uses a Dim fname as string:

fname = "C:\Users\john.smith\Desktop\CITS_" & Format(DateValue(mnth & "-01"), "mm") _
& mnth & "_" & Range("G2") & ".xlsm"

Which makes the file save like this: "CITS_12SEP_2022.xlsm"

Then concludes it with this:

ActiveWorkbook.SaveAs Filename:=fname

Do you mean something like this?

fname = "C:\Users\john.smith\Desktop\" & Format(Range("G2").Value,"YYYY") & "\CITS_" _
    & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

 

[data808]

Do you mean something like this?

fname = "C:\Users\john.smith\Desktop\" & Format(Range("G2").Value,"YYYY") & "\CITS_" _
    & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

Thanks. Ok so I tried that code you provided and get an error message:

Run-Time error '1004':
Microsoft Excel cannot access the file
'C:\Users\john.smith\Desktop\1905\3B285D40'. There are several possible reasons:
- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a (message gets cut off here)

Then it has the "End" or "Debug" buttons so I click Debug and it points to this line:

ActiveWorkbook.SaveAs Filename:=fname

I did a bit more testing to see if my old SaveAs line works and it does with no problems. Here is the old line:

fname = "C:\Users\john.smith\Desktop\CITS_" & Format(DateValue(mnth & "-01"), "mm") _
& mnth & "_" & Range("G2") & ".xlsm"

Sorry I also should mention that the user is not typing in the year "2022" for example, into G2 cell but rather selecting the year from a drop down list that I made with data validation. Not sure if that makes a difference. Also I'm also thinking that the formatting function may not be needed as the cell G2 is only of the year and nothing else.

 

[data808]

Do you mean something like this?

fname = "C:\Users\john.smith\Desktop\" & Format(Range("G2").Value,"YYYY") & "\CITS_" _
    & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

Ok I fixed it. Here is what worked:

fname = "C:\Users\john.smith\Desktop\" & Range("G2") & "\CITS_" _
    & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

Thank you so much for your help. You did all the hard work. You just opened my eyes to see the solution. Thanks again. Appreciate it.

 

[Joe4]

OK, I thought G2 was a date, but it sound like it is just a number.
Then you should just be able to use:

fname = "C:\Users\john.smith\Desktop\" & Range("G2").Value & "\CITS_" _
    & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

 

[Joe4]

OK, I thought G2 was a date, but it sound like it is just a number.
Then you should just be able to use:

fname = "C:\Users\john.smith\Desktop\" & Range("G2").Value & "\CITS_" _
    & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

Whoops, didn't see that you just replied again, and discovered the same thing I explained.

Glad you got it figured out!

 

[data808]

Whoops, didn't see that you just replied again, and discovered the same thing I explained.

Glad you got it figured out!

Thanks for your help. You showed me the way. lol