Find next available folder name and create folder then save file.

[Cyclosarin]

Hello there,

I'm stumped, so i have a macro enabled Template in C:\Template\ChangeRequest\
In that same folder i have different folders all named incrementally like so:

C:\Template\ChangeRequest\CR-0001
C:\Template\ChangeRequest\CR-0002
C:\Template\ChangeRequest\CR-0003
C:\Template\ChangeRequest\CR-0004
C:\Template\ChangeRequest\CR-0005 etc...

What i'd like to do is for Excel to look what the last folder name is, add 1 and put that in "D6" as a value.
Then when pressing a save button create a directory with that same value then save the sheet as a ".xlsx" (no macro's), and if it already exists show a message that it can't be overwritten and maybe a promt to fill in a new name.

I can't for the life of me find something simillar to start off of..
Please help?

 

[Akuini]

Hi, @Cyclosarin. Welcome to the Forum
The folder name pattern, is it always "CR-" followed by 4 digit?
Then try this:

Sub GetSubFolderNames_1180157()
Dim FileName As String
Dim PathName As String

PathName = "C:\Template\ChangeRequest\"
FileName = Dir(PathName, vbDirectory)

Do While FileName <> ""
    If GetAttr(PathName & FileName) = vbDirectory Then tx = FileName
    FileName = Dir()
Loop

z = Split(tx, "-")(1)
Range("D6") = "CR-" & Format(z + 1, "0000")

End Sub

What i'd like to do is for Excel to look what the last folder name is, add 1 and put that in "D6" as a value.

The code does that.
I haven't write the code to save the file. Just see if the code gives the correct result in D6.
If it works then I'll write the code to save the file.

when pressing a save button ...

The button is in a sheet? what sheet?

 

[Cyclosarin]

Hi, @Cyclosarin. Welcome to the Forum
The folder name pattern, is it always "CR-" followed by 4 digit?
Then try this:

Sub GetSubFolderNames_1180157()
Dim FileName As String
Dim PathName As String

PathName = "C:\Template\ChangeRequest\"
FileName = Dir(PathName, vbDirectory)

Do While FileName <> ""
    If GetAttr(PathName & FileName) = vbDirectory Then tx = FileName
    FileName = Dir()
Loop

z = Split(tx, "-")(1)
Range("D6") = "CR-" & Format(z + 1, "0000")

End Sub

The code does that.
I haven't write the code to save the file. Just see if the code gives the correct result in D6.
If it works then I'll write the code to save the file.


The button is in a sheet? what sheet?

Hello and thank you! I'll try this out in about an hour or so of you don't mind.

The button is on a sheet yeah. It's basically on sheet1 (still need to name it). So if you have any examples I could use that'll be greatly appreciated.

 

[Cyclosarin]

Hi, @Cyclosarin. Welcome to the Forum
The folder name pattern, is it always "CR-" followed by 4 digit?
Then try this:

Sub GetSubFolderNames_1180157()
Dim FileName As String
Dim PathName As String

PathName = "C:\Template\ChangeRequest\"
FileName = Dir(PathName, vbDirectory)

Do While FileName <> ""
    If GetAttr(PathName & FileName) = vbDirectory Then tx = FileName
    FileName = Dir()
Loop

z = Split(tx, "-")(1)
Range("D6") = "CR-" & Format(z + 1, "0000")

End Sub

The code does that.
I haven't write the code to save the file. Just see if the code gives the correct result in D6.
If it works then I'll write the code to save the file.


The button is in a sheet? what sheet?

It works Flaweleslly, I think i should be able to figure out how to save it from here so i'm marking your reply as solution!
Thank you so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.