vba：将文本文件导入excel表 [英] vba: Importing text file into excel sheet

问题描述

我正在编写一个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要导入的cst文件
 FileName = Application.GetOpenFilename（FileFilter：= Filt，Title：= Title）
 
如果FileName = False然后
 MsgBox未选择文件 
退出Sub 
结束If 
 
 With Application.ActiveSheet 
 Cells.Select 
 Selection.QueryTable.Delete 
 Selection.ClearContents 
结束
 
 Workbooks.Open FileName 
  

谢谢！

解决方案

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

1. 使用QueryTable


2. 打开内存中的文本文件，然后写入当前工作表，如果需要，最后应用文本列。


3. 如果要使用当前使用的方法，则打开文本后文件在一个新的工作簿，只需将其复制到当前工作表使用 Cells.Copy

使用QueryTable

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

  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：=假
结束
结束子
  

打开文本文件记忆

  Sub Sample（）
 Dim MyData As String，strData（）As String 
 
打开C：\Sample.txt对于二进制为＃1 
 MyData =空格$（LOF（1））
获取＃1，MyData 
关闭＃ 1 
 strData（）= Split（MyData，vbCrLf）
 End Sub 
  

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

使用您已经使用的方法

  Sub Sample（）
 Dim wbI As Workbook，wbO As Workbook 
 Dim wsI As Worksheet 
 
设置wbI = ThisWorkbook 
设置wsI = wbI.Sh eets（Sheet1）'< ~~您要导入的工作表
 
设置wbO = Workbooks.Open（C：\Sample.txt）
 
 wbO.Sheets（1）.Cells.Copy wsI.Cells 
 
 wbO.Close SaveChanges：= False 
 End Sub 
   Application.GetOpenFilename 
 
 
 
 / code>选择相关文件。例如... 
 子样本（）
 Dim Ret 
 
 Ret =应用程序.GetOpenFilename（Prn Files（* .prn），* .prn）$​​ b 
 $ b如果Ret<> False Then 
 With ActiveSheet.QueryTables.Add（Connection：= _ 
TEXT;& Ret，Destination：= Range（$ A $ 1））
 
' ~~>其余的代码
 
结束
结束如果
结束Sub 
  
 
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
Thanks!
解决方案
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
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
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$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...
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


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