Name file depending on the week's number

**ced0802** · 12-05-2015, 12:35 PM

Hello everyone,

I have this macro hereunder with a workbook with the number of the current week in it ( name "wk49production" for the week 49)
But of course I wouldn't like to change the number of the week every week. Is there a way for excel to change automatically the workbook name depending on the current week?

Please Login or Register  to view this content.

Thank you very much in advance.

**PCI** · 12-05-2015, 12:44 PM

If the file to use is always present you could use

Please Login or Register  to view this content.

**joe31623** · 12-05-2015, 01:08 PM

I recently did something similar.

Please Login or Register  to view this content.

...I didn't compile it and there may be a better way to do it, but let me know if you have any issues

**joe31623** · 12-05-2015, 01:15 PM

...looks like someone else came up w/ a better solution, but if the above implementation is useful (now that I see you want week number over the year), I would loop from 1/1/YYYY to now() and increment WeekNumber using DateDiff("d", #1/1/YYYY#, Now()) mod 7 function as the limit to the For loop.

**ced0802** · 12-06-2015, 10:16 AM

Thank you very much to all of you for your help.

I tried this one from PCI, but I don't get why I get an error message (on the set db line).

Please Login or Register  to view this content.

**PCI** · 12-06-2015, 10:37 AM

Does the file named WkStg & "production" exist ???
You could use next code and see how variable WkStg show the full file name

Please Login or Register  to view this content.

**ced0802** · 12-06-2015, 01:43 PM

My file is named "Wk49Production"...as we are this week, week 49, I assume it should be working so don't see why not. Message error saying it is not part of the selection

**PCI** · 12-06-2015, 03:23 PM

Houps ....!
Try next code

Please Login or Register  to view this content.

Check if WkStg has the right value = filename with extension

**ced0802** · 12-06-2015, 04:33 PM

Thank you so much for your help PCI. I have just checked. And indeed the error comes from the fact that Wkstg gets the value 50..instead of 49 (as current week is 49).
Any idea why?

**protonLeah** · 12-06-2015, 04:55 PM

WEEKNUM(NOW(),2)
2--> week begins on Monday

**PCI** · 12-06-2015, 05:02 PM

Ah la la...!
The week number is a curious story:
How do you know that the week number is 50 ?
Are you using the ISO week number or other one ?
Just to give some hits:
The first week of the year is the one with the more days of the new year in the week: 4 days
The number of days in the week is counted from the first to the last day of the week
What is the first day of the week: Sunday or Monday ?
See next code

Please Login or Register  to view this content.

**ced0802** · 12-06-2015, 05:23 PM

Thank you so much. Learning a lot from you, thanks !

**ced0802** · 12-06-2015, 07:12 PM

Quick question:

I'd like the name to look like "Wk 49 Production schedule 151206".
With the code hereunder I have "Wk 49 Production schedule 15126".

How can I deal with the "0"?

Please Login or Register  to view this content.

**ced0802** · 12-06-2015, 07:32 PM

Sorry I found it

I post it if it can help someone someday :

Please Login or Register  to view this content.

**xladept** · 12-06-2015, 07:37 PM

Maybe:

Please Login or Register  to view this content.

**kundepuu** · 12-06-2015, 08:55 PM

here is function for ISO weeks

Please Login or Register  to view this content.

Name file depending on the week's number

LinkBack

Thread Tools

Rate This Thread

Display

Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Re: Name file depending on the week's number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looking for cell value (time spent in a certain week number) based on name and week number

Returning the week number for a week that starts on Saturday in the Australian Calender

Add number from one cell to a running total cell depending on the day of the week

Macro to save a file with a pre file name + week number from a cell

Paste to Specific Sheet Depending Upon Week Number

Working hours in one week depending week no

Changing cell or text color depending on week number

Bookmarks

Bookmarks

Posting Permissions