Decrease serial number if matching file found

Hi Everyone, Any help with any part of this issue would be a great help. I have posted this a couple of times before. You are probably getting sick of seeing this but I hoped someone may be able to help, I'm not giving up on this issue.
http://www.excelforum.com/excel-prog...ml#post3530142

So to the brain achingly annoying issue. What I'm trying to achieve is;
1. When a new document is opened from my template a unique serial number (in format "00000") is generated (currently using txt file)
2. Once the number is generated a search is preformed to see if a file exists saved with the same serial number as a name.
3. If a file exists then the txt files stored value is decreased by 1 and that is displayed in "K5".
4. If the search returns a 0 then the original serial number is displayed.

Please Login or Register  to view this content.

Currently i don't belive the search function is working. So any help with that would be greatly appreciated and oddly once the search fails to run properly the original serial number dissapears.

Thanks Everyone.

Many thanks for getting back to me. I'm quite new to VBA and didn't know of Dir, but a brief read makes me think it would work well. Could you show me how to pass the contents of cell K5 to the Dir search?

K5 is where the serial number is displayed. Which is generated by the "Function GetSerialNumber".

Yes sorry to be so confussing. I am trying to search for a file, as discribed in my oringinal post.
"1. When a new document is opened from my template a unique serial number (in format "00000") is generated (currently using txt file)
2. Once the number is generated a search is preformed to see if a file exists with the same serial number as a name."
So what I'm trying to do is, generate a serial number and then check that a file does exists already with that serial number. I am trying to make sure no one can VOID a number without meaning to.

The file will be stored in the same location as the template.

Hi Norie, I've been trying to work this out for myself and done a lot of reading and searching on the dir subject. However I just can't get it to work for me. Can you help?

Please Login or Register  to view this content.

Sorry, its not a text file its a xls file, it would have the same name as a serial number eg "25601". It would be located in the same directory as the original template file C:\Documents and Setting\ect.xls. However if you mean the text file that stores the value that the serial number is generated from then that is called "Invoice.txt" and that again is in the same directory.

It suggests that the syntax has an error. It gives an "Compile Error. Exspected List Seperator or )" error message.

Thank you, how stupid of me. Well that is sort of working now. It seems to run and exectue however when I save an xls file with the same name as the next serial number to be generated it still says "File does not exist".
What I have done is add a section to the search function to exectue the opposit of the generate serial number code so instead on +1 to the txt file its -1. However when the search function is preformed atm the contents of "K5" (serial number) is dissapears. Any ideas why this is happening?

Please Login or Register  to view this content.

Just to clarify, it clears the content of cell K5 after the ok button is pushed on the MsgBox.