文章背景:想要通过VBA打开一份带密码的Excel文件,然后在文件内填入信息。前述要求可以借助workbook.open
来实现。
功能:Opens an existing workbook and adds it to the Workbooks collection . Returns a reference to the workbook that was opened.
语法:workbooks
.Open(Filename
, [UpdateLinks
], [ReadOnly
], [Format
], [Password
], [WriteResPassword
], [IgnoreReadOnlyRecommended
], [Origin
], [Delimiter
], [Editable
], [Notify
], [Converter
], [AddToMru
], [Local
], [CorruptLoad
])
通过VBA实现的功能:打开带密码的Excel文件,并在文件内填入信息。
Option Explicit
Sub test()
'打开带密码的excel文件
Dim xlapp1 As Excel.Application
Dim xlbook1 As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet
Dim path As String
Dim row_final As String
path = "E:\工作\报告展示\测试文件_密码123.xlsm"
If fileExist(path) Then
Set xlapp1 = CreateObject("Excel.Application")
Set xlbook1 = xlapp1.Workbooks.Open(path, 0, False, 5, "123", "123")
Set xlsheet1 = xlbook1.Worksheets(1)
row_final = xlsheet1.Range("A65535").End(xlUp).Row
xlsheet1.Cells(row_final + 1, 1) = "2021/11/6" '日期
xlbook1.Close savechanges:=True
xlapp1.Quit '关闭测试数据工作簿
Set xlapp1 = Nothing
MsgBox "Done!"
Else
MsgBox "文件路径不存在:" & path & vbCrLf & vbCrLf & "请确认!"
End If
End Sub
Function fileExist(path As String) As Boolean
'判断指定路径的文件是否存在
Dim sName As String
sName = Dir(path)
If Len(sName) Then
fileExist = True
Else
fileExist = False
End If
End Function
视频演示:http://mpvideo.qpic.cn/0bc3xqabeaaammalfdugcvqvbpgdck6aaeqa.f10002.mp4?dis_k=91444e260752b69988a72c74a76ae04e&dis_t=1663656065&vid=wxv_2124342637947158530&format_id=10002&support_redirect=0&mmversion=false
参考资料:
[1] Workbooks.Open(https://www.oreilly.com/library/view/programming-excel-with/0596007663/re315.html)
[2] EXCEL-VBA:Workbooks.Open 参数 (打开文件)(https://blog.csdn.net/jyh_jack/article/details/83820068)
[3] VBA 打开带密码的文件(https://blog.csdn.net/weixin_33709590/article/details/94254831?spm=1001.2101.3001.6650.10&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-10.highlightwordscore&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-10.highlightwordscore)
扫码关注腾讯云开发者
领取腾讯云代金券
Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号:粤B2-20090059 深公网安备号 44030502008569
腾讯云计算(北京)有限责任公司 京ICP证150476号 | 京ICP备11018762号 | 京公网安备号11010802020287
Copyright © 2013 - 2025 Tencent Cloud.
All Rights Reserved. 腾讯云 版权所有