VBA搜索文件夹并按名称选择文件 [英] vba search through a folder and select files by name

问题描述

我的文件夹中有很多文件，这些文件的名称如下:

I've got lots of files in a folder structered with names like:

"[e-mail]"

"[e-mail]"

其中"02122013"​​位是日期-2013年12月12日.其中一些是在同一天制作的.我想创建一个文件，将同一天制作的所有工作簿都放入一个大文件中.到目前为止，我一直在努力选择打开特定于一天的文件.有谁知道什么样的代码可以帮助我呢?

where the "02122013" bit is the date - 02/12/2013. Some of them have been made on the same day. I'd like to create a file that takes all of the workbooks that were made on the same day and put them all in one big file. So far I am struggling to get the selectivity to open a day-specific file. Does anyone have any idea what kind of code can help me with this?

已解决，感谢您的帮助！这是对我有用的代码:

Solved, thanks for the help, all! Here was the code that worked for me:

 folder_location = Application.ActiveWorkbook.Path

i2 = 0

strFile = Dir(folder_location & "\")

'looping through to find the right file names and putting them all in an array
While strFile <> ""
    If Right(strFile, 3) = "csv" Then
                file_to_analyse = Split(strFile, "@")
                If Right(file_to_analyse(0), 8) = date_we_want_to_analyse_on Then
                    found_files_to_analyse(i2) = strFile
                    i2 = i2 + 1
                End If
            End If
    strFile = Dir
Wend

推荐答案

是否要基于文件保存日期或文件名来合并文件.以昨天的日期命名的文件可以今天保存，并带有今天的日期.我猜您将不得不解析文件名，并在do while循环中对日期(在文件名中)应用逻辑，直到在目录中的所有文件中搜索到该条件为止.如果满足条件，则将打开的文件复制到文件中的工作表中.如果不满足条件，则跳过该文件.遵循可能会有所帮助

Do you want to consolidate files on the basis of file saved date or on the basis of the name of the file. A file named with yesterday's date can be saved today and will bear today's date. I guess you would have to parse the name of the file and apply logic around the date (in the file name) in a do while loop till all the files in the directory are searched for the condition. If the condition is met, you copy the opened file into a worksheet in your file. If the condition is not met, the file is skipped. Following might help

For each date which needs to be consolidated, do the following in a loop or an in a user prompted message box where the user inputs the date. You also need to chose whether you want the consolidation to happen in the workbook from where you are launching the macro or a separately opened workbook. The code below assumes you are consolidating in the same workbook.

Path = Path of the directory in which the files are stored

Flname = Dir(Path & "*.csv")

Do While Flname <> "" 

If ' file check Condition' Then
    Filecheckname = True  ' Checks if file follows the naming conventions desired
Else
    Filecheckname = False
End If

If Filecheckname Then
    FlDate = getDate(Flname)     ' extracts the date from the file name
Else
    GoTo Errorhandler ' If there is an error, then the macro stops with a message to the user
End If

If FlDate<> Date Then

    flsskpd = flsskpd + 1       ' If the date criteria is not met, the file is skipped with an increment to the fileskipped counter
    Flname = Dir()
Else

     Workbooks.Open Filename:=Path & Flname, ReadOnly:=True

'Code to Copy into consolidated workbook (ThisWorkbook)
filesmoved = filesmoved + 1
     Flname = Dir()
End if

Loop

Message to user about how many files skipped and consolidated. 

Prompt user whether to continue to the next date consolidation, if yes, continue or take the new date as an input and repeat the loop

这篇关于VBA搜索文件夹并按名称选择文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！