Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new folderand name it the previous month
HI All,
I would like help with the following: I would like to create a folder with the name of the current month less one. Next, I would like to Save As the active file Im working on with the name Audit Report for current month less one. I would like to save this audit report in the folder I just created above. The code Im trying to use is below. I think my problems are with the items in blue MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy") ActiveWorkbook.SaveCopyAs Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above \AuditReport_" & Format(Now, "mmm_yy") & ".xls" Thanks in advance for any help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new folderand name it the previous month
Dim NewFolderName as string
NewFoldername = "\\(longstringhere\" & format(date - day(date), "mmm-yy") on error resume next 'in case it already exists mkdir newfoldername on error goto 0 activeworkbook.saveas filename:= newfoldername _ & "\auditreport_" & format(date-day(date),"mmm-yy") & ".xls" =========== Date - Day(date) will give the last day of the previous month. (April 16, 2010 - 16 days is March 31, 2010) Then just format that the way you want -- But I'd use a 4 digit year. In fact, I'd use: format(date - day(date), "yyyy-mm") Then it would make it easier to sort in chronological order. ram wrote: HI All, I would like help with the following: I would like to create a folder with the name of the current month less one. Next, I would like to Save As the active file Im working on with the name Audit Report for current month less one. I would like to save this audit report in the folder I just created above. The code Im trying to use is below. I think my problems are with the items in blue MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy") ActiveWorkbook.SaveCopyAs Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above \AuditReport_" & Format(Now, "mmm_yy") & ".xls" Thanks in advance for any help -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new folderand name it the previous month
I assume that the path all the way out to \MonthlyReport\ actually exists -
otherwise you have to create the sub-folders one at a time. Assuming that - then here's how I'd probably do it 'early in the module Dim newName As String newName = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "mmm-yy") MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & newName and later ActiveWorkbook.SaveCopyAs _ Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\" & newName & _ Application.PathSeparator & newName & ".xls" "ram" wrote: HI All, I would like help with the following: I would like to create a folder with the name of the current month less one. Next, I would like to Save As the active file Im working on with the name Audit Report for current month less one. I would like to save this audit report in the folder I just created above. The code Im trying to use is below. I think my problems are with the items in blue MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy") ActiveWorkbook.SaveCopyAs Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above \AuditReport_" & Format(Now, "mmm_yy") & ".xls" Thanks in advance for any help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new folderand name it the previous month
Thank you very much this is just what I needed
"JLatham" wrote: I assume that the path all the way out to \MonthlyReport\ actually exists - otherwise you have to create the sub-folders one at a time. Assuming that - then here's how I'd probably do it 'early in the module Dim newName As String newName = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "mmm-yy") MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & newName and later ActiveWorkbook.SaveCopyAs _ Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\" & newName & _ Application.PathSeparator & newName & ".xls" "ram" wrote: HI All, I would like help with the following: I would like to create a folder with the name of the current month less one. Next, I would like to Save As the active file Im working on with the name Audit Report for current month less one. I would like to save this audit report in the folder I just created above. The code Im trying to use is below. I think my problems are with the items in blue MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy") ActiveWorkbook.SaveCopyAs Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above \AuditReport_" & Format(Now, "mmm_yy") & ".xls" Thanks in advance for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3-Color Scale Vlookup for Current Month/Previous/Pre-Previous | Excel Discussion (Misc queries) | |||
Retrieve data for previous 3, 6, 12 month given current month | Excel Worksheet Functions | |||
Previous Month End | Excel Discussion (Misc queries) | |||
copy worksheet from previous month and rename to current month | Excel Programming | |||
End of previous month | Excel Programming |