VBA在封闭的Excel中添加新工作表而不打开并获取工作表名称？

3 回答

• 9

  要获取工作表名称，请使用 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
  

  回复于 2024-04-20T22:47:06+08:00
• 3

  像这样的东西会做你想要的 . 没有打开工作簿是不可能的，但是如果你关闭屏幕更新它会给出不打开的外观 .

  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
  

  回复于 2024-04-20T22:47:06+08:00
• 5

  可以使用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
  

  这将向新工作表的单元格A1添加 Headers “ID” . 如有必要，您可以找到删除/更改它的方法 .

  回复于 2024-04-20T22:47:06+08:00