Excel VBA - save file to go to folder for the month

[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

 

[pendle666]

I just tried the code that StrongM last posted:

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

It opened to my default documents, and then after I tried to cancel out created a file called false.xlsx

thank you for helping

____________
Pendle

 

[strongm]

That's because it isn't production code. It was an example that should just have pointed you in the right direction.

 

[pendle666]

Let's leave it.

The user can do their own thing.


thanks for your efforts.

thank you for helping

____________
Pendle

 

[strongm]

>It opened to my default documents

Indeed. As I said in your similar thread in the Office forum "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"

If the folder does not exist, it will open in your default documents folder