excel - Finding more than instance of data in a Text File -


i desperately trying create macro allow me following:

  1. select text file (different 1 everytime)
  2. look @ data , identify first instance of key phrases such "_ _z_1_:_"
  3. then extract data following phrase
  4. write excel
  5. then identify next instance of key phrase
  6. extract data
  7. write excel

any appreciated.

i'm not sure how it. code follows:

private sub commandbutton1_click() dim myfile string, text string, textline string, day string, zno string, netsales string, cash string, card string, hotdrinks string, colddrinks string, sweets string, crisps string, vat string, nextz string  myfile = application.getopenfilename()  dim bffr string, p long bffr = (myfile) p = instr(p + 1, bffr, "_ _z_1_:_", vbtextcompare) while cbool(p)  '_ _z_1_:_ found; process based upon starting position p  'see if there other occurrences of _ _z_1_:_ p = instr(p + 1, bffr, "_ _z_1_:_", vbtextcompare) loop  open myfile input #1 until eof(1) line input #1, textline text = text & textline loop close #1  day = instr(text, "welcome bite") zno = instr(text, "_ _z_1_:_") netsales = instr(text, "net sales          ") cash = instr(text, "cash in ") card = instr(text, "credit in") hotdrinks = instr(text, "hot drinks         ") colddrinks = instr(text, "cold drinks       ") sweets = instr(text, "sweets             ") crisps = instr(text, "crisps             ") vat = instr(text, "** fixed totaliser period 1 totals reset") nextz = instr(text, "_ _z_1_:_")   range("a1").value = mid(text, day + 19, 18) range("a2").value = mid(text, zno + 10, 8) range("a3").value = mid(text, netsales + 30, 7) range("a4").value = mid(text, cash + 30, 7) range("a5").value = mid(text, card + 30, 7) range("a6").value = mid(text, hotdrinks + 30, 7) range("a7").value = mid(text, colddrinks + 30, 7) range("a8").value = mid(text, sweets + 30, 7) range("a9").value = mid(text, crisps + 30, 7) range("a10").value = mid(text, vat - 9, 7)  range("b2").value = mid(text, p + 1, 8)   end sub 

as far being able choose different file each time, use

with application.filedialog(msofiledialogopen) 'disallows user selecting more 1 file @ time .allowmultiselect=false 'shows file open dialog box .show   if .selecteditems.count=0  'dismisses dialog box if no selection  else  strpath=.selecteditems(1)  end if  end 

you can use strpath read text from, though way accomplished reading text file write text right hidden sheet in workbook (make text=textline in do until eof(1) loop , add [workbookname].sheet([sheetname]).cells(xrowx,1)=text , xrowx=xrowx+1 after that). information more manageable in format, rather trying read 1 giant string. give whirl, see if makes things easier you.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -