vba：将文本文件导入Excel工作表

Question

我正在编写一个vba代码，该代码应该删除所选Excel工作表上的数据，打开一个文本文件选择对话框，然后将该文本文件中的数据导入到我从中删除数据的相同表单。到目前为止，我只能将文本文件打开到新的工作簿中，但无法将其打开到我从中删除数据的同一工作表。这是我到目前为止所提供的内容，非常感谢您的帮助：

Dim Filt As String

Dim FilterIndex As Integer

Dim Title As String

Dim FileName As Variant

Filt = "Cst Files (*.prn),*.prn"

Title = "Select a cst File to Import"

FileName = Application.GetOpenFilename(FileFilter:=Filt, Title:=Title)

If FileName = False Then

MsgBox "No File Was Selected"

Exit Sub

End If

With Application.ActiveSheet

    Cells.Select

Selection.QueryTable.Delete

Selection.ClearContents

End With

Workbooks.Open FileName

谢谢！

Answer 1

您可以通过多种方法将文本文件导入当前工作表。这里有三个（包括你上面使用的方法）使用QueryTable打开内存中的文本文件，然后写入当前工作表，最后根据需要应用Text To Columns。如果要在新工作簿中打开文本文件后使用当前使用的方法，只需将其复制到当前工作表中 Cells.Copy使用QueryTable这是我记录的一个简单的宏。请根据您的需要进行修改。Sub Sample()&nbsp; &nbsp; With ActiveSheet.QueryTables.Add(Connection:= _&nbsp; &nbsp; &nbsp; &nbsp; "TEXT;C:\Sample.txt", Destination:=Range("$A$1") _&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; .Name = "Sample"&nbsp; &nbsp; &nbsp; &nbsp; .FieldNames = True&nbsp; &nbsp; &nbsp; &nbsp; .RowNumbers = False&nbsp; &nbsp; &nbsp; &nbsp; .FillAdjacentFormulas = False&nbsp; &nbsp; &nbsp; &nbsp; .PreserveFormatting = True&nbsp; &nbsp; &nbsp; &nbsp; .RefreshOnFileOpen = False&nbsp; &nbsp; &nbsp; &nbsp; .RefreshStyle = xlInsertDeleteCells&nbsp; &nbsp; &nbsp; &nbsp; .SavePassword = False&nbsp; &nbsp; &nbsp; &nbsp; .SaveData = True&nbsp; &nbsp; &nbsp; &nbsp; .AdjustColumnWidth = True&nbsp; &nbsp; &nbsp; &nbsp; .RefreshPeriod = 0&nbsp; &nbsp; &nbsp; &nbsp; .TextFilePromptOnRefresh = False&nbsp; &nbsp; &nbsp; &nbsp; .TextFilePlatform = 437&nbsp; &nbsp; &nbsp; &nbsp; .TextFileStartRow = 1&nbsp; &nbsp; &nbsp; &nbsp; .TextFileParseType = xlDelimited&nbsp; &nbsp; &nbsp; &nbsp; .TextFileTextQualifier = xlTextQualifierDoubleQuote&nbsp; &nbsp; &nbsp; &nbsp; .TextFileConsecutiveDelimiter = False&nbsp; &nbsp; &nbsp; &nbsp; .TextFileTabDelimiter = True&nbsp; &nbsp; &nbsp; &nbsp; .TextFileSemicolonDelimiter = False&nbsp; &nbsp; &nbsp; &nbsp; .TextFileCommaDelimiter = True&nbsp; &nbsp; &nbsp; &nbsp; .TextFileSpaceDelimiter = False&nbsp; &nbsp; &nbsp; &nbsp; .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)&nbsp; &nbsp; &nbsp; &nbsp; .TextFileTrailingMinusNumbers = True&nbsp; &nbsp; &nbsp; &nbsp; .Refresh BackgroundQuery:=False&nbsp; &nbsp; End WithEnd Sub在内存中打开文本文件Sub Sample()&nbsp; &nbsp; Dim MyData As String, strData() As String&nbsp; &nbsp; Open "C:\Sample.txt" For Binary As #1&nbsp; &nbsp; MyData = Space$(LOF(1))&nbsp; &nbsp; Get #1, , MyData&nbsp; &nbsp; Close #1&nbsp; &nbsp; strData() = Split(MyData, vbCrLf)End Sub获得阵列中的数据后，可以将其导出到当前工作表。使用您已在使用的方法Sub Sample()&nbsp; &nbsp; Dim wbI As Workbook, wbO As Workbook&nbsp; &nbsp; Dim wsI As Worksheet&nbsp; &nbsp; Set wbI = ThisWorkbook&nbsp; &nbsp; Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import&nbsp; &nbsp; Set wbO = Workbooks.Open("C:\Sample.txt")&nbsp; &nbsp; wbO.Sheets(1).Cells.Copy wsI.Cells&nbsp; &nbsp; wbO.Close SaveChanges:=FalseEnd Sub跟进您可以使用它Application.GetOpenFilename来选择相关文件。例如...Sub Sample()&nbsp; &nbsp; Dim Ret&nbsp; &nbsp; Ret = Application.GetOpenFilename("Prn Files (*.prn), *.prn")&nbsp; &nbsp; If Ret <> False Then&nbsp; &nbsp; &nbsp; &nbsp; With ActiveSheet.QueryTables.Add(Connection:= _&nbsp; &nbsp; &nbsp; &nbsp; "TEXT;" & Ret, Destination:=Range("$A$1"))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '~~> Rest of the code&nbsp; &nbsp; &nbsp; &nbsp; End With&nbsp; &nbsp; End IfEnd Sub

Answer 2

你可以写.WorkbookConnection.Delete .Refresh BackgroundQuery：= False这将删除文本文件外部连接。

Answer 3

我认为我对这个问题的回答是你想要做的最简单的解决方案：选择文件中第一行文本所在的单元格。使用Data/ Get External Data/ From File对话框选择要导入的文本文件。根据需要格式化导入的文本。在Import Data打开的对话框中，单击Properties...取消选中此Prompt for file name on refresh框。只要外部文件发生变化，请单击Data/ Get External Data/ Refresh All按钮。注意：在您的情况下，您可能应该跳过步骤＃5。