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

 

[Andrzejek]

Did my MsgBox work for you in thread68-1788720 ?


---- Andy

There is a great need for a sarcasm font.

 

[pendle666]

Hi

well it gave me a message box with the correct month, but the user still has to navigate there - which they'd already know where to go.

thank you for helping

____________
Pendle

 

[strongm]

Andrzejek was, as is often the case on tek-tips, following the philosophy of teaching a man to fish, rather than giving a man a fish.

Having said that, my subsequent post in that thread is a fish ...

 

[SkipVought]

You would modify the PATH by concatenating the month as a folder as Andy suggested.

Don’t need to change drives or directory.

I’m cutting bait.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Skip is right, as (almost) always.
You can use the code from the MsgBox in your own code to point to the right folder.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Ah, Andy, you’ve discovered my uninfallibility.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Skip, if "Infallibility is the inability to be wrong", then the [highlight #FCE94F]un[/highlight]infallibility must be the "inability to be right", right?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

That is un-ir-regardless of the circumstances.

...and “right, right?” Is that mono-legged or an anarchist?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

But, back to the issue at hand, pendle666's question

Did you make it work in your code?
Something like....:

...
ActiveWorkbook.SaveAs Filename:="C:\SomeFolder\" & _
     Format(Month(Date), "00") & " - " & MonthName(Month(Date)) & "\NHE\" & _
    "MyExcelFile.xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
...

---- Andy

There is a great need for a sarcasm font.

 

[pendle666]

Sorry, had to get back to my normal job yesterday afternoon (I work in Payroll, I'm not a programmer)

Using this:

[pre]ActiveWorkbook.SaveAs Filename:="C:\SomeFolder\" & _
Format(Month(Date), "00") & " - " & MonthName(Month(Date)) & "\NHE\" & _
"MyExcelFile.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False[/pre]

gives me an error right at the start: Run time error 1004 Method 'SaveAs' of object'_workbook' failed

So I tried this version :

[pre]Dim varResult As Variant

ChDrive "C"
ChDir "C:\SomeFolder\"

varResult = Application.GetSaveAsFileName( _
FileFilter:="Excel Files (*.xlsx), *.xlsx") & Format(Month(Date), "00") & " - " & MonthName(Month(Date)) & "\NHE\"[/pre]

This goes to the initial directory (names changed for confidentiality) but user would still need to navigate to current month and NHE. Although it allowed me to give the file a name, it doesn't actually save the file with that name, and it's still on it's original CSV file name.


So I tried this version:

[pre]ChDrive "C"
ChDir "C:\SomeFolder\"

ActiveWorkbook.SaveAs Filename:="exported_file.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False[/pre]


And while I'm getting the correct file name, it's saving it into my documents folder rather than the one specified. Never mind any sub folders.

I'm completely lost with this.



thank you for helping

____________
Pendle

 

[SkipVought]

You want to Save As varResult

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

I tried that :

[pre] varResult = SaveAsFileName( _
FileFilter:="Excel Files (*.xlsx), *.xlsx") & Format(Month(Date), "00") & " - " & MonthName(Month(Date)) & "\NHE\"[/pre]


But it's erroring out whether I use SaveAs or SaveAsFileName


I'll abandon this now- the user can sort it out for themselves.

thanks for your help

thank you for helping

____________
Pendle

 

[SkipVought]

Lets go back to

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

Is there one specific primary folder (under which are sub folders for each month) or does the user need to select the primary folder, because there are multiples primary folders under which are sub folders for each month?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

>I tried that

1) It isn't FileFilter that you need to set to get to the folder, it is InitialFileName
2) Neither SaveAs nor SaveAsFileName are legitimate methods. You want GetSaveAsFilename

 

[SkipVought]

So why not...

varResult = "C:\Documents\Overtime Files\" & Format(Month(Date), "00") & "\" & ThisWorkbook.Name
SaveAs varResult, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

I think the point is, Skip, that he wants the user to be able to choose the filename to save to ...

 

[SkipVought]

Well, I went back to the original post an he’s assigning a directory, the one I specified, then he’s using the month as a sub-directory.

I’m getting the impression that he thinks that GetSaveAsFilename does a SaveAs, or that by changing the directory, SaveAs goes to that dir.

So pendle666, are you trying to save to a various number of primary paths, like “C:\Documents\Overtime Files\” for one and there are others that the user may choose instead, and THEN, from there, the month sub directory and other stuff is appended, and finally the workbook name.extn?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

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.

and

it allowed me to give the file a name

Seems reasonably clear to me that pendle666 is expecting the user to be able to select/set the filename in a specific folder. But yes, there is also a suggestion that they think GetSaveAsFileName additionally saves to that filename. So, something like the following:

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

 

[pendle666]

Hi

The file should be saved to

C:\documents\overtime files\08 - August

for August, 09 - September for September and so on.

So this month's file I would hope to save automatically so 08 - August. The type of work means that it will only be done in the actual calendar month. The user will not need to save the file anywhere else.

thank you for helping

____________
Pendle