VBA code to open many files, copy text, close file, paste text in second work

Looking for VBA code that when I click a button in my "Master PCN Data" workbook it looks in a (2014 Test, folder) and opens multiple workbooks with the name PCN12345, PCN 12346 ect. copying one number, 12 employees initials, and 12 dates from the PCN12345 workbooks and enters the info into the "Master PCN Data" workbook, then closes the PCN12345 workbook and moves to the next workbook PCN12346 and so on.

Also before it closes each PCN workbook it enters the word "Processed" into cell A5 so when I go back in a week or so later to enter any new PCN's that have been put in the (2014 Test, folder) the "Master PCN Data" workbook only enters or looks at the ones that do not have the word "Processed" in cell A5.

Have attached 2 files to look at.

The "Master PCN Data" workbook has some code already started that I believe finds the PCN workbooks form the folder, copies the PCN number but that as far as I can get, need help extracting the employees initials, dates.

Note: the folder with the PCN's has many files and more added each day.

Hope a have not confused the issue.

Thanks, Bikeman

Hi Bikeman,

Just a question - when a PCN workbook has been processed, could it be moved into a "Processed" folder located in the same folder as the one in which the PCN workbook itself is stored? If this was an option it would significantly reduce the overall processing time, as workbooks which had been already processed would not be "visible" to be re-opened (perhaps several times?)

Regards,

Greg M

Hi again BikeMan,

I've been thinking a bit more about your post. Production-environment applications like this really interest me, and I'd hope to be able to post something here in the next day or two - watch this space!

Any thoughts/comments on the question I asked in my previous post?

Regards,

Greg M

Hi Bikeman,

The "approved date" cells in your PCN workbook all contain formulas equivalent to:

Please Login or Register  to view this content.

If the Initiator inserts his initials today, then today's date will appear automatically in the associated "approved date" cell. So far, so good, BUT, if the workbook is re-opened next week, the TODAY() formula will insert the CURRENT date. I don't think that this is what you have in mind, or am I missing something???

Maybe when the workbook is closed, all cells which contain date formulas should have their contents automatically converted to date values?

Regards,

Greg M

Thank you very much, this worked perfect!

Hi Greg,

Yes it could be moved to a "Processed" folder. Good idea!

Bikeman

I like your idea about moving to a processed folder, I will work on it.

I also have plans with the Master PCN Data workbook to extract additional data, but have not made it that far yet.

Greg,

You are right about the date, that is not what I had in mind. I'm not sure how to have their contents automatically converted to date values if that will work.

Thanks for noticing that, I will need to work on a fix.

Thanks, Bikeman

Hi Bikeman,

Another question - can the Master workbook be located in the same folder as the PCN workbooks?

If it can, it means that it will not be necessary to specify the path (G:\HYD-Engineering\Product_Data\Product Data Management\PDM Files\RUN REPORT\2014 Test) to the PCN workbooks in the VBA code. As long as the Master workbook is located in the same folder it will be able to locate the PCN files.

Making some progress here - watch this space!

Regards,

Greg M

Hi again,

Yet another question!

Does a completed PCN workbook always need to contain 12 names and dates, or are there situations where fewer than 12 are acceptable? If 12 names/dates are ALWAYS required then some form of data validation should be included to prevent a PCN file from being processed if some of the required values are missing.

Regards,

Greg M

Greg,

Sorry I have been out of the office. Yes the master workbook can be in the same folder as the PCN workbook.

Bikeman

Some PCN's will have all 12 names and dates and some will have a mix of name or the word "Skip" and date. All PCN's should have the first 4 names and dates, (Initiator, Engineer, Eng Manager, Released by) the next 7 places manufacturing thru sales do not have to add their name if the PCN does not apply to them but they do need to look at it and if it does not apply to them they are required to select "Skip" from the drop down list which also adds the date and they will forward it on to the next department. So I guess all 12 will have either a name and date or the word "Skip and date. The last one "complete" is also always with a name and date.

WOW!!! Greg this is great. I have to say my skill level with excel code on a scale of 1-10 is about a 2. So I'm hoping I can understand all you have done.

One bump in the road to note: I mentioned in an earlier reply about adding additional data and management now wants me to add a column on the "Master PCN Data" form for each department that indicates how many days it is if a cell is blank, from the previous date in the same row until now or until the date we run the report.
This means now some PCN's for example may have been signed and dated thru the first five or six departments with names and dates but the remaining departments will be blank, no names or dates. So then management wants to be able to transfer all PCN's to the "Master PCN" workbook even if they are not thru the hole process or not complete with all 12 signed off.

The additional data from the "Master PCN" workbook management is after is to find the blank date in a row (this would be where a department is sitting on the PCN or working on it) and has not signed off on it yet. They then basically want to know how long has the department be working on the PCN. So looking back from the blank cell to the last date entered and calculating how many days until now or how many until the day they process the report (how long has the PCN be sitting in that department).

Anyway long story, this is something I will need to work on adding in to the Master PCN.
I'm going to play with the files you sent.
Thanks again for the great work!!!
Bikeman

Hi Greg,

Management here is trying to look at all our PCN's, ones that have made it thru the complete process, ones that have just started or half way thru the process.

Unfortunately the PCN's are currently in 2 different places, a folder called "ISSUED" these are the completed ones, and a folder called "CHANGE NOTICE" these are ones that are in process at various stages. The Excel files that are in the change notice folder are linked in Outlook emails sent out that go's to the first department and they complete their task, and then move it to the next mail box until complete, then they get moved to the "ISSUED" folder.

They are trying to look at all of them to determine how long it is taking to get them thru the entire PCN process and who or what departments are holding up the process. We have to run a report bi-weekly to send to corporate.

I'm open to any suggestions you might have that would make this any easier.

Thanks again,
Bikeman

Hi Bikeman,

Ok, thanks for that.

Another knee-jerk reaction/question - one "overall" PCN folder which contains (a) the Master workbook, (b) the ISSUED folder, and (c) the CHANGE NOTICE folder.

The Master workbook "investigates" all of the PCN workbooks in the ISSUED folder and stores the results on the existing "Master" worksheet (whose name would be changed to "ISSUED") and then "investigates" all of the partially completed PCN workbooks in the CHANGE NOTICE folder and highlights the various delays on a separate worksheet called "CHANGE NOTICE".

How does the above sound?

The advantage of keeping everything inside one "overall" folder is that it's then not necessary for the Master workbook to know about a detailed filepath to where the PCN files are located - it takes the starting point for the search as the folder in which the Master workbook itself is located.

Feasible? Sort-of feasible? Crazy?

Regards,

Greg M

Greg,

Very Feasible! I like the "One" overall PCN folder. It would be no problem putting everything in one folder.

Bikeman

Hi Bikeman,

Ok - thanks for that!

I'll work on the second worksheet for the Master workbook - the one for recording the days for which a PCN is "stuck" in a department.

Regards,

Greg M

Hi again Bikeman,

I think that this thing is starting to look good!!!

I've added the "Change Notice" worksheet to the "Master" workbook, and for each uncompleted PCN workbook it highlights where the current logjam is, and for how many days it has lasted (up to the date of the report).

To see how the application works, do the following:

(1) Create an "overall" PCN folder - you can name the folder whatever you like, because the VBA Code doesn't need to know the name of the "overall" folder

(2) Copy the "Master" workbook into the "overall" folder

(3) Create two folders inside the "overall" folder - name one of these folders "Issued", and the other one "Change Notice" (if you need to change these folder names later on it will require only a very simple alteration to the VBA code)

(4) Copy the first three PCN workbooks (12345, 23456 and 34567) into the "Issued" folder

(5) Copy the next three PCN workbooks (45678, 56789 and 67890) into the "Change Notice" folder - all of these workbooks represent PCN's whose approval process has not yet been completed

(6) Copy one of the uncompleted PCN workbooks (e.g. 45678) also into the "Issued" folder

(7) Open the "Master" workbook

(8) On the "Issued" worksheet, if appropriate, click on the "Clear" button and then click on the "Update" button - a message will be generated stating that PCN workbook 45678 is incomplete, data from each of the fully completed PCN workbooks will be copied to the "Issued" worksheet, and all of the fully completed PCN workbooks will be moved to the "Processed" folder in the "Issued" folder - the "Processed" folder will be created automatically if it does not already exist. The uncompleted PCN workbook 45678 will NOT be moved to the "Processed" folder.

(9) On the "Change Notice" worksheet, click on the "Update" button (this will automatically clear the worksheet before starting the updating process, so you don't need to click on the "Clear" button if the worksheet already contains data). Data from each of the uncompleted PCN workbooks will be copied to the "Change Notice" worksheet, and the various formulas and Conditional Formatting will highlight the location and duration of the current logjam. PCN workbooks from the "Change Notice" folder are NOT moved to a "Processed" folder as these files will be processed several times until all of the necessary approvals have been issued. At that stage you should move them manually to the "Issued" folder where the workbook can be processed for the last time before being moved automatically to the "Processed" folder.

Try the above and let me know what you think of it.


While I've been typing this it's just occurred to me that it should probably be possible to detect when the final approval has been issued for a PCN workbook currently in the "Change Notice" folder, and then to automatically move that PCN workbook to the "Issued" folder, update the "Issued" worksheet, and then move the PCN workbook to the "Processed" folder - maybe I'll think about that and see if I can come up with something.

Anyway, I hope the above and the attached workbooks are helpful to you - please let me know how you get on with them.

Regards,

Greg M


P.S. It seems that I can attach a maximum of five files to a single post, so I'll attach the Master workbook and the three uncompleted PCN workbooks to a separate post

Hi Bikeman,

I've attached the Master workbook and the three uncompleted PCN workbooks as mentioned in my previous post.

Regards,

Greg M

Greg,

This is AWESOME!!! When I started this thread I was hoping for some code that would help with one issue, but I ended up with completely new master PCN workbook that is clean and organized way beyond my capabilities. I cant tell you how much I appreciate your work on this, it works perfect!

You mentioned to detect when the final approval has been issued for a PCN workbook, in our case automatically moving it would not be needed as on occasion the PCN may need to remain in the change notice folder for a short time even when it has been signed off as complete (long story) but that would be a nice addition.

This now makes me want to see what more data or information we can add in the future.

Thanks again for all your hard work,
Bikeman

Hi again Bikeman,

You're very welcome.

Hoping for feedback and appreciation like yours is what keeps me coming back to this Forum

I'm delighted to hear that you're so pleased with the application. If you need any assistance with the next phase of the development just let me know and I'll see what I can do. You can either send me a Private Message or send me something by email. If you're posting something in the open Forum, send me a Private Message in case I miss what you've posted.

Thanks for the opportunity to have worked on such an interesting project!

Best regards,

Greg M

Hi Greg,

Thanks for the offer of assistance on the next phase, you may very likely hear from me.

Thanks again for all your work.
Bikeman

Hi Greg,

Bikeman here! I know you have better things to be doing so don't feel you need help.

I have some bad news regarding all your hard work you did for me. Management here wants me to change the layout of the PCN Master workbook. They love the way it works but they want the data in columns instead of rows. Unfortunately they did not inform me of that when I laid it out. They want to run pivot tables and charts and according to them it would be easier to do when the data is in columns.
I tried to modify yours but I never have worked with all the defined names you have put in this workbook and having trouble trying to figure it out.
In your opinion to get the data in columns should I create a new PCN master workbook or would it be easier to modify the one you created?

Any suggestions?

Bikeman