Macro to save file in changing directories?
 

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!

Macro to save file in changing directories?
 

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"

--
Best wishes,

Jim


" wrote:

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!

Macro to save file in changing directories?
 

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.

--
urkec


" wrote:

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!