VBA 在不打开的情况下在关闭的 Excel 中添加新工作表并添加工作表名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11469215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to add new sheet in a closed excel without opening & get the sheet name added?
提问by logan
I have an excel "Closed.Xls" which is not opened currently.
我有一个当前未打开的 excel“Closed.Xls”。
Please let me know how to add new sheets in to this closed excel file. I know how to add new sheets in current excel.
请让我知道如何将新工作表添加到这个关闭的 Excel 文件中。我知道如何在当前的 excel 中添加新工作表。
Dim WS as Worksheet
Set WS = Sheets.Add
Please let me know how to
请让我知道如何
- add new sheets in a closed excel using VBA, and
- get the name of the sheet added
- 使用 VBA 在关闭的 excel 中添加新工作表，以及
- 获取添加的工作表的名称
Note: I don't want to rename the sheet.
注意：我不想重命名工作表。
Thanks
谢谢
回答by Alistair Weir
Something like this will will do what you want. It is not possible without opening the workbook but if you turn off screen updating it gives the appearance of not being opened.
像这样的事情会做你想做的。不打开工作簿是不可能的，但是如果您关闭屏幕更新，它会显示未打开的外观。
Option Explicit
Sub Add_Sheet_ClosedBook()
Dim bk As Workbook
Dim sh As Worksheet
Dim shName As String
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Set bk = .Workbooks.Open _
("Path to Book.xls")
End With
With bk
Set sh = .Sheets.Add
shName = sh.Name
.Save
.Close
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
回答by brettdj
To get the sheet name use a worksheetvariable, ie
要获取工作表名称，请使用worksheet变量，即
Sub Added()
Dim Wb As Workbook
Dim ws As Worksheet
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
Set Wb = Workbooks.Open("c:\Temp\closed.xls")
Set ws = Wb.Sheets.Add
Debug.Print ws.Name
Wb.Save
Wb.Close
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
回答by Excel Developers
It is possible to add a sheet without opening the workbook by using the Microsoft.ACE.OLEDB.12.0 provider:
可以使用 Microsoft.ACE.OLEDB.12.0 提供程序在不打开工作簿的情况下添加工作表：
set cn = new adodb.connection
with cn
.provider = "Microsoft.ACE.OLEDB.12.0"
.connectionstring = "Data Source=" & strSomeFilename & ";Extended Properties=""Excel12.0;"""
.open
end with
set cmd = new adodb.command
cmd.activeconnection = cn
cmd.commandtext = "CREATE TABLE MySheet (ID char(255))"
cmd.execute
This will add a heading "ID" to cell A1 of the new sheet. You can probably find a way to delete/change it if necessary.
这将向新工作表的单元格 A1 添加标题“ID”。如有必要，您可能会找到一种删除/更改它的方法。
