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

vba: Importing text file into excel sheet

提问by Elad Sommer

I'm writing a VBA code which supposed to delete the data on a selected excel sheet, open a dialog box for text file selection, and then import the data from that text file to the same exact sheet I've deleted the data from. So far I can only open the text file into a new workbook but can't open it to the same sheet I've deleted the data from.

我正在编写一个 VBA 代码，该代码应该删除所选 Excel 工作表上的数据，打开一个用于选择文本文件的对话框，然后将该文本文件中的数据导入到我从中删除数据的同一个工作表中。到目前为止，我只能将文本文件打开到新工作簿中，但无法将其打开到我从中删除数据的同一张工作表中。

Here's what I came with so far, will appreciate your help:

这是我到目前为止所带来的，感谢您的帮助：

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

回答by Siddharth Rout

There are many ways you can import Text file to the current sheet. Here are three (including the method that you are using above)

有多种方法可以将文本文件导入当前工作表。这里有三个（包括你上面使用的方法）

1. Using a QueryTable
2. Open the text file in memory and then write to the current sheet and finally applying Text To Columns if required.
3. If you want to use the method that you are currently using then after you open the text file in a new workbook, simply copy it over to the current sheet using Cells.Copy

1. 使用查询表
2. 在内存中打开文本文件，然后写入当前工作表，最后根据需要应用“文本到列”。
3. 如果要使用当前使用的方法，然后在新工作簿中打开文本文件后，只需使用以下命令将其复制到当前工作表 Cells.Copy

Using a QueryTable

使用查询表

Here is a simple macro that I recorded. Please amend it to suit your needs.

这是我录制的一个简单的宏。请修改它以满足您的需要。

Sub Sample()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Sample.txt", Destination:=Range("$A") _
        )
        .Name = "Sample"
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open the text file in memory

打开内存中的文本文件

Sub Sample()
    Dim MyData As String, strData() As String

    Open "C:\Sample.txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)
End Sub

Once you have the data in the array you can export it to the current sheet.

一旦您拥有数组中的数据，您就可以将其导出到当前工作表。

Using the method that you are already using

使用您已经在使用的方法

Sub Sample()
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet

    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import

    Set wbO = Workbooks.Open("C:\Sample.txt")

    wbO.Sheets(1).Cells.Copy wsI.Cells

    wbO.Close SaveChanges:=False
End Sub

FOLLOWUP

跟进

You can use the Application.GetOpenFilenameto choose the relevant file. For example...

您可以使用Application.GetOpenFilename来选择相关文件。例如...

Sub Sample()
    Dim Ret

    Ret = Application.GetOpenFilename("Prn Files (*.prn), *.prn")

    If Ret <> False Then
        With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Ret, Destination:=Range("$A"))

            '~~> Rest of the code

        End With
    End If
End Sub

回答by sandeep patel

you can write .WorkbookConnection.Delete after .Refresh BackgroundQuery:=False this will delete text file external connection.

您可以在 .Refresh BackgroundQuery:=False 之后编写 .WorkbookConnection.Delete 这将删除文本文件外部连接。

回答by ysap

I think my answer to my own questionhere is the simplest solution to what you are trying to do:

我认为我在这里对自己问题的回答是您尝试做的最简单的解决方案：

1. Select the cell where the first line of text from the file should be.

2. Use the Data/Get External Data/From Filedialog to select the text file to import.

3. Format the imported text as required.

4. In the Import Datadialog that opens, click on Properties...

5. Uncheck the Prompt for file name on refreshbox.

6. Whenever the external file changes, click the Data/Get External Data/Refresh Allbutton.

1. 选择文件中第一行文本所在的单元格。

2. 使用Data/ Get External Data/From File对话框中选择文本文件导入。

3. 根据需要格式化导入的文本。

4. 在Import Data打开的对话框中，单击Properties...

5. 取消选中该Prompt for file name on refresh框。

6. 每当外部文件发生更改，请点击Data/ Get External Data/Refresh All按钮。

Note: in your case, you should probably want to skip step #5.

注意：在您的情况下，您可能应该跳过第 5 步。