作者:乔山办公网日期:
返回目录:excel表格制作
没有见到具体文百本格式
以及固定长度字符提取要求
所以给出以下建议:
1、使用excel打开文本,然后度看看表格格式,用公式之类是否可以提取
2、就是使用VBA直接读取文本进行提内取
无论用哪种方式,都需要楼主明确数据格容式
建议进一步明确下吧
写一个示例吧:e799bee5baa6e997aee7ad94e59b9ee7ad94331
你依据着改:
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\infor.txt", _
Destination:=Range("A1"))
.Name = "infor_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierSingleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
亲,试一下这个知,会在当前Excel所在的道文件夹下建立一个ac.txt文件,版里面存放A/C列的数据,中间用权TAB空白隔开。
Sub ac()
Dim rcA, i As Long
Dim s As String
rcA = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To rcA
s = s & Range("A" & i) & vbTab & Range("B" & i) & vbCrLf
Next
Open ThisWorkbook.Path & "\ac.txt" For Output As #1
Print #1, s
Close #1
End Sub
Sub query()
Dim filename, inputstring As String, doc
filename = Worksheets(2).Range("B2").Value'文件保存路径
If CreateObject("Scripting.FileSystemObject").FileExists(filename) = True Then
'判断文件是否存在zhidao
Open filename For Input Access Read As #1
'若存在则打开tet文件,名版为#1
Do While Not EOF(1)
Line Input #1, inputstring
'读取tet文件每行数据
doc = inputstring
'将每行数据赋值给权doc
Loop
Close #1'关闭文件
Kill filename'删除文件
End If
End Sub