vba - 改进现有的 VBA 代码以更快地复制文件
问题描述
我是 VBA 上的谷歌搜索编码器。我的电脑中有一个本地文件夹,里面有大约 5000 多个 pdf。我决定将 pdf 排序到具有相同名称的文件夹中。代码的执行非常冗长,因为代码必须循环 5000+,以便相应地进行排序。下面的代码工作正常。我也可以忍受。
只是出于好奇,我发布了这个问题,如果有一种方法可以更快地完成这项任务。
Sub Create_FoldersAndExtractFiles()
Dim sh1 As Object
'for going through the files Dim FSO As Scripting.fileSystemObject Dim
SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
fname As String
Set fso = New Scripting.FileSystemObject
'http://excelspreadsheetshelp.blogspot.com penAt = "My computer:\"
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
scr_Folder = ShellApp.self.Path
'create the folders where-ever the workbook is saved
lrow = sh1.Range("a" & Rows.Count).End(xlUp).Row
If lrow = 1 Then
MsgBox "No data to create the folder"
Else
For i = 2 To lrow
fname = sh1.Range("a" & i)
'to create a new folder
If Len(Dir(ActiveWorkbook.Path & "\" & fname, vbDirectory)) = 0 Then
MkDir (scr_Folder & "\" & fname)
End If
'to move the file into a folder
dst_folder = scr_Folder & "\" & fname
Set SourceFolder = fso.GetFolder(scr_Folder)
For Each FileItem In SourceFolder.Files
mname = Left(FileItem.NAME, InStr(1, FileItem.NAME, ".") - 1)
If InStr(LCase(mname), LCase(fname)) Then
fso.MoveFile Source:=scr_Folder & "\" & mname & "*.*", Destination:=dst_folder
End If
Next
Next
Set FileItem = Nothing
Set SourceFolder = Nothing
Set fso = Nothing
End If
MsgBox "Done"
End Sub
解决方案
如何更换部分(循环)
For Each FileItem In SourceFolder.Files
mname = Left(FileItem.NAME, InStr(1, FileItem.NAME, ".") - 1)
If InStr(LCase(mname), LCase(fname)) Then
fso.MoveFile Source:=scr_Folder & "\" & mname & "*.*", Destination:=dst_folder
End If
Next
仅此行:
fso.MoveFile Source:=scr_Folder & "\*" & fname & "*.*", Destination:=dst_folder
推荐阅读
- quarkus - Quarkus 1.2.0 本机构建不适用于 Java 11
- ibm-mq - PyMQI 持久性较差的消费性能
- authentication - 更改 userModel 以进行身份验证 CAKE 3
- python - 使用生命线和分类变量的 Cox 回归
- angular - Dispatch action N 次,但只执行一次 API 调用并合并结果 N 次
- node.js - Vue 块供应商因运行时而臃肿
- docker - 为不同的架构拉取 docker 镜像
- android - 尝试使用 ExoPlayer 播放 RTMP 流
- postgresql - 架构中新表的 Redshift 默认权限
- powerbi - SSAS 表格模型 - 如何仅在 RAM 中保留活动模型