导入文本文件的内容和文本文件的名称，然后使用宏将其分隔到Excel中 [英] Import text files contents and name of text file and you separator into Excel with a macro

问题描述

由于

解决方案

尝试

  Sub Import_video_txt_files()添加对MICROSOFT文件系统对象的引用昏暗的objFSO作为FileSystemObject昏暗的objFolder为文件夹昏暗的objFile作为文件昏暗的objTextStream作为TextStreamDim strPath作为字符串昏暗的我只要Dim s作为字符串，vSplit'指定文件夹...strPath ="C:\ Users \ User \ Desktop \ TEST \"'使用Microsoft脚本运行时.设置objFSO = New FileSystemObject设置objFolder = objFSO.GetFolder(strPath)'检查文件夹中每个文件的扩展名.对于objFolder.Files中的每个objFile如果objFSO.GetExtensionName(objFile.Name)="txt"，则单元格(i + 2，1)= objFile.Name设置objTextStream = objFile.OpenAsTextStream(ForReading)'Cells(i + 2，2)= objTextStream.ReadAlls = objTextStream.ReadAllvSplit =拆分(s，"|")Range("b"& i + 2).Resize(1，UBound(vSplit)+1)= vSplit我=我+ 1万一下一个结束子 

I have this code thanks to Import text files contents and name of text file into Excel with a macro

Option Explicit

Sub Import_video_txt_files()

    ' 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 + 2, 1) = objFile.Name
            Set objTextStream = objFile.OpenAsTextStream(ForReading)
            Cells(i + 2, 2) = objTextStream.ReadAll
            i = i + 1
        End If
    Next
End Sub

However now I need to expand the function.

The text file has all the information on one line.

For example: grey, red, blue|408.95|14165.849841859

I am using this separator |

I would like to be able to import all the information from the text file and separate them using | and put them in their appropriate cells as illustrated in the image below.

解决方案

Try,

Sub Import_video_txt_files()

    ' 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
    Dim s As String, vSplit

    ' 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 + 2, 1) = objFile.Name
            Set objTextStream = objFile.OpenAsTextStream(ForReading)
            'Cells(i + 2, 2) = objTextStream.ReadAll
            s = objTextStream.ReadAll
            vSplit = Split(s, "|")
            Range("b" & i + 2).Resize(1, UBound(vSplit) + 1) = vSplit
            i = i + 1
        End If
    Next
End Sub

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