我如何提高我的处理Application.FileSearch VBA替代的功能 [英] How can i improve my function for handling alternative to Application.FileSearch VBA

问题描述

我决定尝试使用UDF替代Application.FileSearch。我假设几个文件COULD所在的位置。互联网上的解决方案往往会假定用户一般都知道文件所在的位置，这个假设可以是任何地方。

编辑：互联网上有很多解决方案很长时间我相信它应该更有效率，因此使用这篇文章作为讨论如何实现的一种手段

请注意，我用X替换路径目录，文件名只是文件名

  public Function FindFile（）
 
如果Len（Dir（C：\X\X\X\File Name.xlsm，vbDirectory））< 0然后
 Workbooks.Open（C：\X\X\X\File Name.xlsm），UpdateLinks：= False 
 
 ElseIf Len（Dir（C ：\X\File Name.xlsm，vbDirectory））<> 0然后
 Workbooks.Open（C：\X\File Name.xlsm），UpdateLinks：= False 
 
 ElseIf Len（Dir（C：\X\ X\File Name.xlsm，vbDirectory））< 0然后
 Workbooks.Open（C：\X\X\File Name.xlsm），UpdateLinks = = False 
 
如果
 
结束函数
  

我对上述代码感到满意，但我觉得这可以更加动态不必指定文件的可能位置。

请随时编辑这篇文章，并提供您的想法：）

解决方案你谈谈效率，你的意思是可读性吗？还是处理能力方面的效率？第一个例子很容易阅读和更改，所以我会说它是可读的，但是如果你知道一个文件是在3个位置中的一个，那么最好单独地分配每个位置，就像在第二个例子。

关于以下内容，它依赖于您所指定的HostFolder内的相关文件，因此您可以更精确地执行更多操作将会有效率。例如，使用以下内容将会变得越来越有效：

C：\

C：\Reports

C：\Reports\May

在这里给@Rich的答复：

循环使用VBA的所有子文件夹

  Sub MainBeast（）
 Dim FileSystem As Object 
 Dim HostFolder As String 
 
 HostFolder =C：\mypath\
 
 Set FileSystem = CreateObject（Scripting.FileSystemObject） 
 DoFolder FileSystem.GetFolder（HostFolder）
 End Sub 
 Sub DoFolder（Folder）
 
 Dim SubFolder 
对于Folder.SubFolders中的每个子文件夹
 DoFolder SubFolder 
下一个
 Dim File 
对于Folder.Files中的每个文件
如果File.Name =Name.xlsm然后
 W orkbooks.Open（Folder.Path& \& Name.xlsm），UpdateLinks：= False 
工作簿（Name.xlsm）。激活
退出子
结束如果
下一个
 End Sub 
  

我应该说，这只会打开它找到名为name的文件的第一个实例。 XLSM。如果要处理多个文件，您需要进行修改，尽管通过使用 Path.FileDateTime 存储潜在路径并打开最近的

关于第二个，如果你有一个候选名单，我会使用下面的代码，这是更有效率，但如果文件不在右边位置，那么它将无法工作：

  sub MainBeast（）
如果fileExists（C：\ &Name.xlsm）然后Workbooks.Open（C：\&Name.xlsm），UpdateLinks：= False 
如果fileExists（C：\locA\ Name.xlsm）然后Workbooks.Open（C：\locA\&Name.xlsm），UpdateLinks：= False 
如果fileExists（C：\locB\ ;Name.xlsm）然后Workbooks.Open（C：\locB\&Name.xlsm），UpdateLinks：= False 
 End Sub 
函数FileExists（ByVal FullPath As String）As Boolean 
如果dir（FullPath）<> 然后
 FileExists = True 
 Else 
 FileExists = False 
如果
结束函数
  

I have decided to attempt a UDF around alternative to Application.FileSearch. I assume a few locations where a file COULD be located. Solutions on the internet tend to assume the user generally knows where the file is located, this assumed it could be anywhere,

EDIT: Alot of solutions on the internet are long winded and i believe it should be much more efficient, hence using this post as a means of a discussion as to how this can be achieved

Please note, I have replaced the path directories with an 'X' and the file name is just "File Name"

Public Function FindFile()

If Len(Dir("C:\X\X\X\File Name.xlsm", vbDirectory)) <> 0 Then
    Workbooks.Open ("C:\X\X\X\File Name.xlsm"), UpdateLinks:=False

ElseIf Len(Dir("C:\X\File Name.xlsm", vbDirectory)) <> 0 Then
    Workbooks.Open ("C:\X\File Name.xlsm"), UpdateLinks:=False

ElseIf Len(Dir("C:\X\X\File Name.xlsm", vbDirectory)) <> 0 Then
    Workbooks.Open ("C:\X\X\File Name.xlsm"), UpdateLinks:=False

End If

End Function

I am happy with the code above but i feel it could be even more dynamic to the point of not having to specify POSSIBLE locations of a file.

Please feel free to edit this post as you see fit and contribute your thoughts :)

解决方案

You talk about efficiency, do you mean readability? Or efficiency in terms of processing power required? The first example is easy enough to read, and change, so I would say that it's readable, but if you know that a file is in, say, one of 3 locations, it would be better to dir each location separately, as in the second example.

Regarding the following, it relies on the file in question being inside the "HostFolder" that you specify, so effectively the more precise you can be, the more efficient it will be. For example, using the following will be increasingly more efficient:

C:\

C:\Reports

C:\Reports\May

Credit to @Rich for his answer here:

Loop Through All Subfolders Using VBA

Sub MainBeast()
    Dim FileSystem As Object
    Dim HostFolder As String

    HostFolder = "C:\mypath\"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
End Sub
Sub DoFolder(Folder)

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        If File.Name = "Name.xlsm" Then
            Workbooks.Open (Folder.Path & "\" & "Name.xlsm"), UpdateLinks:=False
            Workbooks("Name.xlsm").Activate
            Exit Sub
        End If
    Next
End Sub

I should say though, that this will just open the first instance that it finds of the file named "name.xlsm". You need to make modifications if you want to deal with multiple files, although this should be easily possible by storing the potential paths with the Path.FileDateTime and opening the most recent.

Regarding the second, if you have a shortlist of places to check, then I would use the code below, this is more efficient, but if the file is not in the right location, then it won't work:

sub MainBeast()
    if fileExists("C:\" & "Name.xlsm") then Workbooks.Open ("C:\" & "Name.xlsm"), UpdateLinks:=False
    if fileExists("C:\locA\" & "Name.xlsm") then Workbooks.Open ("C:\locA\" & "Name.xlsm"), UpdateLinks:=False
    if fileExists("C:\locB\" & "Name.xlsm") then Workbooks.Open ("C:\locB\" & "Name.xlsm"), UpdateLinks:=False
End Sub
Function FileExists(ByVal FullPath As String) As Boolean
If dir(FullPath) <> "" Then
    FileExists = True
Else
    FileExists = False
End If
End Function

这篇关于我如何提高我的处理Application.FileSearch VBA替代的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！