Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a new folder according to the month
Hi,
I wanted to have my macro to save my file into a new folder according to the month it was for (ie, a new folder will be created every month). My macro goes like this, but it can't seem to work: Dim myMonth As String Dim myNewFolder As String myMonth = Format(Range("c4"), "yymm") myNewFolder = Format(Range("c4"), "MMM") myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & " SG51-1421000" ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal Thanks, Dolphin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a new folder according to the month
You didn't tell us what is in C4 when this is run. Make sure that what is in
C4 is actually a date, and that C4 is formatted as a date. Basically your code works for me with that stipulation. "Dolphin" wrote: Hi, I wanted to have my macro to save my file into a new folder according to the month it was for (ie, a new folder will be created every month). My macro goes like this, but it can't seem to work: Dim myMonth As String Dim myNewFolder As String myMonth = Format(Range("c4"), "yymm") myNewFolder = Format(Range("c4"), "MMM") myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & " SG51-1421000" ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal Thanks, Dolphin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a new folder according to the month
I notice there is no \ between your folder name and file name? myFilnename="M:\RECONCILIATIONS\" & myNewFolder & "\" & myMonth & "SG51-1421000" -- Hth Kassie Kasselman "JLatham" wrote: You didn't tell us what is in C4 when this is run. Make sure that what is in C4 is actually a date, and that C4 is formatted as a date. Basically your code works for me with that stipulation. "Dolphin" wrote: Hi, I wanted to have my macro to save my file into a new folder according to the month it was for (ie, a new folder will be created every month). My macro goes like this, but it can't seem to work: Dim myMonth As String Dim myNewFolder As String myMonth = Format(Range("c4"), "yymm") myNewFolder = Format(Range("c4"), "MMM") myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & " SG51-1421000" ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal Thanks, Dolphin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a new folder according to the month
Hi,
Cell C4 is a date format. The macro works well if I do not add the myNewFolder in. Upon adding it in (and include your comment whereby I should add "\"), I was given a runtime error 1004. Why is this so? Regards, Dolphin "kassie" wrote: I notice there is no \ between your folder name and file name? myFilnename="M:\RECONCILIATIONS\" & myNewFolder & "\" & myMonth & "SG51-1421000" -- Hth Kassie Kasselman "JLatham" wrote: You didn't tell us what is in C4 when this is run. Make sure that what is in C4 is actually a date, and that C4 is formatted as a date. Basically your code works for me with that stipulation. "Dolphin" wrote: Hi, I wanted to have my macro to save my file into a new folder according to the month it was for (ie, a new folder will be created every month). My macro goes like this, but it can't seem to work: Dim myMonth As String Dim myNewFolder As String myMonth = Format(Range("c4"), "yymm") myNewFolder = Format(Range("c4"), "MMM") myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & " SG51-1421000" ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal Thanks, Dolphin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a new folder according to the month
What I normally did, was to create the month folders in advance. Your
problem here is that you try to create a new folder, without using MkDir! You will either have to add a line of code to create the new directory, using MkDir, before doing the save, or else manually create the new directory. If you do it with code, once the directory is there, you are again going to get an error -- Hth Kassie Kasselman "Dolphin" wrote: Hi, Cell C4 is a date format. The macro works well if I do not add the myNewFolder in. Upon adding it in (and include your comment whereby I should add "\"), I was given a runtime error 1004. Why is this so? Regards, Dolphin "kassie" wrote: I notice there is no \ between your folder name and file name? myFilnename="M:\RECONCILIATIONS\" & myNewFolder & "\" & myMonth & "SG51-1421000" -- Hth Kassie Kasselman "JLatham" wrote: You didn't tell us what is in C4 when this is run. Make sure that what is in C4 is actually a date, and that C4 is formatted as a date. Basically your code works for me with that stipulation. "Dolphin" wrote: Hi, I wanted to have my macro to save my file into a new folder according to the month it was for (ie, a new folder will be created every month). My macro goes like this, but it can't seem to work: Dim myMonth As String Dim myNewFolder As String myMonth = Format(Range("c4"), "yymm") myNewFolder = Format(Range("c4"), "MMM") myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & " SG51-1421000" ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal Thanks, Dolphin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a new folder according to the month
I noticed the lack of the "\" between myNewFolder and myMonth - I took it
that you just wanted a really odd looking file (folder) name. Kassie's explanation of why you got the 1004 error once you added that into the fully formed path is right on target - You can use DIR to test to see if the folder exists, and if not, then use MkDir to create it before doing the save. "Dolphin" wrote: Hi, Cell C4 is a date format. The macro works well if I do not add the myNewFolder in. Upon adding it in (and include your comment whereby I should add "\"), I was given a runtime error 1004. Why is this so? Regards, Dolphin "kassie" wrote: I notice there is no \ between your folder name and file name? myFilnename="M:\RECONCILIATIONS\" & myNewFolder & "\" & myMonth & "SG51-1421000" -- Hth Kassie Kasselman "JLatham" wrote: You didn't tell us what is in C4 when this is run. Make sure that what is in C4 is actually a date, and that C4 is formatted as a date. Basically your code works for me with that stipulation. "Dolphin" wrote: Hi, I wanted to have my macro to save my file into a new folder according to the month it was for (ie, a new folder will be created every month). My macro goes like this, but it can't seem to work: Dim myMonth As String Dim myNewFolder As String myMonth = Format(Range("c4"), "yymm") myNewFolder = Format(Range("c4"), "MMM") myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & " SG51-1421000" ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal Thanks, Dolphin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help (Uppercase multiple ranges?) | Excel Discussion (Misc queries) | |||
Selecting the current month using a macro on a pivot table | Excel Discussion (Misc queries) | |||
Trying to Create a Macro | Excel Worksheet Functions | |||
Macro to create PDF's question | Excel Discussion (Misc queries) | |||
How to create an "IF - THEN" macro | Excel Discussion (Misc queries) |