Macro to save file in changing directories?

[vrzimmerm]

I am trying to write a macro that will save the current spreadsheet in
a unique directory depending on an input supplied by the user. In
this case the user will input a month number. If they input "1" the
macro will save the file in a directory named "Jan". If "2" is
inputted it will save the file to "Feb" etc. What would the VBA
coding for this look like?

Would it be easier if I have the user just input "Jan"....then use
this info in the path for the save command?

Many thanks!

 

[Jim Jackson]

You could use seomthing like this.
fnum = InputBox("Enter the month number.")
If fnum = 1 then
fldr = "Jan"
Elseif fnum = 2 then
fldr = "Feb"
etc, etc, etc
End if
This Workbook.SaveAs("X:\" & fldr & "Wrkbookname.xls"

 

[urkec]

Try something like this:

Sub saveBook()

Set fso = CreateObject _
("Scripting.FileSystemObject")

'get the month number
monthNO = InputBox _
("Enter the month number")

'some validation
If Not IsNumeric(monthNO) Then
MsgBox "Please enter a number 1 - 12"
Exit Sub
End If

'more validation
If monthNO < 1 Or monthNO > 12 Then
MsgBox "Please enter a number 1 - 12"
Exit Sub
End If

'create folder path
'based on the entered number
folderPath = "C:\" & _
MonthName(monthNO, True) & "\"

'check if the folder exists
'if not create it
If Not fso.FolderExists(folderPath) Then
fso.CreateFolder (folderPath)
End If

'save the active workbook
Application.ActiveWorkbook.SaveAs _
folderPath & "book.xls"


End Sub

You can also add code to check if a file with that name already exists.