Opening a text file copying a specific set of info for a list off numbers

Zip several of the wpd folders with the wpd files and attach them to this post. Also include a sample workbook with part numbers matching the WPD folders.

I'll need a bit more help with understanding what you want with the TR Number. In the picture that you show there are two of them. Which do you want to list? Also, are they always in the same rows? Otherwise I will assume you want to find them based on the first two characters being "TR."

It's not that long of a shot: go to the directory matching the part number, open the MPF file, find the TR Number.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I got enough to get started with, but still have a couple of questions:

1. What data are you given? I see two tabs, M, K01 and Routing and Parts List. Am I to read the routing and generate the parts list?

2. Also I still have the same questions about the TR-number. I can find two. They seem to be associated with lines N130 and N140. Can I depend on them being on the same line in the text file every time? Which one do you want to use or do you want to use both, separated by a comma?

I just noticed something about the data you sent me: the part numbers do not match the directories you sent me. There are over 5,000 part numbers, and 9 directories. None of the 9 directories have a corresponding part number. I will doctor up your data so there are matches.

Which leads to question number 3: what do you want to do if I can't find a directory for a part number? Do you want a stop-the-program warning message? Do you want me to silently pass over it? Do you want me to write it to a log?

I take back what I said about the directory mismatches. The Part numbers use dashes, the directories use underscores. I can work with this.

One more question. You have duplicate part numbers. Do you want them repeated or do you want a unique list of parts?

I got ahead of you and assumed that you wanted to use the routing sheet. Attached is a file that does that. It will give you an idea of what things will look like at least. I can change my workbook to look at the parts list.

Before I go further, if you can tell me how you fill in the workbook now, I will try to make the code match your procedure. For example, I can arrange so that you can copy / paste the routing on the routing sheet, press a button and the parts list and the lookups for the TR-numbers will be generated automatically. I can handle the duplicates caused by the suffixes on the routing sheet.

Some more notes: I do have enough data to work with. I pared it down to those parts with a matching directory and kept a few that didn't for the purposes of testing. At the moment, I leave a part with a missing directory blank and log it in a separate log file. I can display this information on the main display.

It does not matter where the TR_numbers are in the file, but I did notice that in the data you provided, most have line numbers starting with N010 or something like that. On these the TR-numbers are on rows 12 and 13 (If I recall correctly). There is one file (1006030_313.WPD) that does not have the line numbers and in this file the TR-numbers are in different rows. I am a bit uncomfortable with making this the means by which I find TR-numbers. I don't know if I have all the patterns down.

There is a Control Panel Sheet. This is where you tell the program the directory name that contains the WPD directories.

P.S. I did a timing test. It took 4 seconds to process 12 part numbers. At this rate it would take a bit under 30 minutes to do 5,000.

OK, one more question then. Do you have control of the access database? We can push the automation back a step further. You can import the data directly from the access database without the need of copy and paste. Also you can set up the windows task scheduler to run this for you automatically in the early hours of the morning so everything is already run and waiting for you when you get in . These things are not as difficult to do as they sound. However, we don't have to do them now. I'll save that as a "teaser."

I can keep the dash in the parts list for the purposes of display. I'll do the translation in the code.

I will take it from the copy / paste of the routing - I should be able to give you a prototype later today.

I plan on starting form scratch. I will document the macro profusely and I'll answer any questions you may have.

Let's see if this meets your needs or if I have to change anything.

Here is the prototype.

The Control Panel sheet is sort of a checklist.

Cell B2 contains the path to the parent directory containing the WPD directories.

Clicking on the Clear Routing button clears the Excel Table on the M, K01 and Routing sheet. All you will need to do is copy and paste the routing into cell A2 on this sheet.

There is a helper column (I) that has the formula =MATCH([@Part],[Part],0) = ROW()-1. This formula is True only for the first occurrence of the part number in column H. I use this in the code. I only process Trues.

Column I can be hidden. Since this is an excel table, the formulas in columns H & I are “remembered” and copied down automatically for each row pasted into the table. You will also notice that the formulas are defined in terms of column headers instead of cryptic column letters. All this is part of working with tables.

Here is some more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

Excel Tables also make the VBA coding a lot easier.

Clicking on the Make Parts List Button on the Control Panel runs the program.

The program does several things.

Internally, it makes the WPD sub-directory name from the Part Number

It checks to see if this directory exists – if it does not, it writes out “N/A – no directory.”

Then it checks to see if the file, MPF1.MPF exists in this directory – if it does not, it writes out “N/A – no file.”

If it passes both these tests, it opens the file and looks for the string “ZOA” – I am assuming that the TR numbers are on the two rows after this string.

If it cannot find this string, it returns a row number of zero and the program writes out “N/A – not found.”

Finally if everything passes, it gets the next two lines and prints out the TR-numbers in columns B & D.

As a bonus I record the start and end times in cells B11 and B12 and the elapsed time in cell B13.

"1002690-305" should convert to 1002690_305 inside the program.

I'll need a bit more explanation on how you get 12690305 from 1002690_305. Should I be removing the second and third character from each part number?

dflak,
I have been using this macro and it is brilliant.

Column H is the first 11 characters of column A.
however the mpf files are only saved in directories that are in odd numbers.

ie it would find part number 1021487-387 and it would return a directory of TR7 etc, however it will not return a directory for 1021487-388. is there anyway to add in a -1 to the code if the last number is even? I tried doing this manually however itll will not return a value as the part is no a number due to the "-".


Also if I needed to make changes to the remembered formula in the table where can I find this info?
thanks

OK, I'll make that update later today.

I think I have it this time.

This version should take 1 off the part number if the part number is even. So 1002690-306 will be shown as 1002690-306 and the overall part number will be 12690306, but it will look for directory 1002690-305.WPD.

As for the tables. On the routing sheet, there are formulas in Columns H and I. If you add new part numbers to the end of the table, these formulas will be copied down automatically. If you clear the contents of the table (rows 2 to the end) the formulas will be "remembered" and when you paste in new data into columns A:G, they will be applied automatically.

On the Parts List table, there are no formulas. Everything is filled in by the program.

This version is totally untested.

Unfortunately I've already purged the test data I had for this. However, all is not lost. I'll need to ask some more questions.

The part of the code you pointed out adds a "-1" to the expected directory name if the last three digits of the part number in column A are even. The part that tests for the existence of the directory comes a couple of rows later.

So now you are telling me that the directory name could be 1002690-305 or it could be 12690305 or in the case of an even number: 1002690-306-1 or 12690306-1.

If this is the case, then I'll have to add a bit of code between the two pieces of existing code to test if either of these directory names exist and use the proper one. I'll push on in this direction.

If I am going to be able to test this, I will need some sample data like I had before.