vba:将文本文件导入excel表格

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. Here's what I came with so far, will appreciate your help:

我正在编写一个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

Thanks!

谢谢!

3 个解决方案

#1

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

有很多方法可以将文本文件导入到当前的表中。这里有三个(包括上面使用的方法)

Using a QueryTable
使用QueryTable
Open the text file in memory and then write to the current sheet and finally applying Text To Columns if required.
打开内存中的文本文件，然后写入当前表，如果需要，最后将文本应用到列。
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
如果您想使用当前正在使用的方法，那么在您打开新工作簿中的文本文件之后，只需使用Cells.Copy将其复制到当前工作表中

Using a QueryTable

使用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$1") _
        )
        .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.GetOpenFilename to choose the relevant file. For example...

您可以使用该应用程序。选择相关文件的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$1"))

            '~~> Rest of the code

        End With
    End If
End Sub

#2

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

你可以写.WorkbookConnection。在. refresh BackgroundQuery:=False之后删除，这将删除文本文件外部连接。

#3

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

我想我对我自己的问题的回答是你要做的事情的最简单的解决办法:

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

选择该文件的第一行文本所在的单元格。
Use the Data/Get External Data/From File dialog to select the text file to import.

使用Data/Get External Data/From File对话框选择要导入的文本文件。
Format the imported text as required.

根据需要格式化导入的文本。
In the Import Data dialog that opens, click on Properties...

在打开的导入数据对话框中，单击Properties…
Uncheck the Prompt for file name on refresh box.

在刷新框中取消对文件名称的提示。
Whenever the external file changes, click the Data/Get External Data/Refresh All button.

当外部文件发生变化时，单击Data/获取外部数据/刷新All按钮。

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

注意:在您的例子中，您可能需要跳过第5步。

#1