调试经验——联合使用批处理、VBA完成文件的自动重命名(Renaming files with batch and VBA)

今天花了大半天时间完成了一个自动化业务：文件的自动重命名。

本程序启动前，Windows批处理将提取工作文件夹下的所有source文件，并将文件名保存在Source_File_List.xls中。
然后，Windows批处理程序将启动本程序所在的工作簿。启动后执行以下操作：
1. 将Source_File_List.xls中的文件名复制到A列
2. 将公式模板行中的公式自动填充到所有行
3. 筛选出需要重命名的行
4. 将筛选出的重命名语句写入一个rename.txt的临时文件中
5. 将该临时文件另存为带有时间戳的.bat文件
6. 将.bat文件移动到当前工作夹文件下，执行此批处理文件，完成自动重命名

 

技术要点：

1. 获取有效数据的行号

    '复制前清除Rename工作表中的数据（仅清除文本内容，不清除格式及行标题）
    lastRow = ThisWorkbook.Sheets("Rename").Range("A1048576").End(xlUp).Row() '获取最后一行行号
    ThisWorkbook.Sheets("Rename").Range("A3:E" & lastRow).ClearContents

2. 将模板公式（位于表头第二行）填充到所有数据行

'将公式从模板行（第二行）复制到第三行，然后自动填充到最后一行
    ThisWorkbook.Sheets("Rename").Range("B2:E2").Select
    Selection.Copy  '复制B2:E2单元格，即公式模板行
    ThisWorkbook.Sheets("Rename").Range("B3").Select    '将公式复制到B3单元格
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.AutoFill Destination:=Range("B3:E" & lastRow)

3. 筛选目标数据行，选择第一条可见行

'筛选出重命名语句
    ThisWorkbook.Sheets("Rename").UsedRange.AutoFilter Field:=5, Criteria1:="Y"     'field = 5即Renamed?列的列号

    '筛选得到的重命名语句第一条记录所在行号
    firstVisibleRow = ThisWorkbook.Sheets("Rename").Range("A3:A" & lastRow).SpecialCells(xlCellTypeVisible)(1).Row()

4. 将Excel中的内容写入txt文件

Dim c As Range, i&
    Open ThisWorkbook.Path & "\rename.txt" For Append As #1
    For Each c In ThisWorkbook.Sheets("Rename").Range("D" & firstVisibleRow & ":D" & lastRow).SpecialCells(xlCellTypeVisible) '只写入可见行（有效的重命名语句）
        Print #1, c.Value
    Next
    Close #1

5. 更改文件类型

txtFileName = ThisWorkbook.Path & "\rename.txt"
    batchFileName = "rename_" & Format(Now, "yyyy-mm-dd hh_mm_ss") & ".bat"
    Name txtFileName As ThisWorkbook.Path & "\" & batchFileName '更改文件类型

6. 移动文件

Set FSO = CreateObject("Scripting.Filesystemobject")    '创建文件系统对象
    backupBatchFileName = Left(ThisWorkbook.Sheets("Rename").Range("A2"), 60) & batchFileName  '当前工作文件夹的路径保存在Rename工作表的H单元格中

    FSO.MoveFile Source:=ThisWorkbook.Path & "\" & batchFileName, Destination:=backupBatchFileName   '移动文件到工作文件夹

7. VBA中执行批处理

Call Shell(backupBatchFileName)   '执行批处理（似乎必须在当前路径下执行重命名操作，因为使用完整文件名时出错：incorrect syntax）

一个小小的功能，使用的技术点还不少。这其中，好几个技术点对我而言是第一次应用，不错，VBA技术又提升了一步。