跳到主要内容

如何在Excel页眉或页脚中插入上次修改日期?

作者:晓阳 最后修改时间:2020-05-25

在Excel中,我们可以快速轻松地将工作簿信息(例如文件名,文件完整路径,当前日期等)插入页眉或页脚。 但是,您是否尝试过将工作表的最后修改日期和时间插入其中? Excel不提供此功能,我们无法在页眉或页脚中插入上次修改的日期,在这里,我将向您介绍一种方便的解决方法。

使用VBA代码在Excel页眉或页脚中插入上次修改日期

使用 Kutools for Excel 将上次修改日期/创建日期/用户插入页眉/页脚 好主意3


箭头蓝色右气泡 使用VBA代码在Excel页眉或页脚中插入上次修改日期

以下简单的VBA代码可以帮助您将工作表的上次保存的日期和时间插入页眉或页脚,请按以下步骤操作:

1。 激活您想要将上次修改日期插入页眉或页脚中的工作表。

2。 然后按住 ALT + F11 键打开 Microsoft Visual Basic for Applications窗口.

3。 在左边 VBA项目 窗格,双击 的ThisWorkbook 打开 模块,然后复制并粘贴以下代码。

VBA代码:在页眉或页脚中插入上次修改的日期

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.CenterHeader = "Last saved: " & Format(Date, "mm-dd-yy") & " " & Time
End Sub

doc-insert-last-saved-date-header-1

4。 然后保存并关闭此代码,然后单击 首页 > 页面布局 查看结果。 您会看到最后修改的日期和时间已插入到中心标题中。 查看屏幕截图:

doc-insert-last-saved-date-header-2
-1
doc-insert-last-saved-date-header-3

笔记:

1.当您修改并保存Excel文件时,上次修改的日期和时间会自动更新。

2.如果您需要将最后保存的日期和时间插入左侧的页眉/页脚,右侧页眉/页脚或中心页脚,则只需将上面代码中的CenterHeader更改为您的请求。


箭头蓝色右气泡 使用 Kutools for Excel 将上次修改日期/创建日期/用户插入页眉/页脚

使用 Kutools for Excel,您可以使用插入工作簿信息工具将工作簿信息快速插入到页眉、页脚或单元格中。

Kutools for Excel, 与超过 300 方便的功能,使您的工作更加轻松。 

安装后 Kutools for Excel,请执行以下操作:(立即免费下载 Kutools for Excel!)

点击 Kutools 加 > 工作簿 >插入工作簿信息,然后在弹出的对话框中,检查要插入的信息,然后转到 插入于 部分,检查 范围, 标题 or 页脚 选项,并指定在左侧页眉/页脚,中间页眉/页脚或右侧页眉/页脚处插入以满足您的需要。
doc插入工作簿信息
doc插入工作簿信息

箭头蓝色右气泡 插入工作簿信息


相关文章:

获取并在单元格中插入创建时间和上次修改时间

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Why do I have to load the VB code into 'ThisWorkBook' of the VBAproject for every new workbook created? I would think that if I loaded the VB code into 'ThisWorkBook' of VBAHOME (PERSONAL.XLSB) if would be retained and available cresting new workbooks
This comment was minimized by the moderator on the site
Just tried it after moving the center header to the left footer. Didn't work. What I saved at 1:20 pm still recorded as 1:00 pm. Your help would be most appreciated. BTW, my spreadsheet was created as Excel 2007, and I made changes at the library on Excel 2016. Generally, I can go back and forth between versions with no problems.
Also, I didn't realize my name would be published. Any chance of changing it to Nancy E.?
Many thanks!
This comment was minimized by the moderator on the site
THANK YOU! That was invaluable. Who'd believe that Microsoft would exclude such an important function! What about Word? Is it done the same way now?
This comment was minimized by the moderator on the site
Thanks for the Info.
This comment was minimized by the moderator on the site
perfect.very useful ;-)
This comment was minimized by the moderator on the site
I did this and it works. I saved the file as .xlsx and closed it. Now, when I open it, I open the VBA coding window but I am not able to find this chunk of code to edit it. I would like to play with location and remove time. Thank you.
This comment was minimized by the moderator on the site
@John R. When you get to the VBA coding window, on the left section of the screen you need to double-click "ThisWorkbook" to open the right section of the screen. Then in the right section (at the top) you need to select "Workbook" and "BeforeSave". That should hopefully show you the chunk of code that you're looking for.
This comment was minimized by the moderator on the site
I tried this technique but could only get it to show up on one sheet. Help?
This comment was minimized by the moderator on the site
Thanks! I maintain an employee phone number and extension list. People get hired, quit, move to different extensions. If someone wants a copy I print it off, but it only showed the date and time it was printed -- not necessarily the most current information. It was difficult to tell if you had two different copies which one was the most up-to-date. Showing the last time the file was saved will do what I wanted!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations