|
- PATHM = ThisWorkbook.Path & "\简历模板.XLS" '//外部模板
- Set SHX = Worksheets("员工资料")
- PathG = ThisWorkbook.Path & "\员工简历" '//结果文件夹
- Set FSO = CreateObject("Scripting.FileSystemObject")
- If FSO.FolderExists(PathG) = True Then
- FSO.GetFolder(PathG).Delete '//删除文件夹
- End If
- MkDir PathG '//创建文件夹
-
- Str_coon = "HDR=yes';Data Source =" & ThisWorkbook.FullName '//OFFICE2003,2007 通用
-
- Rem 模板中放置数据的单元格位置,和查询标题对应
- StrWZ = ""
- StrWZ = StrWZ & "B2,D2,F2,B3,D3,F3,B4,D4,F4"
- StrWZ = StrWZ & ",B5,F5,B6,F6,B7"
- BRX = Split(StrWZ, ",")
- StrBT = ""
- StrBT = StrBT & "[姓名],[年龄],[性别],[籍贯],[民族],[学历],[健康],[身高],[体重]"
- StrBT = StrBT & ",[身份证号码],[政治面貌],[联系方式],[婚姻状况],[现居住地]"
-
-
- Rem 先获取要拆分字段的不重复值
- StrSQL = "SELECT DISTINCT [身份证号码]"
- StrSQL = StrSQL & " FROM [" & SHX.Name & "$A1:IT]"
- StrSQL = StrSQL & " WHERE NOT [身份证号码] IS NULL AND LEN([身份证号码])>0"
- ARX = GET_SQL_To_Arr(StrSQL, Str_coon, False) '//不重复姓名放入二维数组
-
- If ARX(0, 0) <> "" And ARX(0, 0) <> "Error" Then
- ICINT = UBound(ARX) + 1
-
- For X = 0 To ICINT - 1 '//循环每一个值
- Rem 提示信息,在状态栏显示
- Application.StatusBar = "需拆分总数:" & ICINT & " 个,当前是第:" & X + 1 & " 个,当前身份证号码是:" & ARX(X, 0)
- DoEvents
-
- Rem 查询对应数据
- StrSQL = ""
- StrSQL = StrSQL & "SELECT TOP 1 " & StrBT
- StrSQL = StrSQL & " FROM [" & SHX.Name & "$A1:IT]"
- StrSQL = StrSQL & " WHERE [身份证号码]='" & ARX(X, 0) & "'"
- SQLARR = GET_SQL_To_Arr(StrSQL, Str_coon, False)
-
- If SQLARR(0, 0) <> "" And SQLARR(0, 0) <> "Error" Then '//没有数据,不保存
-
- Set WB = Workbooks.Open(PATHM)
- Set SHW = WB.Worksheets(1)
- SHW.Name = SQLARR(0, 0) & "_" & ARX(X, 0)
-
- Rem =============================================================按对应位置放置,数组和标题对应
- For ICOL = 0 To UBound(BRX)
- SHW.Range("" & BRX(ICOL)).Value = SQLARR(0, ICOL)
- Next
-
- WB.SaveAs Filename:=PathG & "" & SQLARR(0, 0) & "_" & ARX(X, 0) & ".XLS", FileFormat:=xlExcel8
- WB.Close True
- End If
- Next
- Else
- MsgBox "未发现拆分依据 需要的值!", vbInformation, "北极狐提示!!"
- End If
复制代码 |
|