vba：将文本文件导入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文件导入当前工作表。这里有三个（包括上面使用的方法）

使用QueryTable

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

如果要使用当前使用的方法，则打开文本后文件在一个新的工作簿，只需将其复制到当前工作表使用 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

/ 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