Conas comhaid téacs a iompórtáil go Excel mar bhileoga ar leithligh le hainmneacha bunaidh in Excel?

An ndearna tú iarracht riamh comhaid téacs a iompórtáil go Excel mar bhileoga ar leithligh le hainmneacha bunaidh na gcomhad in Excel? San Airteagal seo, tugaim isteach cód VBA chun comhaid téacs a iompórtáil go tapa ó fhillteán amháin go Excel leis na hainmneacha.

Comhaid téacs a iompórtáil go Excel le hainmneacha

---

Comhaid téacs a iompórtáil go Excel le hainmneacha

1. Cumasaigh Excel, brúigh Alt + F11 eochracha le cumasú Microsoft Visual Basic d’Fheidhmchláir fuinneog, ansin cliceáil Ionsáigh > Modúil.

2. Cóipeáil agus greamaigh thíos an cód leis an Modúil script.

VBA: Iompórtáil comhaid téacs le hainmneacha

Sub LoadPipeDelimitedFiles()
'UpdatebyExtendoffice20181010
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xCount As Long
    Dim xWS As Worksheet
    
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\*.txt")
    Do While xFile <> ""
        xCount = xCount + 1
        Set xWS = Sheets(xCount)
        xWS.Select
        xWS.Name = "TEXT " & Left(xFile, Len(xFile) - 4)
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & xStrPath & "\" & xFile, Destination:=Range("A1"))
            .Name = "a" & xCount
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "|"
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            xFile = Dir
        End With
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files txt", , "Kutools for Excel"
End Sub

3. Brúigh F5 chun an cód a rith, ansin roghnaigh fillteán ina bhfuil comhaid téacs a theastaíonn uait a iompórtáil chuig Excel sa Roghnaigh fillteán dialóg.

4. cliceáil OK, iompórtáladh na comhaid téacs san fhillteán in Excel mar bhileog aonair lena n-ainmneacha féin.