2021-09-18

最新推荐文章于 2024-10-13 14:15:57 发布

原创最新推荐文章于 2024-10-13 14:15:57 发布 · 75 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#vba #批量替换

VBA 同时被 2 个专栏收录

2 篇文章

订阅专栏

批量生成电子表格

1 篇文章

订阅专栏

VBA根据模板批量导出EXCEL文件

- 四步完成

四步完成

打开文件
另存为
写入
保存退出

Private Sub CommandButton2_Click()
  Dim mypath As String, svalue '定义变量
Dim num, m As Integer
m = 2

For i = 2 To 4392
  mypath = "C:\WK\Temp.xlsx" '& Sheet3.Cells(i, 1) '把路径赋值给一个字符串
  Workbooks.Open Filename:=mypath '这句是打开文件
  mypathB = "C:\WK\" & Sheet1.Cells(i, 5) & ".xlsx"
  ActiveWorkbook.SaveAs mypathB    '/false,这句是关闭文件，close有两个参数，true是关闭保存修改，false是关闭时不保存修改
  Workbooks.Open Filename:=mypathB '这句是打开文件
  'write
   k = 2
   For j = i To 4392
     If Sheet1.Cells(j, 5) = Sheet1.Cells(j + 1, 5) Then
        ActiveWorkbook.Sheets(1).Cells(k, 1) = Sheet1.Cells(j, 1)
        ActiveWorkbook.Sheets(1).Cells(k, 2) = Sheet1.Cells(j, 2)
        ActiveWorkbook.Sheets(1).Cells(k, 3) = Sheet1.Cells(j, 3)
        ActiveWorkbook.Sheets(1).Cells(k, 4) = Sheet1.Cells(j, 4)
        ActiveWorkbook.Sheets(1).Cells(k, 5) = Sheet1.Cells(j, 5)
     Else
        ActiveWorkbook.Close True
        i = j
        Exit For
     End If
     k = k + 1
   Next
  
  ' ActiveWorkbook.Close True    '/false,这句是关闭文件，close有两个参数，true是关闭保存修改，false是关闭时不保存修改
Next
End Sub