How to activate excel sheet in workbook by date-CodePudding

I have created a workbook in excel for meeting notes for work. There are 52 sheets which are named by specific dates in the format "10.01.2023". I want to try set it in a way that when someone opens the workbook on a date of a meeting, it will automatically open on the tab that is named with that date. I'm not sure if this can be done, but I would really appreciate any help from people who have ideas of how to work this out.

I have tried many other tags and VB codes but none give me the result I'm hoping for. For example, if I open the workbook on 27.11.2023, it will open on the tab that is named the same. Then next time I open the workbook on 04.12.2023 it will open the tab named the same (and so on).

CodePudding user response：

Put this code in a normal module:

Public Sub activateTodaysSheet()

Dim dateMonday As Date
dateMonday = getMondayForDate(Now)

Dim strDate As String
strDate = Format(dateMonday, "dd.mm.yyyy")

On Error Resume Next    'if sheet does not exist
ThisWorkbook.Worksheets(strDate).Activate
If Err <> 0 Then
    MsgBox "No worksheet found for " & strDate, vbInformation
End If
On Error GoTo 0

End Sub


Public Function getMondayForDate(d As Date) As Date
getMondayForDate = Date - Weekday(Date, vbMonday)   1
End Function

And this code into the ThisWorkbook- module:

Private Sub Workbook_Open()
activateTodaysSheet
End Sub

If the workbook is opened Monday for current date is caculated. if there is a sheet for that date it gets selected - if not user is shown a message bos

CodePudding user response：

If you have python, below code should work, but this would mean running the script everyday, probably there is a way to run the script evryday before you use excel.

from datetime import datetime
from openpyxl import load_workbook

wb = load_workbook(r"C:\Users\your_user\Desktop\your_workbook.xlsx") #


now = datetime.now().strftime("%d.%m.%Y") #gives current date in string format like 10.01.2023
  

wb.active = wb[now]  #sets the active sheet as the current date sheetname

wb.save(r"C:\Users\grkel\Desktop\your_workbook.xlsx")