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 I’m 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 I’m 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

0
Utf
4/16/2010 3:27:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1008 Views

Similar Articles

[PageSpeed] 18

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 I’m 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 I’m 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
0
Dave
4/16/2010 5:01:09 PM
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 I’m 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 I’m 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
> 
0
Utf
4/16/2010 5:08:01 PM
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 I’m 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 I’m 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
> > 
0
Utf
4/16/2010 5:17:03 PM
Reply:

Similar Artilces:

Return the name of cell to sheet name
How do I return the cell name to the sheet name. When I start a ne sheet I write a name in a cell and I want it to automatically renam the sheet to that cell name //Andrea Olsso -- AndreaOlsso ----------------------------------------------------------------------- AndreaOlsson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1651 View this thread: http://www.excelforum.com/showthread.php?threadid=31367 introduce this in <thisworkbook> event code begins Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) ActiveSheet.Name =...

problem creating report
I have 15 working forms from 2 diferant colums from one table what I want is to run each query on its own line of a report with 4 calculations at the end I can select 1 query and produce the first line but how do I move to second line and query? thanks Duane Hookom wrote: > This is a really unusual question. What do your forms and columns have to do > with your queries and report? > > A report can have only one record source. This record source could be a > union query. You can also use subreports to display additional queries. > thank you ...

How change Sign-in Name?
I've been using Money for years. Recently I've changed my passport account email address because the old one will no longer be available to me in the near future. When starting Money, I entered the new email address and password and Money allows me to log in. However, the issue is, the next time I start Money, the old email address is back in there and I must enter the new email address every time I start Money. How can I change the Sign-in name to same the correct email address? (I tried File/Password Manager and it will show the new address... until I close and re-open mone...

Project Name on POP Receivings posting journal
Ability to put Project Name and number on the POP receivings posting journal ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=1beb1016-e5ea-4dd0-ae7e-0cdb71e8e8da&dg=micr...

Automatically include new code.
It would be nice to have a dex.ini option to have the system not prompt when including code from a new (or updated) chunk file. This way we could push updates to our clients and not deal with the support calls (which we'll get even if we send out an email ahead of time telling them to click "yes" when prompted). ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open ...

Moving Outlook Express to New PC
I got a new PC and am trying to move my old Outlook Express e-mails to it (both are using Outlook Express 6.00). I'm assuming that the .dbx files are the e-mail files. My old PC is storing these on C:\WINDOWS\Application Data\Identities\...[I don't feel like typing the number!]...\Microsoft\Outlook Express My new PC is storing these on C:\Documents and Settings\Matty\Local Settings\Application Data\Identities\...[similar long number]...\Microsoft\Outlook Express I tried copying the .dbx files from my old PC to the Documents and Settings\...\Outlook Express file on my new P...

New Mailbox
Hi All, When I create a userid object for a new employee in 'Users and Computers' the NT account is created. I usually wait about 3-5 minutes and the email address are created. Yesteday when I created a new user the email address took about 15 minutes, but Exchange 2003 only created a X500 email address. No smtp etc. I am thinking that this is a problem with RUS, however I have run a rebuild and update, and still no luck, I cant seem to find any messages in the event log either! I deleted the entry and recreated the same user with the same result. I hope someone can help me!!! Tha...

Help creating two listings from one record
I have extracted "members" from my database. One record will contain LastName and FirstName, and may also contain SpouseName. Because I am creating membership cards in Publisher, I think I probably should actually create another query or ?? that contains one record with either [FirstName LastName] or [SpouseName LastName] Probablyl something like ... If SpouseName greater spaces List SpouseName LastName else next sentence. List FirstName LastName I don't have a clue how to do this in Access. Appreciate all help. Try this -- [FirstName] &a...

Visual Basic
Hi, I trying to create a table using visual basic, when i try to run it. It prompt me "No fields defined, can't append Table or Index. Anyone, could highlight to me what is the problem. My code is as follows: Public Sub PCreateTable() Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim idx As DAO.Index Dim fldTradeDate As DAO.Field Dim fldStockName As DAO.Field Dim fldRemarks As DAO.Field Dim fldCurrency As DAO.Field Dim fldClose As DAO.Field Dim fldChange As DAO.Field Dim fldVolume As DAO.Field Dim fldHigh As DAO.Field Dim fldLow As DAO.Field Dim fldValue As DAO.Field Dim fldDay...

Creating Postcard 2 UP & 2 sided with Mail merge
Hi everyone: Good morning. I hope that some one can help with the design and setup for doing the following: 1) Postcard 8.5 by 5.5 (basically half of 8.% X 11 letter paper). 2) Print on both sides 3) Mail merge on both sides How do I set it up so that I can have 2 postcards to be printed on 1 sheet using either one of MS Publisher or MS Word? (prefer Word) I know that my printer does it. First, I already have the design in both MS Publisher and MS Word. I also have the mail merge done. MS Publisher: Page Setup: Width 8.5 Height: 5.5 Paper: Letter (8.5 X 11)...

