使用 VBA 获取文件夹中的 Excel 文件列表 [英] Get list of Excel files in a folder using VBA

问题描述

我需要获取文件夹中所有 Excel 文件的名称，然后对每个文件进行更改.我已经整理了进行更改"部分.有没有办法在一个文件夹中获取 .xlsx 文件的列表，比如 D:Personal 并将其存储在一个字符串数组中.

I need to get the names of all the Excel files in a folder and then make changes to each file. I've gotten the "make changes" part sorted out. Is there a way to get a list of the .xlsx files in one folder, say D:Personal and store it in a String Array.

然后我需要遍历文件列表并对我认为可以使用的每个文件运行一个宏:

I then need to iterate through the list of files and run a macro on each of the files which I figured I can do using:

Filepath = "D:Personal"
For Each i in FileArray
    Workbooks.Open(Filepath+i)
Next

我查看了这个，但是，我无法打开文件，因为它以 Variant 格式存储名称.

I had a look at this, however, I wasn't able to open the files cause it stored the names in Variant format.

简而言之，如何使用 VBA 获取特定文件夹中的 Excel 文件名列表?

In short, how can I use VBA to get a list of Excel filenames in a specific folder?

推荐答案

好吧，这可能对你有用，一个接受路径并返回文件夹中文件名数组的函数.循环遍历数组时，您可以使用 if 语句仅获取 excel 文件.

Ok well this might work for you, a function that takes a path and returns an array of file names in the folder. You could use an if statement to get just the excel files when looping through the array.

Function listfiles(ByVal sPath As String)

    Dim vaArray     As Variant
    Dim i           As Integer
    Dim oFile       As Object
    Dim oFSO        As Object
    Dim oFolder     As Object
    Dim oFiles      As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    Set oFiles = oFolder.Files

    If oFiles.Count = 0 Then Exit Function

    ReDim vaArray(1 To oFiles.Count)
    i = 1
    For Each oFile In oFiles
        vaArray(i) = oFile.Name
        i = i + 1
    Next

    listfiles = vaArray

End Function

如果我们可以仅通过索引号访问文件对象中的文件就好了，但无论出于何种原因(错误?)，这似乎在 VBA 中都被破坏了.

It would be nice if we could just access the files in the files object by index number but that seems to be broken in VBA for whatever reason (bug?).

这篇关于使用 VBA 获取文件夹中的 Excel 文件列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！