谢谢!
解决方案有很多方法可以将Text文件导入当前工作表。这里有三个(包括上面使用的方法)
使用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 $ p $您可以使用 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 FileNameThanks!
解决方案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
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 SubOpen 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 SubOnce 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 SubFOLLOWUP
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表
发布评论