How to create treeview active control as another activex control's child?
I want to create an activex control who will wrap a treeview active control but has some its own logic. I don't know how to create it and how to set its position and size. say,if I want my active control's size is the same as the treeview active control. Any idea ? Thanks ...

Customizing menu item names, form names etc.?
Hi all, Is there any way to customize the names of forms - for example if I want to call an "account" a "business partner" and modify it in the menu items and form/view names as well, can I do it? Thanks in advance, -- Anurag ...

Display middle name with Primary Contact field on Account screen?
How do I Display the middle name with Primary Contact field on Account screen? Please help. We use the Primary Contact field on the main Account screen to search and select a contact. The field then displays the First Name + Last Name of the contact that was selected. Can I make it show First Name + Last Name + Middle Name? How do I do it? I will be very appreciative for any help I receive. Thanks adna; The CRM has one format for displaying contact names. you can set that format in Settings-->Administration-->System Settings. While First Name + Last Name + Middle Name isn&...

Creating Shortcuts
I have a script running in an EXCEL document that creates new workbooks based on input from the user. It then saves the file in a set location. I would like to adjust the code to creat a shortcut to the new file and place the shortcut in a different folder. Is there a way to do this? In the VBE editor, go to Tools > References and set a reference to 'Windows Script Host Object Model" Now, the code below would create a shortcut named "Book1Shortcut" located in the folder "C:\Test\myfolder" with the target Excel file of the shortcut being "C:\Test\Boo...

Creating an additional profile in Outlook 2000?
Iam a newb with Outlook and have searched on here for a solution to my problem. I am using XP home edition and Outlook 2000 with update of SP3. My ISP provides me with 5 email accounts, all of which are not aliases from my understanding of what an alias account is. Mine are "name1@dodo.com.au","name2@dodo.com.au"etc. I am wanting to be able to access 2 email accounts, one with my nickname for friends and forums and the other for my new business, while using my version of Outlook 2000 without having to upgrade to a newer version. I don't want to create a new '...

Insert file name in Excel
I want to insert the file name into a cel of my Excel Worksheet. I need this for document management issues with Microsoft Business Soltutions Axapta. I work with Microsoft Office Excel 2003. =MID(CELL("filename",A1),FIND("[",CELL("filename",A1)) +1,FIND("]",CELL("filename",A1))-FIND("[",CELL ("filename",A1))-1) HTH Jason Atlanta, GA >-----Original Message----- >I want to insert the file name into a cel of my Excel Worksheet. >I need this for document management issues with Microsoft Business >Soltutions ...

Moving Outlook to a New Computer #3
I use Outlook as my email software, and have a large number of message rules set up to move messages to the correct folders, etc. I am getting a new laptop tomorrow, and want to use it now as the computer I do all my emailing from. I've found info on how to reconnect my old PST file onto the new machine (http://office.microsoft.com/en-us/outlook/HA010771141033.aspx). My question is whether this will move all of my message rules to the new machine, and what I need to do to create the various folders on the new machine. Can I copy over the old file structure of these folders to the new m...

Add names to scatter plot chart
I have three columns of info in my excel spreadsheet. column 1 has names, column 2 has numbers and column 3 has numbers. I can create the scatter plot chart just fine, but what I am having a problem with is that I cannot get the names from column one to show next to the points on the scatter plot. How is this done? -- oberon.black ------------------------------------------------------------------------ oberon.black's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26732 View this thread: http://www.excelforum.com/showthread.php?threadid=471758 I'm gu...

Reading material for someone new to Exchange
Hi, Exchange 2003 has recently been set up on our Small Business Edition 2003 server. I know nothing about it and want some beginner information to find out what it is capable of doing and how to do it. All the books on Amazon seem to be for advanced users so I'd appreciate if someone could recommend some books for beginners. Thanks in advance, George On Wed, 10 May 2006 12:23:28 +0100, "George Taylor" <newsgroups@kellymurray.ie> wrote: >Hi, > >Exchange 2003 has recently been set up on our Small Business Edition 2003 >server. > >I know nothin...

Get control of a Userform by the Name
I want to change the text of a form. I know the name of the form and the name of the control in it. How to get the control of a form if I know the Name? Below post refers to adding a form. But I already have a form, just need the control of that! http://groups.google.co.in/group/microsoft.public.excel.programming/browse_thread/thread/beedb666e1d7a29b/f2df993ded867dd4?hl=en&lnk=gst&q=form+by+name#f2df993ded867dd4 Thanks a lot in advance Regards' Joe If you are talking about a VBA user from in VBA window press F4 (View properties window) and click on the control....

New account with new Domain
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I am trying to set up a new entourage email account using a new recently purchased domain. I am putting in mail@domainname.com for incoming and out going mails but when i verify details it tells me both are not right. Do I have to have set up the hosting for the website before being able to set up the email account or do I need to set up the email address at somewhere like GMail first and then will I be able to do it???? Any help appreciated. > This message is in MIME format. Since your mail reader does not understand this f...

Rename Cell Name
hey all, i have change the cell anem from A1 to StartCell. Bu, how can i rename it back to A1 or change it to another name? Thanks in advance Regards Dragon Hi Dragon go into insert / name / define - you can delete the name there and create another if you wish. Cheers JulieD "Dragon" <Dragon@discussions.microsoft.com> wrote in message news:B6A6510E-0233-4B2A-8A0C-F16F73585CBA@microsoft.com... > hey all, i have change the cell anem from A1 to StartCell. Bu, how can i > rename it back to A1 or change it to another name? > Thanks in advance > Regards > Drago...

What is the Outlook keyboard shortcut for checking new mail
"Steve Jaeger" <Sjaeger1@elp.rr.com> wrote in message news:043f01c36b5f$468b1110$a601280a@phx.gbl If there is a coded shortcut for a menu item, you will see it on the rightside of the menu. If you open Tools -> Send/Receive -> Send/Receive All, you will see F9 listed on the right. Also, it is considered impolite to use only the Subject line to carry the body of your message. -- ____________________________________________________________ ** Share with others. Post replies in the newsgroup. ** If present, remove all "-nix" from my email address. _________...

pivot table: create worksheetsheet using pivot table
While working in a pivot table, I accidentally created a new worksheet in my file with isolated data from the report e.g. pivot table shows all days of the month with totals for each deposit account... my 'accident' created a sheet with all the data for a single date. It turns out, this would be a useful tool for my manager, but we cannot reproduce my accident. My mouse was on the blitz and after a few solo selections the new worksheet appeared. Does anyone know how to do this (intentionally)? Please help. ...

Open new form based on two criteria
I have a main form that I need to be able to open another form that is based on two different fields on the main form. This is what I have: Private Sub cmdOpenForm_Click() On Error GoTo Err_cmdOpenForm_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "SFRM_AllLocationsperContract" stLinkCriteria = (((TBL_LocationContract.ContractNumber) = "&[Forms]! [FRM_CompanyAddContracts]![txtContractID]&") And ((TBL_LocationContract. CompanyID) = "&[Forms]![FRM_CompanyAddContracts]![txtCompanyID]&")) DoCmd...