如何使用VBS从命令提示符格式化现有的XLSX文件

Question

我正在尝试使用命令提示符将现有的xlsx文件格式化为包含自动调整列大小和交替行颜色的表。我知道这是可以做到的，但我丢失了我拥有的.vbs脚本。我能找到的只是这样的脚本，它们不引用现有的xlsx，而是在创建它们。如有任何帮助，我们将不胜感激。

'Bind to the Excel object
Set objExcel = CreateObject("Excel.Application")

'Create a new workbook.
objExcel.Workbooks.Add

'Select the first sheet
Sheet = 1

'Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)

'Name the worksheet
objSheet.Name = "VBS_Excel_Example"

'Set the save location
strExcelPath = "d:Vbs_Excel_Example.xlsx"

'--------------------------------------------------------
'Populate the worksheet with data
'--------------------------------------------------------
'   objSheet.Cells(row, column).Value = "Whatever"

'Add some titles to row 1
objSheet.Cells(1, 1).Value = "Name" 'Row 1 Column 1 (A)
objSheet.Cells(1, 2).Value = "Description" 'Row 1 Column 2 (B)
objSheet.Cells(1, 3).Value = "Something Else" 'Row 1 Column 3 (C)

'Add some data using a loop
For row = 2 to 10
objSheet.Cells(row, 1).Value = "Item Name"
objSheet.Cells(row, 2).Value = "Item Description"
objSheet.Cells(row, 3).Value = "Item Something Else"
Next

'--------------------------------------------------------
' Format the spreadsheet
'--------------------------------------------------------
'Put the first row in bold
objSheet.Range("A1:C1").Font.Bold = True

'Change the font size of the first row to 14
objSheet.Range("A1:C1").Font.Size = 14

'Freeze the panes
objSheet.Range("A2").Select
objExcel.ActiveWindow.FreezePanes = True

'Change column A and B to use a fixed width
objExcel.Columns(1).ColumnWidth = 20
objExcel.Columns(2).ColumnWidth = 30

'Change column C to autofit
objExcel.Columns(3).AutoFit()

'Change the background colour of column A to a light yellow
objExcel.Columns(1).Interior.ColorIndex = 36

'Change the font colour of column C to blue
objExcel.Columns(3).Font.ColorIndex = 5

'--------------------------------------------------------
' Save the spreadsheet and close the workbook
'--------------------------------------------------------
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

'Quit Excel
objExcel.Application.Quit

'Clean Up
Set objSheet = Nothing
Set objExcel = Nothing

Answer 1

绑定对象后，需要加载现有文件，如下所示：

'Bind to the Excel object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("d:Vbs_Excel_Example.xlsx")

'Select the first sheet
Sheet = 1

'Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)
objSheet.Cells(row, column).Value = "Whatever"

然后做剩下的…

我不确定你被困在哪一部分，告诉我是否需要更多的调整