PDA

View Full Version : Clean Files names on a list, compare and signals missing files



Plutark
10-27-2008, 03:20 AM
Hi I am new to the forum, so Hi to everyone!

I have a project to work on at work.

What I need to do is :

I have a list of all the journals/invoices which have been posted in the system during the month.

I need to check on the shared folder if the people have put the copy of the journal as a back up. So far I have found a script to list all the items in the shared folder and the sub folders.

The problem is we have litteraly thousands of postings made per month. So I need to find a way to check if there is any missing journal in the shared drive.

the way we name the journals is "document number + name of the transaction". I only need to check the document number.

to summarize I need:

-A way to clean the list I have and only keep the document numbers
-Compare and signals missing files


Do you know if there is any formula that might help me do that? I don't need the whole formula, just where I should be looking etc...

thank you for your help, I think I am gonna spend time on this forum, find it through suggestions from my Google Reader, I guess he knew I was desperate to find a solution : pray2:

Plutark

CreganTur
10-27-2008, 06:23 AM
Welcome to the forum- always good to see new members!


the way we name the journals is "document number + name of the transaction". I only need to check the document number.

Can you post an example of a file name? when you post your example let us know which part is the document number, and which part is the transaction name (if they are not obvious).


Do you have a master list that shows all of the document numbers that should exist? If not, then we need something to compare the files with to tell if something is missing.

Plutark
10-27-2008, 07:21 AM
Thanks for your quick answer

Ok so here is a common file names, I have asked everybody for the last two months to name the file in putting always the document number first. Basically I just need this number as it is a unique number. So for example here I need the first 10 digits (the same for any document number)

1134089088 - CZTQP correction Oct 08

I then just need the 1134089088 here.

and I have attached an example (example.xls) of the list I get of all the posting which have been made during that period.


I then do a directory listing of all the files I have on the shared drive where every one is supposed to copy the journals. So I get a basic list of all the files names (document number + transaction name).


What I want is compare the first list with the last one and check if every journal posted is stored on the shared drive.



Hope I am clear enough?:help

CreganTur
10-27-2008, 07:35 AM
First things first: you can get just the document number using:
Left("1134089088 - CZTQP correction Oct 08",Instr(1,"1134089088 - CZTQP correction Oct 08"," ")-1)
Which returns '1134089088'

If you don't use the '-1' at the end of the code, then you will have a space left over at the end of your document number.

Your second question- determining which files are missing, is a little more indepth. I'm sorry I can't work on it right now (meeting coming up), but I'll try to get back to you soon- unless someone else answers your question first.