将文本文件导入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)
- 使用查询表
- 打开内存中的文本文件,然后写入当前工作表,并根据需要最终将文本应用于列".
- 如果要使用当前使用的方法,则在新工作簿中打开文本文件后,只需使用
Cells.Copy
将其复制到当前工作表中即可.
- 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
使用查询表
这是我录制的一个简单宏.请根据您的需要进行修改.
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屋!