Excel VBA - Save As to go to specific folder depending on what month it is 1

[pendle666]

Hi

Part of my routine uses Save As to open a dialog box at the folder "overtime files" so that the user can name and save the workbook.

I've looked around and come up with the following:

Sub Otime_SaveFile()

Dim varResult As Variant

ChDrive "C"
ChDir "C:\Documents\Overtime Files\"

varResult = Application.GetSaveAsFileName( _
FileFilter:="Excel Files (*.xlsx), *.xlsx")

End Sub

There are a number of sub-folders in Overtime Files, one for each month, named as such:

01 - January
02 - February

etc (so that they're always in order) and in each of those files are more sub-folders, one of which is called NHE.

What I'd like to do is for Excel to go to the NHE file for the current month. So for it to know that it's August and go to the August folder.

Is that possible?

thank you for helping

____________
Pendle

 

[Andrzejek]

You should be asking VBA questions in forum707

But, try this:

MsgBox Format(Month(Date), "00") & " - " & MonthName(Month(Date)) & "\NHE\"

---- Andy

There is a great need for a sarcasm font.

 

[pendle666]

Oh right okay. I've always posted my Excel macro questions in this forum.





thank you for helping

____________
Pendle

 

[SkipVought]

Not so! You’ve posted in forum707.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

Worth pointing out that you don't really need ChDrive or ChDir, given you can exploit an undocumented quirk of GetSaveAsFileName (thanks to the way the underlying common dialog works), which is that the InitialFilename parameter can actually be a folder rather than a filename which will - as long as the folder exists - cause the dialog to start browsing at that folder. So something like the following:

[blue]varResult = Application.GetSaveAsFilename(InitialFileName:="C:\Documents\Overtime Files\" & Format(Month(Date), "00\\NH\E\\"), FileFilter:="Excel Files (*.xlsx), *.xlsx")[/blue]

or

[blue]varResult = Application.GetSaveAsFilename(InitialFileName:="C:\Documents\Overtime Files\" & Format(Month(Date), "00 - " & MonthName(Month(Date)) & "\\NH\E\\"), FileFilter:="Excel Files (*.xlsx), *.xlsx")[/blue]