将文本文件导入Excel工作表 [英] Importing text file into excel sheet

问题描述

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

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

推荐答案

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

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. 使用查询表
2. 打开内存中的文本文件，然后写入当前工作表，并根据需要最终将文本应用于列".
3. 如果要使用当前使用的方法，则在新工作簿中打开文本文件后，只需使用 Cells.Copy
将其复制到当前工作表中即可.

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

使用查询表

这是我录制的一个简单宏.请根据您的需要进行修改.

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

打开内存中的文本文件

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.

使用您已经在使用的方法

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

关注

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

You can use the Application.GetOpenFilename to choose the relevant file. For example...

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

这篇关于将文本文件导入Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！