Change Save to Directory depending on Month

[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 ( thanks to help from Combo )

Sub SaveStickers()

Dim strFileB
Const sPATH As String = "\\Solaris\Operations\Clients\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 to change depending on the current MM-YY and point to the corresponding Dir.

Any Ideas on ho I can do this ?

Thanks ( as always )

 

[Bong]

something like month(now) will return an integer (1-12) representing the current month. Then you could have a look-up table, or a collection, or an array with the directory names corresponding to each month.

_________________
Bob Rashkin

 

[SkipVought]

Hi,

It happens at my company, a fairly large aerospace manufacturer...

People save files in a form like...
[tt]
FileRootName_08-29-07
[/tt]
It really chaps me, when I'm looking for a file with a particular root, saved on a particular date, that the NAME SORT produces an OUT-OF-SEQUENCE result.

Why not save the file like...
[tt]
FileRootName_2007-08-29
[/tt]
and make it LOGICAL and SIMPLE for all of us???

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[WinblowsME]

Based on Bong's suggestion

Sub Print_Dir()
   Debug.Print Get_Dir_Name
End Sub

Private Function Get_Dir_Name() As String
   Dim months As Variant
   
   months = Array("", "January", "February", "March", _
                      "April", "May", "June", _
                      "July", "August", "September", _
                      "October", "November", "December")

   Get_Dir_Name = Format(Now, "yy-mm") & " " & months(Month(Now)) & " Stickers"
End Function

 

[WinblowsME]

Sub Print_Dir()
   Debug.Print Format(Now, "yy-mm mmmm") & " Stickers"
End Sub