vba - Copy specific lines from text files into excel -
i have developed below code open large number of text files (within same folder) , copy each file excel (one cell each line of text file & 1 row each text file).
however, not require of data text files , slowing down process. text files in following format:
dataset unstructured_grid points 5 float 0.096853 0.000000 0.111997 0.096853 -0.003500 0.111997 0.096890 0.000000 0.084015 0.096853 -0.003500 0.111997 0.096890 -0.003500 0.084015 cell_data 5 scalars pressure float 1 lookup_table default -0.000000 -0.000000 -3.000000 -2.000000 -6.000000 the data need copy file second batch of numbers (below "lookup_table default"). number of lines in example 5 (as stated on line starting "cell_data" number can change file file.
in summary, i'm looking code copy last batch of numbers excel instead of i'm @ loss on how tackle this.
any or advice appreciated.
sub importtextfile()
dim rowndx integer dim colndx integer dim tempval string dim wholeline string dim pos integer dim nextpos long dim savecolndx integer fname = "e:\zdump\" myfile = dir(fname & "*.txt") sep = vblf savecolndx = activecell.column rowndx = activecell.row while myfile <> "" open (fname & myfile) input #1 while not eof(1) line input #1, wholeline if right(wholeline, 1) <> sep wholeline = wholeline & sep end if colndx = savecolndx pos = 1 nextpos = instr(pos, wholeline, sep) while nextpos >= 1 tempval = mid(wholeline, pos, nextpos - pos) cells(rowndx, colndx).value = tempval pos = nextpos + 1 colndx = colndx + 1 nextpos = instr(pos, wholeline, sep) wend rowndx = rowndx + 1 wend close #1 myfile = dir() debug.print text loop end sub
give try:
dim rowndx integer dim colndx integer dim tempval string dim wholeline string dim pos integer dim nextpos long dim savecolndx integer dim saverowndx long dim founddata boolean dim numberofdata long fname = "e:\zdump\" myfile = dir(fname & "*.txt") sep = vblf colndx = activecell.column rowndx = activecell.row saverowndx = rowndx while myfile <> "" open (fname & myfile) input #1 while not eof(1) line input #1, wholeline if right(wholeline, 1) <> sep wholeline = wholeline & sep end if pos = 1 nextpos = instr(pos, wholeline, sep) while nextpos >= 1 tempval = mid(wholeline, pos, nextpos - pos) if founddata = false if instr(tempval, "cell_data") numberofdata = val(right(tempval, len(tempval) - len(left(tempval, len("cell_data") + 1)))) end if if instr(tempval, "lookup_table default") <> 0 founddata = true end if pos = nextpos + 1 nextpos = instr(pos, wholeline, sep) else if numberofdata <> 0 cells(rowndx, colndx).value = tempval pos = nextpos + 1 rowndx = rowndx + 1 nextpos = instr(pos, wholeline, sep) numberofdata = numberofdata - 1 end if end if wend wend close #1 founddata = false colndx = colndx + 1 cells(saverowndx, colndx).activate rowndx = saverowndx myfile = dir() 'debug.print text loop
Comments
Post a Comment