使用当前日期、月份和年份自动生成工作簿

Question

我是MS EXCEL VBA的新手，但我仍然阅读了一些关于VBA及其工作原理的博客。我想到了重命名 excel 工作表、禁用行和添加新工作表的想法。但是我想知道如何每天
自动插入行，每月添加新工作表，并每年在MS EXCEL 2013中添加新的相同(默认结构(工作簿？我该怎么做？

今天的日期是1，然后明天Excel会自动为日期添加1行。如果一月份结束，则excel会自动为2月份添加新工作表。它 年份更改 excel 生成与当前年份相同的默认或空工作簿。

excel

的默认结构，excel文件位于 https://github.com/Ailyn09/project102/blob/master/2017.xlsx

当前代码

Sub Sample()
'Disable adding row
Dim I As Integer
Dim cbStr As String
Dim cbCtrl As CommandBarControl
Application.ScreenUpdating = False
For I = 1 To 2
If I = 1 Then
cbStr = "row"
Else
cbStr = "column"
End If
For Each cbCtrl In Application.CommandBars(cbStr).Controls
If cbCtrl.ID = 3183 Then
cbCtrl.Enabled = False
End If
Next
Next
Application.ScreenUpdating = True
'Copy Existing Sheet And Month As Name 
ActiveSheet.Copy , Sheets(Sheets.Count)
ActiveSheet.Name = Month() 
End Sub

每个月在这里都有一个工作表的想法： https://excel.tips.net/T002017_Sheets_for_Months.html

Answer 1

这是一个开始...只是尝试一些代码...如果不存在，则创建月份工作表...然后将数据添加到工作表

我必须离开几天...当我回来时会想出更多的东西

Sub testDate()
Debug.Print Format(Now(), "d")
Debug.Print Format(Now(), "dd")
Debug.Print Format(Now(), "ddd")
Debug.Print Format(Now(), "m")
Debug.Print Format(Now(), "mm")
Debug.Print Format(Now(), "mmm")
Debug.Print Format(Now(), "mmmm")
Debug.Print Format(Now(), "yy")
Debug.Print Format(Now(), "yyyy")
Debug.Print Month(Now)
Debug.Print MonthName(Month(Now))
Dim ws As Sheets
Set ws = ActiveWorkbook.Worksheets
Dim nam As String
nam = Format(Now(), "mmmm")                     ' month name in local language
Dim sh As Worksheet
If Evaluate("ISREF('" & nam & "'!A1)") Then     ' sheet name exists ?
Set sh = ws(nam)
Else
Set sh = ws.Add(after:=ws(ws.Count))
sh.Name = nam
End If
' !!!!! use one of the sections below, but NOT both !!!!!
' --------------------------------------------------------------------------------------
Dim lastCell As Range
Set lastCell = sh.Range("A" & sh.Rows.Count).End(xlUp)                   ' last used cell in column A
lastCell.Offset(1).Value = Format(Now(), "today is the dd of mmmm")  ' some of the characters must be escaped
' --------------------------------------------------------------------------------------
'   this section corrupts the lastCell value that is used above
'   if fixed number of rows per day, then put daily data in particular rows
'   Dim day As Integer
'   day = Format(Now(), "d")
'   sh.Range("A1").Offset(day).Value = Format(Now(), "today is the dd of mmmm")
' --------------------------------------------------------------------------------------
End Sub