An idea on handling serial numbers?

[HotLanta]

Hi! I have a macro that I have put together and used for some time that is a huge help to me. I wanted to see if I could modify it to add an additional function.
As it stands, I run the macro and point it to a folder containing many text files. The macro does some modification to a text file, saves it, and loops through until all files in the folder have the new info in them.
I need for these text files to have a unique serial number in them as well.

My idea was to paste into the excel starting in cell A1 the list of serial numbers. The list would go down to A100.
I'm thinking to have the macro CUT cell A1 and paste the contents into H11 and from there that will be added to text file #1
Now that cell A1 is blank, for text file #2, the macro should see the cell is blank and go down to cell A2 for the serial.
This should go on until all of the text files are modified.
I will never run this on more than 70 files at a time probably, so I say cell A100.

Does this sound like a feasible way of handling this? If so, would someone suggest some code?

Thank you in advance.

 

[Micron]

Based on your post title I might do this: Append/insert the SN into the file name. Then I wouldn't have to open files to find out which one had the SN. I might also have A1 = "A" and B1 = 99. Then my code would concatenate as "A" & Range("B1")+1 (A100), alter the file name (C:\...myFileNameA100.txt) and increment B1 by 1. Then when/if I end up with more files, the code just carries on from the last used number. If I had to worry about anyone altering either A1 or B1 I might put the values on a separate locked/protected sheet.

 

[HotLanta]

Hi Micron and thank you for the reply. Ultimately another system opens and imports the data contained inside the files. The filename could be anything... the system will not take any information from the filename.

In short, a software produces the text files and they are read by a different software in another area of the company. I have been using my macro as a go between to format some information and add some information that the first software will not produce. If I could get this serial number thing going it really is a great solution and would end manually editing the files themselves (thousands of them).

 

[Micron]

The macro does some modification to a text file

So if you have code that edits the file, you could either use your idea or mine (where cells provide the seed value and code increments the numeric cell value)? Unless you want to re-use the list numbers. I don't understand why you'd take what's in A column and move it to H, nor would the code need to check if A1 is blank in order to move on. Code would just loop over your list rows. Could delete first if you want, but I don't see a need for it to find what I guess would be the first blank row in column A.

 

[HotLanta]

Maybe I wasn't completely clear. So I will paste into column A a unique set of serial numbers. In the first iteration, the contents of cell A1 will be copied to H11. The rest of my macro is taking information from H2 to H11 and adding that to the beginning of text file #1. In the second iteration, cell A1 will be blank so I need for the macro to then go to cell A2 and paste the contents to H11 then H2 to H11 goes to the top of text file #2. It loops through 50 or 60 files afterwards.

These are made up serial numbers below but this is the idea. It is unique information that I will insert into my main sheet before running. "qwerty8" goes into file 1 and "asdfg5" to file 2.

 

[Micron]

The list would go down to A100.

My bad; I thought A1 to A100 were your serial numbers. Sorry
It might be easier to integrate what you need into what you already have, for whoever takes this on? If you post that code, please use code tags (vba button on posting toolbar) to maintain indentation and readability.

Or inside your loop where the code puts H2 to H11 values into the files, have it also add A1 serial number then set A1 to "". If you're always going to go from A1 to A100 then a common approach would be to loop as

For i = 1 to 100
    Range("H11") = Range("A" & i)
    'file edit code here, or this code can pass the range (e.g. A1) to your existing sub, which would have to be modified to suit
    Range("A" & i) = ""
Next

As i steps from 1 to 100, the range will be A1, then A2 ... to A100.