|
本帖最后由 乐乐2006201506 于 2016-6-28 11:10 编辑
怎样判断文件名是否相同?下面第一段代码可以提取指定文件夹工作簿文件名,第二段代码可以提取指定文件夹中文本文件文件名,假如两个文件夹中工作簿和文本文件比较多,怎样用代码匹配相同文件名,然后将文本文件内容导入工作簿中。谢谢!
Sub 提取指定文件夹内的所有文件名() '含所有子文件夹内的文件
Dim Fso As Object, arrf$(), mf&
Set Fso = CreateObject("Scripting.FileSystemObject")
Call GetFiles("C:\Users\YYB\Desktop\导入文本文件 - 副本\", Fso, arrf, mf)
[A1].Resize(mf) = Application.Transpose(arrf)
Set Fso = Nothing
End Sub
Private Sub GetFiles(ByVal sPath$, ByRef Fso As Object, ByRef arrf$(), ByRef mf&)
Dim Folder As Object
Dim SubFolder As Object
Dim File As Object
Dim En$
Set Folder = Fso.GetFolder(sPath)
For Each File In Folder.Files
En = Fso.GetExtensionName(sPath & "\" & File.Name)
If En Like "*xls*" Then
mf = mf + 1
ReDim Preserve arrf(1 To mf)
arrf(mf) = Left(File.Name, Len(File.Name) - (Len(File.Name) - InStr(File.Name, ".")) - 1)
End If
Next
For Each SubFolder In Folder.SubFolders
Call GetFiles(SubFolder.Path, Fso, arrf, mf)
Next
Set Folder = Nothing
Set File = Nothing
End Sub
Sub 提取指定文件夹内的所有文件名1() '含所有子文件夹内的文件
Dim Fso As Object, arrf1$(), mf1&
Set Fso = CreateObject("Scripting.FileSystemObject")
Call GetFiles1("C:\Users\YYB\Desktop\导入文本文件 - 副本\", Fso, arrf1, mf1)
[B1].Resize(mf1) = Application.Transpose(arrf1)
Set Fso = Nothing
End Sub
Private Sub GetFiles1(ByVal sPath$, ByRef Fso As Object, ByRef arrf1$(), ByRef mf1&)
Dim Folder As Object
Dim SubFolder As Object
Dim File As Object
Dim En$
Set Folder = Fso.GetFolder(sPath)
For Each File In Folder.Files
En = Fso.GetExtensionName(sPath & "\" & File.Name)
If En Like "*txt" Then
mf1 = mf1 + 1
ReDim Preserve arrf1(1 To mf1)
arrf1(mf1) = Left(File.Name, Len(File.Name) - (Len(File.Name) - InStr(File.Name, ".xls")) - 1)
End If
Next
For Each SubFolder In Folder.SubFolders
Call GetFiles1(SubFolder.Path, Fso, arrf1, mf1)
Next
Set Folder = Nothing
Set File = Nothing
End Sub
下面代码可以将文本文件导入指定工作表中。
Sub 读取文本文件并放入指定工作表遍历()
Dim Fso As Scripting.filesystemobject
Dim mt As Scripting.textstream
Dim myfile$, i&, sht$, b As Boolean, rng$, fm$
myfile = Application.GetOpenFilename("TXT Files (*.txt), *.txt", 0, "选定文件", , False)
If myfile = "" Then Exit Sub
Set Fso = New Scripting.filesystemobject
Set mt = Fso.OpenTextFile(Filename:=myfile, IOMode:=ForReading)
sht = "测试" '公式插入那个工作表
With mt
Do Until .AtEndOfStream
i = i + 1
If i Mod 3 = 1 Then
b = False
sht = .ReadLine
End If
If i Mod 3 = 2 Then
b = False
rng = .ReadLine
End If
If i Mod 3 = 0 Then
b = True
fm = .ReadLine
End If
If b = True Then Sheets(sht).Range(rng).Formula = fm
Loop
.Close
End With
MsgBox "公式还原成功!"
End Sub
我这个做的是批量的把所有文件夹中的工作簿公式读取出来并保存在文本文件中,每个工作簿对应一个文本文件,最后可以批量的把这些文件夹中的工作簿对应工作表中的公式从文本文件中读取并进行还原,附件是包含了测试文件,第一次使用的时候必须遵守以下步骤:
1、删除公式文件夹下的所有文本文件(只有当改变了整个文件路径的时候执行)
2、执行过程“读取各工作簿对应工作表中公式并存储”(这时会在公式文件夹中生成N个文本文件)
3、你可以把原来已经读取并保存了的公式删掉用于测试,再执行“遍历所有工作薄并读取文本文件公式并放入对应工作表”这个过程,此时还原所有的公式!
保存公式时由于是把工作簿的路径保存为文档名称,而原因的时候也是依据路径去对于的文本文件,所有两个相对路径一定要对,即如果文件有移动,那么就要去执行步骤一!
整个过程可作为学习参考! - Sub 读取各工作簿对应工作表中公式并存储() '含所有子文件夹内的文件,对每个工作簿创建一个独立的文本文档
- Dim fso As Object, arrf$(), mf&, MyPath$, sht As Worksheet, wb As Workbook, Cell As Range, FormulaCells As Range
- Dim mt As Scripting.textstream
- Set fso = CreateObject("Scripting.FileSystemObject")
- Set objShell = CreateObject("Shell.Application")
- Set objFolder = objShell.BrowseForFolder(0, "请选择来源位置", 0, 0) '注意这里选择的是EXCEL表格所在的位置
- If Not objFolder Is Nothing Then
- MyPath = objFolder.self.Path
- Else
- MyPath = ""
- Exit Sub
- End If
- Call GetFiles(MyPath, fso, arrf, mf)
- Application.AskToUpdateLinks = False
- Application.DisplayAlerts = False
- If mf > 0 Then
- For i = 1 To mf
- Set wb = GetObject(arrf(i))
- myfile = ThisWorkbook.Path & "\公式" & Replace(Replace(arrf(i), "", "^"), ":", "$") & ".txt" '为了简便这里把公式存放在当前位置的【公式】文件夹中
- Set mt = fso.CreateTextFile(Filename:=myfile, overwrite:=True)
- For Each sht In wb.Sheets
- Set FormulaCells = sht.Range("A1").SpecialCells(xlFormulas, 23)
- If (FormulaCells Is Nothing) = 0 Then
- For Each Cell In FormulaCells
- mt.Write sht.Name & vbCrLf
- mt.Write Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & vbCrLf
- mt.Write Cell.Formula & vbCrLf
- Next
- End If
- Set FormulaCells = Nothing
- Next
- wb.Close False
- Next
- Else
- Exit Sub
- End If
- Application.DisplayAlerts = True
- Application.AskToUpdateLinks = True
- Set fso = Nothing
- End Sub
- Sub 遍历所有工作薄并读取文本文件公式并放入对应工作表()
- Dim fso As Object
- Dim mt As Scripting.textstream
- Dim myfile$, i&, sht$, b As Boolean, rng$, fm$, MyPath$, arrf$(), mf&, x&, wb As Workbook
- Set fso = CreateObject("Scripting.FileSystemObject")
- Set objShell = CreateObject("Shell.Application")
- Set objFolder = objShell.BrowseForFolder(0, "请选择目标位置", 0, 0) '注意这里也是选择EXCEL表格所在的位置
- If Not objFolder Is Nothing Then
- MyPath = objFolder.self.Path
- Else
- MyPath = ""
- Exit Sub
- End If
- Call GetFiles(MyPath, fso, arrf, mf)
- Application.AskToUpdateLinks = False
- Application.DisplayAlerts = False
- For i = 1 To mf
- myfile = ThisWorkbook.Path & "\公式" & Replace(Replace(arrf(i), "", "^"), ":", "$") & ".txt"
- Set fso = New Scripting.filesystemobject
- Set mt = fso.OpenTextFile(Filename:=myfile, IOMode:=ForReading)
- Set wb = Workbooks.Open(arrf(i))
- With mt
- Do Until .AtEndOfStream
- x = x + 1
- If x Mod 3 = 1 Then
- b = False
- sht = .ReadLine
- End If
- If x Mod 3 = 2 Then
- b = False
- rng = .ReadLine
- End If
- If x Mod 3 = 0 Then
- b = True
- fm = .ReadLine
- End If
- If b = True Then wb.Sheets(sht).Range(rng).Formula = fm
- Loop
- .Close
- End With
- wb.Close True
- x = 0
- Next
- MsgBox "公式还原成功!"
- Application.AskToUpdateLinks = True
- Application.DisplayAlerts = True
- End Sub
- Private Sub GetFiles(ByVal sPath$, ByRef fso As Object, ByRef arrf$(), ByRef mf&) '遍历文件
- Dim Folder As Object
- Dim SubFolder As Object
- Dim File As Object
- Dim En$
- Set Folder = fso.GetFolder(sPath)
- For Each File In Folder.Files
- En = fso.GetExtensionName(sPath & "" & File.Name)
- If En Like "*xls*" Then
- mf = mf + 1
- ReDim Preserve arrf(1 To mf)
- arrf(mf) = sPath & "" & File.Name
- End If
- Next
- For Each SubFolder In Folder.SubFolders
- Call GetFiles(SubFolder.Path, fso, arrf, mf)
- Next
- Set Folder = Nothing
- Set File = Nothing
- End Sub
复制代码
|
|