使用宏将文本文件的内容和文本文件的名称导入Excel [英] Import text files contents and name of text file into Excel with a macro

问题描述

我在下面的代码中部分起作用.

I have this code below which partially works.

• Cats.txt(内容:灰色，蓝色，黑色)
• Dogs.txt(内容:粉红色，红色，橙色)
• Cow.txt(内容:黑色，白色)

这就是我想要得到的:

Dim objFSO    As Object
Dim objFolder As Object
Dim objFile   As Object
Dim strPath   As String
Dim strName   As String

' Specify the folder...
strPath = "C:\Users\User\Desktop\TEST\"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
' Check extension of each file in folder.
For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = ".txt" Then
Cells(i + 1, 1) = objFile.Name

    i = i + 1

End If
End Sub

不幸的是，我无法将文本文件的内容导入到B列中.我一直在网上寻找，说实话，我有点不成功.

Unfortunately I am unable to get the contents of the text file imported into column B. I have been looking online that I am a little bit unsuccessful to be honest.

推荐答案

尝试类似的方法.确保将引用添加到"Microsoft脚本运行时" ...

Try something like this. Make sure you add the reference to "Microsoft Scripting Runtime" ...

Option Explicit

Sub load()

    ' ADD REFERENCE TO MICROSOFT FILE SYSTEM OBJECT

    Dim objFSO As FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim objTextStream As TextStream
    Dim strPath As String
    Dim i As Long

    ' Specify the folder...
    strPath = "C:\Users\User\Desktop\TEST\"

    ' Use Microsoft Scripting runtime.
    Set objFSO = New FileSystemObject
    Set objFolder = objFSO.GetFolder(strPath)

    ' Check extension of each file in folder.
    For Each objFile In objFolder.Files
        If objFSO.GetExtensionName(objFile.Name) = "txt" Then
            Cells(i + 1, 1) = objFile.Name
            Set objTextStream = objFile.OpenAsTextStream(ForReading)
            Cells(i + 1, 2) = objTextStream.ReadAll
            i = i + 1
        End If
    Next
End Sub

这篇关于使用宏将文本文件的内容和文本文件的名称导入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！