Saving File in Directory depending on date

Smoothas

New Member
Joined
Aug 29, 2007
Messages
3
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 )
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

New Member
Joined
Aug 29, 2007
Messages
3
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

New Member
Joined
Aug 29, 2007
Messages
3
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top