VBA Saving File with Week Number

[Littlemalky]

I need to Save As with this file name exactly:

"BO Priority April Week 3.xlsx"

The month part is easy, i just declare it as Format(Date, "mmmm"); however, the "Week 3" part is tricky because it is number by the amount of week's in a month, not the week it would be in a year (which seems to be the only functions I could find).

Does anyone know how to declare this so that when I save each week during the month, it will be accurate?

 

[Glory]

Yeah; use one variable for each month (a01-a12), assign the number of days to each variable (a01 = 31, If Not LeapYear = True Then a02 = 28, etc). A third variable should equal Day(Date), and you can then use Month(Date) to figure out which "aXX" variable's scopre your current days falls under. Next you want to add all the values of each aXX that falls before the one you're now working with. Last, you want to subtract the value of your current Day(Date) from your contemporaneous aXX.

 

[Warship]

Have a peek at:
http://www.cpearson.com/Excel/WeekNumbers.aspx

 

[Littlemalky]

Yeah Warship, non of that seems applicable for which week it is within the month.

Glory, I apologize, I'm extremely new to VBA and that seems really complicated for me to figure out on my own.

I found this function on this website, but I'm not sure how to use that in my filename:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1085

 

[Glory]

Workbooks.SaveAs (yourPath & SailWeek(yourDate As Date) & ".xls")

This would create the filename "Week X.xls" at yourPath.

 

[Littlemalky]

THAT WORKED!!!

Now here's another problem i just realized. This function is awesome; however, I need the first Monday of the month to be the first week. For example, April 1st is on a Friday in this month. However, every Friday for the following week, and we consider that Monday through friday Week 1, so it would be the 4th through the 8th. This **** is getting technical.

 

[Littlemalky]

So this is what I have thus far, but I just need to adjust it in some way to factor in the first Monday of the month as Week 1:

Private Sub SaveAs()
    Workbooks.Open Filename:="C:\...\BO Priority.xlsx"
    ActiveWorkbook.SaveAs "C:...\BO Priority " & Format(Date, "mmmm") & " " & SailWeek(Date) & ".xlsx"
End Sub
Function SailWeek(dDate1 As Date) As String
    Dim dDate2 As String
    Dim wWeek As Integer
     
     'dDate2 is changed to date from String
     
    dDate2 = VBA.CDate(Month(dDate1) & "/01/" & Year(dDate1))
    wWeek = DateDiff("ww", dDate2, dDate1, vbMonday, vbUseSystem) + 1
     
     'Return the Week number of the month
     'Monday is taken as week starting date, you can change
     'it to your desired day as starting date of week
     
    SailWeek = "Week " & wWeek
End Function

 

[JoeMo]

So this is what I have thus far, but I just need to adjust it in some way to factor in the first Monday of the month as Week 1:

Private Sub SaveAs()
    Workbooks.Open Filename:="C:\...\BO Priority.xlsx"
    ActiveWorkbook.SaveAs "C:...\BO Priority " & Format(Date, "mmmm") & " " & SailWeek(Date) & ".xlsx"
End Sub
Function SailWeek(dDate1 As Date) As String
    Dim dDate2 As String
    Dim wWeek As Integer
     
     'dDate2 is changed to date from String
     
    dDate2 = VBA.CDate(Month(dDate1) & "/01/" & Year(dDate1))
    wWeek = DateDiff("ww", dDate2, dDate1, vbMonday, vbUseSystem) + 1
     
     'Return the Week number of the month
     'Monday is taken as week starting date, you can change
     'it to your desired day as starting date of week
     
    SailWeek = "Week " & wWeek
End Function

The function appears to assign week 1 to the week in which the first day of the month falls. Do you want week 1 to be the week in which the first Monday of the month occurs?

 

[Littlemalky]

Yes, that's what I want.