如何在 Excel 中将多个文本文件导入到多个工作表?

ms excelmicrosoft technologiesadvanced excel function更新于 2024/10/26 14:36:17

Microsoft Excel 在管理和分析海量数据方面的效率备受推崇。然而，在 Excel 中将多个文本文件导入到单独的工作表的过程乍一看似乎令人望而生畏。幸运的是，Excel 本身就提供了一个简单而强大的解决方案，可以无缝地实现这一目标。本文将逐步探讨如何将多个文本文件导入不同的工作表，以增强组织结构并促进数据集的有效分析。

掌握这些方法后，您将能够更好地将数据从多个文本文件导入 Excel，从而在处理大量数据集时节省时间和精力。无论您是处理来自不同来源的数据，还是需要整合来自多个文本文件的信息，Excel 丰富的导入功能都将帮助您简化工作流程。让我们深入了解如何在 Excel 中导入文本文件，并探索能够提升您数据管理技能的实用技巧。

使用 VBA 宏导入多个文本文件

如果您熟悉 Excel VBA(Visual Basic for Applications)，您可以利用其编程功能将多个文本文件导入到单独的工作表中。这种方法提供了灵活性和自定义选项，可用于处理特定需求。

要在 Excel 中启用 VBA，请按照以下说明操作

右键单击功能区栏，然后选择"自定义功能区"选项。

选中"开发人员"框，然后点击"确定"。

方法 1:使用 VBA 宏导入多个文本文件，方法是选择包含所有所需文本文件的文件夹

步骤 1 - 在 Excel 中打开 Visual Basic 编辑器。您可以按"Alt+F11"或导航到功能区中的"开发人员"选项卡，然后选择"Visual Basic"选项。

步骤 2 − 在 Visual Basic 编辑器中，点击"插入"，然后选择"模块"以插入新模块。

步骤 3 − 在模块中，粘贴以下 VBA 代码 −

Sub LoadPipeDelimitedFiles()
   'UpdatebyExtendoffice20181010
   Dim xStrPath As String
   Dim xFileDialog As FileDialog
   Dim xFile As String
   Dim xSheetCount As Long
   Dim xWS As Worksheet
   Dim xRow As Long ' Added variable for row reference
    
   On Error GoTo ErrHandler
   Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
   xFileDialog.AllowMultiSelect = True
   xFileDialog.Title = "Select a folder [Kutools for Excel]"
   If xFileDialog.Show = -1 Then
      xStrPath = xFileDialog.SelectedItems(1)
   End If
   If xStrPath = "" Then Exit Sub
    
   ' Ask the user for the number of copies
   xSheetCount = InputBox("Enter the number of sheets:", "Number of Copies")
   If Not IsNumeric(xSheetCount) Or xSheetCount < 1 Then
      MsgBox "Invalid number of copies. Please enter a positive number.", vbExclamation, "Invalid Input"
      Exit Sub
   End If
    
   Application.ScreenUpdating = False
   Set xWS = Sheets.Add(After:=Sheets(Sheets.Count))
   xWS.Name = "Sheet 1"
   xRow = 1 ' Start with row 1
    
   xFile = Dir(xStrPath & "\*.txt*")
   Do While xFile <> ""
      With xWS.QueryTables.Add(Connection:="TEXT;" & xStrPath & "" & xFile, Destination:=xWS.Cells(xRow, 1)) ' Update destination range
         .Name = "a" & xSheetCount
         .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
      End With
      xFile = Dir
      xRow = xRow + 1 ' Increment row reference
   Loop
    
   ' Create copies of the sheet
   For i = 2 To xSheetCount
      xWS.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = "Sheet " & i
   Next i
    
   Application.ScreenUpdating = True
   Exit Sub
    
ErrHandler:
   MsgBox "No txt files found.", , "Kutools for Excel"
End Sub

步骤 4 - 选择"宏"选项卡。

