Saving File in Directory depending on date

[Smoothas]

Hello,

I've got a set of dir's labeled
07-01 January Stickers
07-02 February Stickers
etc

and i'm currently using the following code

Sub SaveStickers()

Dim strFileB
Const sPATH As String = "\\Server\Dir1\Dir2\Stickers\"
Const sFILE As String = "Fulfillment"
Const sVAR As String = ""
ActiveWorkbook.SaveCopyAs sPATH & strFileB & sVAR & _
Format(Now - 1, "dd-mm-yy ") & sFILE & ".csv"
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

What I now need to do is get the sPATH var ( I think ) to change depending on the current MM-YY and point to / create if it dosn't exist, the corresponding Dir.

Any Ideas on how I can do this ?

Thanks ( as always )

 

[Gates Is Antichrist]

You can create folders with the MkDir command.

A simple way to test if they already exist is to go
On Error Resume Next
and just always create it(do the MkDir). If it doesn't exist yet, it creates it; if it exists, it doesn't, and keeps going (without interruption)

 

[Smoothas]

Thats great, but how do I get the macro to save the file into a current, depending on the date. ie, save the file as "29-08-07 Fulfillment.csv", and because of the date, it tries to save it in folder "07-08 August".

Thank for your help re: MkDir. Much apprechated

 

[Gates Is Antichrist]

I don't understand you well but I think you want this: if you don't like the order & appearance of Date, then instead of Date, use Format(Date, "yy-mm MMM") or something like that. Check the help for Format for more detailed options. Try
debug.print "date:" & date & ";format:" & Format(Date, "yy-mm MMM")
and decide what you like.

If that's not your problem, and operations are not occurring with or from the folder you desire, consider affecting scope of operations with the ChDir command.

Do either of those solve your problem?

 

[Smoothas]

Thanks for the quick replay again.

I'm sorry I'm not being clear, I'll try again.

I have a Directory ( \\Server\Dir1\Dir2\Stickers\ ) full of sub directories, all labelled in the following format YY-MM MONTH ( ie 07-07 July, 07-08 August etc).

Under each of these directories is a file for every day of the month ( ie 01-07-07 Fulfillment.csv, 02-07-07 Fulfillment.csv etc).

The macro in my first post saves the file in the correct format, just in the root directory (\\Server\Dir1\Dir2\Stickers\).
I'm trying to find away to get the macro to save the file in a directory, depending on the current date (Ie. the filename gets saved as "29-08-07 Fulfillment.csv" into the Subdirectory "07-08 August" instead of \\Server\Dir1\Dir2\Stickers\. Then next month, the filename gets saved as 01-09-07 Fulfillment.csv into the Subdirectory "07-09 September" etc)

I hope this clarifies my question a little beeter than my 1st attempt

Thanks again for your kind help

 

[Gates Is Antichrist]

That's a lot of good information. I'll give you many thoughts that run through my mind in looking at a problem like that.

I'll go through this with a rough review of simple information I'm sure you know. Directories (or folders - another word for same thing) are often described as trees with branches. If you open Windows Explorer you can pop open branches that extend to the right from each folder - i.e. each "branch that has its own branches;" or no branches extending from nonfolder files.

So the simple point is that you can reference any file just by combining the branch names and a "slash" (\) between each one. If Windows Explorer, which is a "map of the tree" that begins with \\Server, showed a file 5 levels to the right of the start, you could get to it by just adding enough slashes and branch names.

So you were close at the beginning. You had the folder
\\Server\Dir1\Dir2\Stickers\
in which you could create files or more folders beneath it.

Let's say you did
Chdir "\\Server\Dir1\Dir2\Stickers\"
and then
MkDir "Smoothas"
Now to refer to Smoothas you could start with
Chdir "\\Server\Dir1\Dir2\Stickers\Smoothas\"

Now to get to subdirectory (subfolder) "07-08 August", you'd just tack it on like
MkDir "07-08 August"
and
Chdir "\\Server\Dir1\Dir2\Stickers\Smoothas\07-08 August"

The way to "tack it on" in code is with the concatenation operator "&" which works like
"A" & "B"
functionally equivalent to
"AB"

For your case. since
sPATH As String = "\\Server\Dir1\Dir2\Stickers\"
then
SPATH & "\" & "07-08 August"
would give
"\\Server\Dir1\Dir2\Stickers\07-08 August"

and you could CHDIR to there.

Is any of this making sense, or am I already telling you things you learned 10 years ago?! I just spit out numerous topics, keeping it very simple, to see if I hit a nerve.