Saving a file to a "YYYY" and "MMM" folder

**hriggs** · 01-03-2008, 11:33 AM

Hi all,

Could anyone tell me if it is possible to get a file to save as follows:

filepath\folder called "YYYY"\folder called "MMM"\File.xls

where YYYY is the year and MMM is the month?

The folders YYYY and MMM may or may not exist. Is it possible to get excel to create them if they do not already exist?

thanks very much

hriggs

**hbrake1** · 01-03-2008, 04:15 PM

Creates a new directory or folder.

Syntax

MkDir path

The required path argument is a string expression that identifies the directory or folder to be created. The path may include the drive. If no drive is specified, MkDir creates the new directory or folder on the current drive.

So just make your new folder name a string you pass
ie......
mdate = yyyymm

C:/My Docs/mdate

**hriggs** · 01-04-2008, 06:10 AM

thanks for the reply but I don't understand. Could you tell me how I would incorporate that into this :

Please Login or Register  to view this content.

This would be run at the end of each day to archive "Art Analysis.xls"

I need it to save to the existing year and month folder, but if there is not one present (e.g. start of the new month) then I need it to create the directory.

thanks again

**hriggs** · 01-07-2008, 07:09 AM

bump....

**broro183** · 01-07-2008, 07:53 AM

hi,

The below is a coding approach I use frequently (copied & pasted w/o the guts of the macro - hopefully I haven't deleted too much) & most of the ease comes from Ken Pul's function which is included below...

Please Login or Register  to view this content.

hth
Rob

**broro183** · 01-07-2008, 08:10 AM

hi again,

This would be run at the end of each day to archive "Art Analysis.xls"

On rereading your post I noticed that with your current naming convention
the file could be over-written each day but if you were to modify my code you would get a msgbox saying the file already exists and the macro would end. This can be overcome by including a date code in the file name so that each day has unique filename.
I use the below with yyyymmdd as the date order because this allows for easy chronological sorting based on the file name.

Please Login or Register  to view this content.

Let us know if you have any more questions & hopefully someone else can help - I'm off to bed now.

hth
Rob

**hriggs** · 01-07-2008, 09:44 AM

Thanks very much - that code works really well. I changed a couple of small bits and it does exactly what I wanted

Now all I have do do is copy and change it for 26 files!

Cheers

hriggs

**hriggs** · 01-07-2008, 01:59 PM

Ok, this is what I have so far.

I've edited the macro a bit and it all still seems to work. It was getting stuck on the

Please Login or Register  to view this content.

bit and removing this did not seem have any effect. The code below seems to work fine and archives the file as needed

I changed private sub to sub so that I could see it in the macro menu.

Please Login or Register  to view this content.

My question now is how do I add another section to this code, so that I can archive another file?

Can I just copy the whole thing and change the file paths and names? or do I need something extra due to the

Please Login or Register  to view this content.

bit? Will it know where to go back to?

Thanks again for all your help

**broro183** · 01-09-2008, 05:48 AM

hi,

Sorry it has taken so long to get back to you & I'm guessing you're all sorted by now but anyway...

The IsMacroComplete was part of some error handling I had in the guts of my macro so you're right it deleting it won't have any effect, the problem ws probably due to the fact that I had deleted its declaration ("dim ismacrocomplete as boolean") but I hadn't deleted one of the sections using it.

To archive another file you should just need to change the filenames within the brackets (ie "DoesFileFolderExist (xyz)" )because all the DoesFileFolderExist function is doing is checking if a file or a folder exists & then returning either "TRUE" or "FALSE" for use in the rest of the code.

I've tried to keep all the definitions at the top - you only need to change them in one place then. I suggest changing the line

Please Login or Register  to view this content.

A couple of code shortening changes that you can make are:

Please Login or Register  to view this content.

In this code the colon ":" allows for 2 lines of code to be merged/written on the same line (this is the opposite of a "space underscore enter" which splits one line of code into two).

Please Login or Register  to view this content.

hth
Rob

**hriggs** · 01-09-2008, 07:17 AM

Thanks very much for you help again Rob, it has helped greatly! Very much appreciate your time

**broro183** · 01-10-2008, 07:00 AM

hey I'm just pleased I could help - thanks for the feedback :-)

Rob

Saving a file to a "YYYY" and "MMM" folder

LinkBack

Thread Tools

Rate This Thread

Display

Saving a file to a "YYYY" and "MMM" folder

Create a new directory or folder.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions