本文介绍了如何使用制表符分隔符和文本限定符“导入文本文件”使用VBA编码。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在订单项行中合并两个分隔符。我如何一次性拆分数据。如果我只给vbtab数据导入正确但是有双引号
Sub importfile()打开 E :\ anaken\excel n vba\project \DATA SETS_VBA.txt 对于输入 As #1 x = 0 直到EOF( 1 )行输入#1,LineFromFile lineitems = Split(LineFromFile,vbCrLf& vbTab) ActiveCell.Offset (x, 0 )。值= lineitems( 0 ) ActiveCell.Offset(x, 1 )。值= lineitems( 1 ) ActiveCell.Offset(x, 2 )。值= lineitems( 2 ) ActiveCell.Offset(x, 3 )。值= lineitems( 3 ) ActiveCell.Offset(x, 4 )。值= lineitems( 4 ) ActiveCell.Offset(x, 5 )。值= lineitems( 5 ) ActiveCell.Offset(x, 6 )。值= lineitems( 6 ) ActiveCell.Offset(x, 7 )。值= lineitems( 7 ) ActiveCell.Offset(x, 8 )。值= lineitems( 8 ) ActiveCell.Offset(x, 9 )。值= lineitems( 9 ) x = x + 1 循环 关闭#1 结束 Sub数据
期间单位代码地区代码类型代码MT / TT贸易面板子商店类型MBD(地区)宇宙数PCV宇宙数理想样品(当前)可用样品 3350607 500199 500 199 TT手册SIMPLE Market Stall Large GRA1,046363,657,70629 10 3350607 500150 500 150 TT手册SIMPLE Market Stall Small GRA2,2591,168,516,331 28 43 3350607 610199 610 199 TT手册SIMPLE Market Stall Large North LUK1,326323,053,61240 27 3350607 610150 610 150 TT manual SIMPLE Market Stall Small North LUK1,473130,342,51723 29 3350607 660199 660 199 TT手册SIMPLE Market Stall Large North LUK2,9617,213,883,34838 14i想要划界标签和双引号
解决方案为什么要强行打开门? 使用 OpenText方法 [ ^ ]将文本文件作为新工作簿加载和解析,其中包含一个包含已解析文本文件的工作表数据。 我建议您在运行之前开始记录宏文本导入向导 [ ^ ],然后完成录制。接下来,您将能够根据需要改进该代码。 Workbooks.OpenText文件名:= E:\ annaken \excel n vba\project \DATA SETS_VBA .txt,_ 原产地:= 852,StartRow:= 1,DataType:= xlDelimited,TextQualifier:= _ xlDoubleQuote,ConsecutiveDelimiter:= False,Tab:= True,分号: = False,_ 逗号:= False,空格:= False,其他:= False,FieldInfo:= Array(数组( 1 , 1 ),_ 数组( 2 , 1 ),数组( 3 , 1 ),数组( 4 , 1 ),数组( 5 , 1 ),数组( 6 , 1 ),数组( 7 , 1 ),数组( 8 , 1 ),_ 数组( 9 , 1 ),数组( 10 , 1 ),数组( 11 , 1 ),数组( 12 , 1 ),数组( 13 , 1 )),_ TrailingMinusNumbers:= True
另一种方法是使用 QueryTables.Add [ ^ ]方法:
Dim wsh 作为工作表 设置 wsh = ThisWorkbook.Worksheets.Add After:= ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) with wsh.QueryTables.Add(Connection:= _ TEXT; E:\ annaken \ excel n vba\project \DATA SETS_VBA.txt,目的地:=范围( A1)_ ) .Name = wsh.Name .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 。 TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes =数组( 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 ) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:= False 结束 使用
How do i incorporate both the delimiters in the line items line. how do i split the data at one go.if i give only vbtab the data is imported correctly but there are double quotes left out
Sub importfile() Open "E:\anaken\excel n vba\project\DATA SETS_VBA.txt" For Input As #1 x = 0 Do Until EOF(1) Line Input #1, LineFromFile lineitems = Split(LineFromFile, vbCrLf & vbTab) ActiveCell.Offset(x, 0).Value = lineitems(0) ActiveCell.Offset(x, 1).Value = lineitems(1) ActiveCell.Offset(x, 2).Value = lineitems(2) ActiveCell.Offset(x, 3).Value = lineitems(3) ActiveCell.Offset(x, 4).Value = lineitems(4) ActiveCell.Offset(x, 5).Value = lineitems(5) ActiveCell.Offset(x, 6).Value = lineitems(6) ActiveCell.Offset(x, 7).Value = lineitems(7) ActiveCell.Offset(x, 8).Value = lineitems(8) ActiveCell.Offset(x, 9).Value = lineitems(9) x = x + 1 Loop Close #1 End Subthe data is
Period Cell Code Region Code Type Code MT/TT Trade Panel Sub Store Type MBD (Region) Universe Number PCV Universe Number Ideal Sample (current) Usable Sample 3350607 500199 500 199 TT manual SIMPLE Market Stall Large GRA "1,046" "363,657,706" 29 10 3350607 500150 500 150 TT manual SIMPLE Market Stall Small GRA "2,259" "1,168,516,331" 28 43 3350607 610199 610 199 TT manual SIMPLE Market Stall Large North LUK "1,326" "323,053,612" 40 27 3350607 610150 610 150 TT manual SIMPLE Market Stall Small North LUK "1,473" "130,342,517" 23 29 3350607 660199 660 199 TT manual SIMPLE Market Stall Large North LUK "2,961" "7,213,883,348" 38 14i want to delimit the tabs and the double quotes
解决方案 Why to force doors wide open? Use OpenText method[^] to load and parse a text file as a new workbook with a single sheet that contains the parsed text-file data. I'd suggest to start recordding macro before you run Text Import Wizard[^], then finish recording. Next, you'll be able to improve that code to your needs. [EDIT#1] Workbooks.OpenText Filename:="E:\anaken\excel n vba\project\DATA SETS_VBA.txt", _ Origin:=852, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _ TrailingMinusNumbers:=TrueAnother way is to use QueryTables.Add[^] method:
Dim wsh As Worksheet Set wsh = ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) With wsh.QueryTables.Add(Connection:= _ "TEXT;E:\anaken\excel n vba\project\DATA SETS_VBA.txt", Destination:=Range("A1") _ ) .Name = wsh.Name .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With更多推荐
如何使用制表符分隔符和文本限定符“导入文本文件”使用VBA编码。
发布评论