|
注释中都有说明, 有几个参数可以自行调整
1, 原Excel表格中做了一个名为code的工作表, 存放代码1,2
2, xlsm源文件放在与分发库同级目录下
- Sub moveFile()
- '思路分析
- '原Excel表格中做了一个名为code的工作表, 存放代码1,2
- '假设所有需要分发的文件都已存放在 .\分发库 先手工保证文件名的有效性
- '只需要取文件名的前5位作为药名, 文件名的后4位的第1位是部门代码
- '根据情况来决定是否创建文件夹以及移动文件
- Dim shtCode As Worksheet
- Dim intRow1, intRow2, intIsMove As Integer
- Dim i, j, k, m, n As Integer
- Dim arrCode(), arrDept(), arrFile()
- Dim strName, strDept, strDeptPref, strDesDir, strSrcDir, strFile, strSrc, strDes As String
- Dim intName, intDept As Integer
- strDesDir = ThisWorkbook.Path
- strSrcDir = ThisWorkbook.Path & "\分发库"
- intIsMove = 1 '1为移动, 0为 复制
- '将代码1和代码2装入字典
- Set shtCode = Sheets("code")
- With shtCode
- intRow1 = Cells(.Cells.Rows.Count, 1).End(xlUp).Row
- intRow2 = Cells(.Cells.Rows.Count, 3).End(xlUp).Row
-
- arrCode = Range(.Cells(2, 1), .Cells(intRow1, 2))
- arrDept = Range(.Cells(2, 3), .Cells(intRow2, 4))
-
- Set dicCode = CreateObject("scripting.dictionary")
- Set dicDept = CreateObject("scripting.dictionary")
-
- For i = 1 To UBound(arrCode)
- If Not dicCode.exists(arrCode(i, 1)) Then
- dicCode(arrCode(i, 1)) = arrCode(i, 2)
- Else
- MsgBox "代码1名称有重复, 请检查后重试"
- GoTo 100
- End If
-
- Next
-
- For i = 1 To UBound(arrDept)
- If Not dicDept.exists(arrCode(i, 1)) Then
- dicDept(arrDept(i, 1)) = arrDept(i, 2)
- Else
- MsgBox "代码2名称有重复, 请检查后重试"
- GoTo 100
- End If
-
- Next
- End With
- '获取分发库的文件名称
- j = 0
- strFile = Dir(strSrcDir & "\*.*")
- Do While strFile <> ""
- j = j + 1
- ReDim Preserve arrFile(1 To j)
- arrFile(j) = strFile
- strFile = Dir
- Loop
- '分发文件
- For k = 1 To UBound(arrFile)
- '获取文件名称 药名 部门
- strFile = Trim(arrFile(k))
- m = InStr(1, strFile, ".")
- If m > 0 Then
- strFile = Trim(Left(strFile, m - 1))
- End If
- strName = dicCode(Left(strFile, 5))
- strDept = dicDept(Left(Right(strFile, 4), 1))
-
- If strName = "" Or strDept = "" Then
- MsgBox "无法匹配的文件:" & arrFile(k)
- GoTo 200
- End If
-
- '创建目录
- If Dir(strDesDir & "" & strDept, vbDirectory) = "" Then
- MkDir strDesDir & "" & strDept
- End If
-
- If Dir(strDesDir & "" & strDept & "" & strName, vbDirectory) = "" Then
- MkDir strDesDir & "" & strDept & "" & strName
- End If
-
- '移动文件
-
- strSrc = strSrcDir & "" & arrFile(k)
- strDes = strDesDir & "" & strDept & "" & strName & ""
-
-
- If Dir(strDes & "" & arrFile(k)) = "" Then
-
- Set fso = CreateObject("scripting.filesystemobject")
- If intIsMove = 1 Then
- fso.moveFile strSrc, strDes
- Else
- fso.copyFile strSrc, strDes
- End If
- End If
-
- 200
- Next
- MsgBox "已完成全部文件分发"
- 100
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|