将文本文件导入excel表

将文本文件导入excel表

本文介绍了vba:将文本文件导入excel表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

  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

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表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-30 00:52