Excel VBA 按类型从当前文件夹获取文件列表

Excel VBA get file list from current folder by type

提问by Marcel

I have an excel macro to get the file list by type from current folder.

我有一个 excel 宏可以从当前文件夹中按类型获取文件列表。

Sub getfilelistfromfolder()

Dim varDirectory As Variant
Dim flag As Boolean
Dim i As Integer
Dim strDirectory As String

strDirectory = Application.ActiveWorkbook.Path & "\"
i = 1
flag = True
varDirectory = Dir("C:\Macro\*.txt", vbNormal)

While flag = True
    If varDirectory = "" Then
        flag = False
    Else
        Cells(i + 1, 1) = varDirectory
        varDirectory = Dir
        i = i + 1
    End If
Wend
End Sub

This works fine. But I want to make two modifications: 1. Change the path name in

这工作正常。但我想做两个修改： 1. 更改路径名

varDirectory = Dir("C:\Macro\*.txt", vbNormal)

to active workbook path. When I tried to change it and I got errors. I don't know how to combine

到活动工作簿路径。当我尝试更改它时出现错误。不知道怎么组合

Application.ActiveWorkbook.Path & "\"

Application.ActiveWorkbook.Path & "\"

with the .txt file type condition.

与 .txt 文件类型条件。

1. I want to print the results in cells starting from B2, B3, B4...

2. 我想在从 B2、B3、B4 开始的单元格中打印结果...

Could somebody please help me fix these two issues? Thanks in advance.

有人可以帮我解决这两个问题吗？提前致谢。

回答by gipadm

1. change varDirectory = Dir("C:\Macro\*.txt", vbNormal)to varDirectory = Dir(strDirectory, vbNormal)

2. change Cells(i + 1, 1) = varDirectoryto Cells(i + 1, 2) = varDirectory

1. 更改varDirectory = Dir("C:\Macro\*.txt", vbNormal)为varDirectory = Dir(strDirectory, vbNormal)

2. 更改Cells(i + 1, 1) = varDirectory为Cells(i + 1, 2) = varDirectory

回答by Miguel Febres

I don't know what error you got when running the code with the ActiveWorkbook but if you use the following code it should work. And for the second issue, you should change the second parameter of Cells from 1(A) to 2(B).

我不知道您在使用 ActiveWorkbook 运行代码时遇到了什么错误，但如果您使用以下代码，它应该可以工作。对于第二个问题，您应该将 Cells 的第二个参数从 1(A) 更改为 2(B)。

Sub getfilelistfromfolder()

Dim varDirectory As Variant
Dim flag As Boolean
Dim i As Integer
Dim strDirectory As String

strDirectory = Application.ActiveWorkbook.Path & "\"
i = 1
flag = True
varDirectory = Dir(strDirectory & "*.pdf", vbNormal)

While flag = True
    If varDirectory = "" Then
        flag = False
    Else
        Cells(i + 1, 2) = varDirectory
        varDirectory = Dir
        i = i + 1
    End If
Wend
End Sub

回答by Stian Yttervik

Well, the varDirectory string seems to work fine when I try to "immediate" it so I don't think there are errors there.

好吧，当我尝试“立即”它时 varDirectory 字符串似乎工作正常，所以我认为那里没有错误。

Just set

刚设置

strDirectory = Application.ActiveWorkbook.Path & "\*.txt"

and change this:

并改变这一点：

    Else
    Cells(i + 1, 1) = varDirectory
    ' this is... unnecessary: varDirectory = Dir
    i = i + 1