Find multiple occurences of string in a text file using VBA?

Question

I have a large text file which is contains the text _ _Z_1_:_ followed by data such as:

_ _Z_1_:_0_1_3_4_2                      

 Fixed Totaliser Period 1 Reset Report  

 NET sales          57        £202.05   

 CASH in Drawer     55        £172.35   
 CREDIT in Drawer   2         £29.70    
 TOTAL in Drawer    57        £202.05

There are two occurrences of the _ _Z_1_:_ text in each file.
I am trying to get the macro to find the _ _Z_1_:_ and then lift the cash amount (in the above case £172.35) and credit etc.

I have tried using a code to allow me to specify the text file and then used the InStr function but it will only identify the first occurrence. Any help appreciated.

Post the code you've tried. Edit your question, don't add it in comment. — L42
– L42, Commented Mar 31, 2015 at 2:29

user4039065 · Accepted Answer · 2015-03-31 02:58:54Z

1

Your InStr function will return the position of the first occurence. Use that position to start the search for the second.

dim bffr as string, p as long
bffr = (◄ assign the text here)
p = instr(p + 1, bffr, "_ _Z_1_:_", vbtextconpare)
do while cbool(p)

  '_ _Z_1_:_ was found; process it based upon the starting position p

  'see if there are other occurrences of _ _Z_1_:_
  p = instr(p + 1, bffr, "_ _Z_1_:_", vbtextconpare)
loop

By continually advancing the search by starting one character ahead of the last position it was found you will walk through the buffer holding the text and pick up a new p for each occurrence. When it can no longer be found, InStr returns zero to p.

answered Mar 31, 2015 at 2:58

user4039065

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find multiple occurences of string in a text file using VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related