flashercs

回复 4# toyerspollors

<#*,:&cls
@echo off
cd /d "%~dp0"
powershell -NoProfile -ExecutionPolicy RemoteSigned -Command ". ([ScriptBlock]::Create((Get-Content -LiteralPath \"%~0\" -ReadCount 0 | Out-String ))) "
pause
exit /b
#>
# 功能: 处理当前目录的xls文件

$r = '6:6' # 要插入的行
$c = 'G:G' # 要插入的列
$d = 'F5'  # 更改单元格
$t = '批处理之家' # 更改的内容

$xlShiftDown = -4121
$xlShiftToRight = -4161
$xlFormatFromLeftOrAbove = 0
$xlFormatFromRightOrBelow = 1

$excel = New-Object -ComObject excel.application
$excel.Visible = $true #显示excel窗口
$excel.DisplayAlerts = $false
Get-ChildItem .\*.xls, .\*.xlsx | ForEach-Object {
  $workbook = $excel.workbooks.open($_.fullname)
  $worksheet = $workbook.sheets.item(1) # 操作第一表格 Sheet1
  $worksheet.range($c).insert($xlShiftToRight, $xlFormatFromLeftOrAbove) #插入列
  $worksheet.range($r).insert($xlShiftDown, $xlFormatFromLeftOrAbove) #插入行
  $range = $worksheet.Range($d)
  $range.Value() = $t #更改内容
  $range.interior.colorindex = 6 #单元格背景色
  $range.font.colorindex = 3 #单元格字体色
  $range.font.bold = $true #加粗
  [void]$workbook.save() #保存xls
  $workbook.close() 
}
$excel.Quit()
复制代码

保存为 a.bat

flashercs

回复 6# toyerspollors

$worksheet.Columns("B").Delete()
$worksheet.Rows(2).Delete()
$worksheet.Range("G7").ClearContents()
复制代码

去微软官方网站 学习vba