|
有一大批文件需要统一重新命名,就是在所有文件名称前面统一加上文件的信息例如:“创建日期,【2012年05月】文件名.xls"。
希望在一个Excel表格里面有两个按钮(【查找】、【修改】)
1、点击【查找】以后实现:
从A2开始,A列显示这个表格所在目录下(包括文件下里面的文件)所有文件的名称,从B2开始,B列显示文件的创建时间,从C2开始,显示文件的修改时间等文件信息求……
2、再点击【修改】
那么文件能修改为E列对应的新文件名称。(前提是从E2开始,E列有对应的名称)
已经有相关代码,求高手帮忙修改,感谢。
批改文件名的VB代码为:
- Dim fs, f, f1, fc, objxls
- Dim filename() As Variant
- Dim fullfilename() As Variant
- Dim newfullfilename() As Variant
- Dim namefilter() As Variant
- Dim i%, n%
- Sub getnames()
- Dim error
- On Error Resume Next
- Set objxls = CreateObject("Excel.application")
- Set fs = CreateObject("Scripting.FileSystemObject")
- Set f = fs.GetFolder(ActiveWorkbook.Path)
- Set fc = f.Files
- n = 0
- For Each f1 In fc
- ReDim Preserve filename(n)
- ReDim Preserve fullfilename(n)
- ReDim Preserve namefilter(n)
- fullfilename(n) = f1
- namefilter(n) = Right(f1, Len(f1) - InStrRev(f1, "."))
- filename(n) = Mid(f1, InStrRev(f1, "") + 1, Len(f1) - InStrRev(f1, "") - Len(namefilter(n)) - 1)
- n = n + 1
- Next
- Range("C2").Resize(n) = Application.Transpose(filename)
- Range("E2").Resize(n) = Application.Transpose(fullfilename)
- End Sub
- Sub changenames()
- Dim error
- On Error Resume Next
- n = Range("E65536").End(xlUp).Row - 1
- ReDim newfullfilename(n - 1)
- ReDim filename(n - 1)
- ReDim fullfilename(n - 1)
- For i = 0 To n - 1
- fullfilename(i) = Range("E" & 2 + i)
- filename(i) = Range("D" & 2 + i)
- If Range("D" & 2 + i) = "" Then GoTo 200
- newfullfilename(i) = ActiveWorkbook.Path & "" & filename(i) & "." & Right(fullfilename(i), Len(fullfilename(i)) - InStrRev(fullfilename(i), "."))
- Name fullfilename(i) As newfullfilename(i)
- 200
- Next
- 'Range("F2").Resize(n) = Application.Transpose(newfullfilename)
- End Sub
复制代码
查找文件创建日期的VB代码为:
- Option Explicit
- Sub 提取文件信息()
- Dim arr(1 To 10000) As String
- Dim f, i, k, f2, f3, x
- Dim arr1(1 To 100000, 1 To 6) As String, q As Integer
- Dim fso As Object, myfile As Object
- arr(1) = ThisWorkbook.Path & ""
- i = 1: k = 1
- Do While i < UBound(arr)
- If arr(i) = "" Then Exit Do
- f = Dir(arr(i), vbDirectory)
- Do
- If InStr(f, ".") = 0 And f <> "" Then
- k = k + 1
- arr(k) = arr(i) & f & ""
- End If
- f = Dir
- Loop Until f = ""
- i = i + 1
- Loop
- '*******下面是提取各个文件夹的文件***
- Set fso = CreateObject("Scripting.FileSystemObject")
- For x = 1 To UBound(arr)
- If arr(x) = "" Then Exit For
- f3 = Dir(arr(x) & "*.*")
- Do While f3 <> ""
- q = q + 1
- arr1(q, 6) = arr(x) & f3
- Set myfile = fso.GetFile(arr1(q, 6))
- arr1(q, 1) = f3
- arr1(q, 2) = myfile.Size
- arr1(q, 3) = myfile.DateCreated
- arr1(q, 4) = myfile.DateLastModified
- arr1(q, 5) = myfile.DateLastAccessed
- f3 = Dir
- Loop
- Next x
- Range("a2").Resize(1000, 6) = ""
- Range("a2").Resize(q, 6) = arr1
- End Sub
复制代码
本帖最后由 zjdh 于 2012-5-28 10:47 编辑
批量修改文件名3.rar
(18.81 KB, 下载次数: 113)
|
|