问题描述
我正在编写一个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()
$ p $您可以使用
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表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!