Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify changing directories | Excel Worksheet Functions | |||
Using macro to save xls as txt without changing originial xls file | Excel Programming | |||
Using Macro to Save xls to txt without changing file format | Excel Programming | |||
changing directories...with vba | Excel Programming | |||
changing directories | Excel Programming |