使用VBA获取文件夹中的Excel文件列表

问题描述

我需要获取文件夹中所有Excel文件的名称，然后对每个文件进行更改。我已经把make changes部分整理出来了。有没有办法在一个文件夹中获取 .xlsx 文件的列表，例如 D：\Personal 和将它存储在一个String数组中。

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.

然后，我需要遍历文件列表，并在我认为可以使用的每个文件上运行一个宏： / p>

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

    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 that that seems to be broken in VBA for whatever reason (bug?).

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