excel - 使用批处理文件重命名和格式化 Excel 工作表

Question

是否可以创建 批处理文件那将执行以下操作？

重命名 Excel 文件中的单个工作表(不是 Excel 工作簿/文件本身)

将简单格式应用于 Excel 文件 - 例如，将字体和字体大小应用于整个工作簿。

谢谢

Answer 1

创建两个文件:一个批处理文件和一个由批处理文件调用的 VBScript 文件。在 VBS 中操作 Excel 的工作很容易。

modder.bat

:Start
   @Echo off
   CScript modder.vbs
:End

修改器.vbs

'launch Excel and open file
Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("c:\temp\filename.xls")
'turn off screen alerts
xlObj.Application.DisplayAlerts = False
'loop through sheets
For Each Worksheet In xlFile.Worksheets 
   'change sheet to desired worksheet name
   If Worksheet.Name = "SheetToRename" Then
      Worksheet.Name = "NewName"
   End If
   'change all sheets to desired font 
   With Worksheet.Cells.Font 
       .Name = "Verdana" 
       .Size = 12
   End With 
Next  
'save, close, then quit
xlFile.Close True
xlObj.Quit

修改器.vbs (根据要求使用 workday() 函数)

'launch Excel and open file
Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("c:\temp\filename.xls")
'turn off screen alerts
xlObj.Application.DisplayAlerts = False
'loop through sheets
For Each Worksheet In xlFile.Worksheets 
   'change sheet to desired worksheet name
   If Worksheet.Name = "SheetToRename" Then
      'get prior workday
      dPriorWorkday = xlObj.Application.WorksheetFunction.WorkDay(Now, -1)
      'format as necessary (I used ISO 8061)
      Worksheet.Name = Year(dPriorWorkday) & "-" & Right("0" & Month(dPriorWorkday),2) & "-" & Right("0" & Day(dPriorWorkday),2)
   End If
   'change all sheets to desired font 
   With Worksheet.Cells.Font 
       .Name = "Verdana" 
       .Size = 12
   End With 
Next  
'save, close, then quit
xlFile.Close True
xlObj.Quit

修改部分工作表名称

   If Left(Worksheet.Name,12) = "Target Sheet" Then
      'get prior workday
      dPriorWorkday = xlObj.Application.WorksheetFunction.WorkDay(Now, -1)
      'format as necessary (I used ISO 8061)
      Worksheet.Name = "Target Sheet " & Year(dPriorWorkday) & "-" & Right("0" & Month(dPriorWorkday),2) & "-" & Right("0" & Day(dPriorWorkday),2)
   End If