Option Explicit
Option Base 1
Sub test()
'打开工作簿
Dim wb As Workbook, pathname As String
pathname = "D:\OneDrive\文档\test.xlsm"
Set wb = Workbooks.Open(pathname)
wb.Sheets(1).Range("A1") = "Hello world!"
wb.Close SaveChanges:=True
MsgBox "Done!"
End Sub
打开一份Excel文件,编辑结束后,保存内容并关闭。
2 GetObject
返回对 ActiveX 组件提供的对象的引用。
GetObject ([ pathname ], [ class ])
代码示例:
Sub test2()
'使用GetObject函数打开工作簿
Dim wb As Workbook, pathname As String, content As String
pathname = "D:\OneDrive\文档\test.xlsm"
On Error Resume Next
Set wb = GetObject(pathname)
On Error GoTo 0
If wb Is Nothing Then
MsgBox "File not found or error occurred."
Exit Sub
End If
' 获取内容
content = wb.Sheets(1).Range("A1").Value2
MsgBox content
wb.Close SaveChanges:=False
MsgBox "Done!"
End Sub
Sub test4()
'使用GetObject函数打开工作簿,修改内容,文件不会被隐藏
Dim wb As Workbook, pathname As String, content As String
pathname = "D:\OneDrive\文档\test.xlsm"
Set wb = GetObject(pathname)
wb.Sheets(1).Range("A2").Value2 = "No 2"
Application.Windows(wb.Name).Visible = True
wb.Close SaveChanges:=True
MsgBox "Done!"
End Sub