步骤 5 - 选择可用的宏并点击"运行"。

步骤 6 - 将打开窗口以选择包含文本文件的文件夹。

步骤 7 − 它会询问您需要的纸张数量。输入所需数字，然后点击"确定"。

Excel 会将文本文件中的数据导入到单独的工作表中。

方法 2:使用 VBA 宏通过选择所需文件导入多个文本文件

步骤 1 − 要在 Excel 中打开 Visual Basic 编辑器，您可以按"Alt+F11"。或者，您也可以按"Alt+F11"。您可以在功能区中打开"开发人员"选项卡，然后选择"Visual Basic"选项。

步骤 2 − 在 Visual Basic 编辑器中，点击"插入"，然后选择"模块"以插入新模块。

步骤 3 − 在模块中，粘贴以下 VBA 代码 −

Sub LoadPipeDelimitedFiles()
   'UpdatebyExtendoffice20181010
   Dim xFileDialog As FileDialog
   Dim xFile As Variant
   Dim xSheetCount As Long
   Dim xWS As Worksheet
   Dim xRow As Long ' Added variable for row reference
   Dim i As Long ' Added variable for loop counter
    
   On Error GoTo ErrHandler
   Set xFileDialog = Application.FileDialog(msoFileDialogFilePicker)
   xFileDialog.AllowMultiSelect = True
   xFileDialog.Title = "Select text files [Kutools for Excel]"
   xFileDialog.Filters.Clear
   xFileDialog.Filters.Add "Text Files", "*.txt"
    
   If xFileDialog.Show = -1 Then
      xSheetCount = InputBox("Enter the number of sheets:", "Number of Copies")
      If Not IsNumeric(xSheetCount) Or xSheetCount < 1 Then
         MsgBox "Invalid number of copies. Please enter a positive number.", vbExclamation, "Invalid Input"
         Exit Sub
      End If
        
      Application.ScreenUpdating = False
      Set xWS = Sheets.Add(After:=Sheets(Sheets.Count))
      xWS.Name = "Sheet 1"
      xRow = 1 ' Start with row 1
        
      For Each xFile In xFileDialog.SelectedItems
         With xWS.QueryTables.Add(Connection:="TEXT;" & xFile, Destination:=xWS.Cells(xRow, 1)) ' Update destination range
            .Name = "a" & xSheetCount
            .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
         End With
         xRow = xRow + 1 ' Increment row reference
      Next xFile
        
      ' Create copies of the sheet
      For i = 2 To xSheetCount
         xWS.Copy After:=Sheets(Sheets.Count)
         Sheets(Sheets.Count).Name = "Sheet " & i
      Next i
        
      Application.ScreenUpdating = True
      Exit Sub
   End If
    
ErrHandler:
   MsgBox "No text files selected.", , "Kutools for Excel"
End Sub

步骤 4 − 选择"宏"选项卡。

步骤 5 − 选择可用的宏并点击"运行"。

步骤 6 − 它将打开一个文本文件窗口。按住"Ctrl"键并点击所需文件，选择多个文本文件，然后按"确定"。

步骤 7 − 系统会询问您需要的工作表数量。输入所需数字，然后点击"确定"。

Excel 会将文本文件中的数据导入到单独的工作表中。

总结

在 Excel 中，将多个文本文件导入多个工作表是一项非常有效的功能，可以显著提高您高效管理和分析数据的能力。在本文中，我们探讨了两种成功完成此任务的方法。第一种方法是使用 Excel 的 Power Query 编辑器，让您可以轻松地从多个文本文件导入和转换数据。第二种方法是使用 VBA 宏来自动化导入过程，并提供针对特定需求的自定义选项。

将这些技术融入您的 Excel 工作流程，以简化文本文件的导入，节省时间并提升数据管理能力。Excel 丰富的导入功能使您能够无缝处理来自各种来源的数据，从而促进数据分析和决策过程。