|
本帖最后由 327718098 于 2017-5-12 19:09 编辑
在工作簿所在的路径新建一个文件夹修改用来保存修改好的数据,应为工作簿正在运行,无法直接更改名称,只能另存,还是一样把源数据复制一份来测试,基本上没什么问题,ActiveWorkbook.SaveAs Filename:=lj1 & "\" & "新建文件夹" & "\" & "综合素质评价报告单" & rng.Offset(0, 2) & "班(" & rng.Offset(0, 1) & "_" & 这一句前面的部分就是指定保存路径
Sub text()
Application.ScreenUpdating = False
Dim rng As Range, wr As Worksheet, lj1 As String, lj2 As String, wb As String, wb1 As String
lj1 = ThisWorkbook.Path & "\"
lj2 = Dir(lj1 & "*.xl*")
Do While lj2 <> ""
If lj2 <> ThisWorkbook.Name Then
Workbooks.Open (lj1 & lj2)
wb1 = Mid(ActiveWorkbook.Worksheets("学生登记表").Range("a2"), 29, 15)
Set rng = Workbooks("学生信息表(含学籍号、班级).xlsx").Sheets("sheet1").Range("a:a").Find(ActiveWorkbook.Sheets("学生登记表").Range("f5"))
For Each wr In ActiveWorkbook.Sheets
If wr.Name = "学生登记表" Then
wr.Cells(2, 1) = "××市教育局 ××区教育局 ××省××中学 " & wb1 & rng.Offset(0, 2) & "班"
ElseIf wr.Name = "综合素质-终结性评价" Then
wr.Cells(2, 1) = "××省××中学" & wn1 & rng.Offset(0, 2) & "班"
ElseIf wr.Name = "体检表" Then
wr.Cells(2, 1) = "××省××中学" & wb1 & rng.Offset(0, 2) & "班"
Else
wb = Right(wr.Cells(3, 1).Value, 12)
wr.Cells(3, 1) = "××省××中学" & wb1 & rng.Offset(0, 2) & "班 学生姓名:" & rng.Offset(0, 1) & "学籍号:" & rng & "考籍号:" & wb
End If
Next
ActiveWorkbook.SaveAs Filename:=lj1 & "\" & "新建文件夹" & "\" & "综合素质评价报告单" & rng.Offset(0, 2) & "班(" & rng.Offset(0, 1) & "_" & rng.Value & ".xls"
ActiveWorkbook.Close saverange = True
End If
lj2 = Dir
Loop
Application.ScreenUpdating = True
End Sub
|
|