VBA-在文件夹中打开文件并打印名称

debugcn 发表于 Dev

泰勒

我想打开某个文件夹中的所有文件，并打印出这些文件的名称。

我已经设置了一个打开文件的代码，但是我无法获取它来打印名称。我有一个单独的代码，可以打印名称，但只能打开一个文件。我无法将两者正确地结合在一起。有任何想法吗？

打开所有Excel文件的代码：

‘set path to progress folder
Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = “C:\Users\trembos\Documents\TDS\progress"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
Workbooks.Open fileName:=MyFolder & "\" & MyFile
    MyFile = Dir
Loop
End Sub

打印一个文件名的代码：

'set path to TDS_Working
Sub TDS()
    Workbooks.Open ("C:\Users\trembos\Documents\TDS\progress")
End Sub

'set up dim
Sub LoopThroughDirectory()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object

Dim i As Integer

'create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'get the folder object
Set objFolder = objFSO.GetFolder("C:\Users\trembos\Documents\TDS\progress\")
i = 1
'loop through directory file and print names
For Each objFile In objFolder.Files
    'print file name
    Cells(i + 1, 1) = objFile.Name
Next objFile
End Sub

R3uK

这应该工作顺利：

   Sub LoopThroughDirectory()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim MyFolder As String
    Dim Sht As Worksheet
    Dim i As Integer

    MyFolder = "C:\Users\trembos\Documents\TDS\progress\"

Set Sht = ActiveSheet

    'create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'get the folder object
    Set objFolder = objFSO.GetFolder(MyFolder)
    i = 1
    'loop through directory file and print names
    For Each objFile In objFolder.Files

        If LCase(Right(objFile.Name, 3)) <> "xls" And LCase(Left(Right(objFile.Name, 4), 3)) <> "xls" Then
        Else
            'print file name
            Sht.Cells(i + 1, 1) = objFile.Name
            i = i + 1
            Workbooks.Open Filename:=MyFolder & objFile.Name
        End If
    Next objFile

    End Sub