Poor man’s where-used

[bigdaddy]

I've used Intralink in the past and like its functionality. However, we don't have it where I'm working now. So, in an attempt to try to regain some of the functionality (without the cost), I createdan Excel file to use for the "Where-Used" functionality.


Here's the file and how it works (2008-06-11_105323_Intralink.zip):


1)Enter folder where your files are kept (currently only works for a single folder)


2) Click the button. Excel will create a list of drw and asm files, then go out and open each one and extract the part list data


3) Enter the part number that you want to find "where-used" for and click a button.



Edited by: bigdaddy

 

[cdtoad]

Here is the file

2008-06-11_101959_Intralink.zip

 

[dr_gallup]

Well, it doesn't work for me.

 

[bigdaddy]

dr_gallup, are all your files in a central folder? What issue did you run into?


Did you have "macros enabled" in Excel? If not, you need to open a blank version of Excel first, then go to Tools\Macro\Security and set the level to Low or Medium.

 

[jelston]

So, uhhh, two questions:


1. how does it open more than one file without using Pro/e?


B. will it search in subfolders?

 

[bigdaddy]

jelston,


If you open an assembly or drawing file from Explorer using Word or Wordpad, you can see that there is a mix of readable characters and ASCII or other characters. This Excel file systematically opens each drw and asm file and looks for the characters within those files that represent parts lists. It dumps this information into one of the tabs in the file and uses that to search for in the Where-Used functionality.


Unfortunately, I haven't set it up to do subfolders yet (mainly because we aren't using any), but I might get to that in the future. I've alreadycreated similar files that search subfolders.

 

[jelston]

If you don't have subfolders and everything is in one directory then it's not hard to know "where used" it's in that one folder, or am i oversimplifying (sp?)?

 

[jelston]

No workie fo me eitha

 

[bigdaddy]

jelston,


Sorry, I guess I was using Intralink terminology ("Where-Used"). What this file does is give you a list of all the assemblies or drawings that are usingthe specified part. So, for example, if you were going to rename the part and wanted to make sure that you maintain all the relationships with any assemblies or drawings that were using that part, this file would tell you everywhere that your part shows up. Then, you can bring those into session so that they get updated as well. Otherwise, you have no idea (other than ERP system information).

 

[jelston]

ohhh! Cool! I never used intralink so maybe that part of the problem. Anywho, I have parameters enabled, does Pro/e need to be opened?

 

[bigdaddy]

Pro/E doesn't need to be open. Actually, you don't even need Pro/E to use this. The Excel file will just open and close the files on its own. Be prepared. As it does this, your Excel screen will sort of "flicker" as each file is opened, extracted, and then closed. We have about 16000 files in our folder and to do the whole group can take a while. I usually just let it run and if I need to use Excel in the mean-time, I'll just open up a new session.

 

[dr_gallup]

I did enable macros. The macro starts and I get a divide by zero error. If I click it again I get another error.

 

[bigdaddy]

dr_gallup,


I just did a test with a folder that didn't contain any assemblies or drawings. I got the same DIV/0 error. Sorry. I guess I only tried it with folders that had assemblies and drawings. After you get that error, however, it should fix itself if you run it on a folder that DOES have asms and drws. When you get the dialog that you have shown, click the NO button so that it starts fresh again. That dialog is there as a safeguard in case the process stops in the middle for some reason -- you can just start it again and continue on.


If you already DO have asms or drws in the folder your looking in, then something else is going on.

 

[dr_gallup]

I have hundreds of assemblies & drawings in that directory.

 

[bigdaddy]

I just checked your jpeg again. You need to havea "\" at the end of your file path.

 

[dr_gallup]

OK, that made it run but now I got a whole bunch of these errors:

 

[jelston]

Dude (sorry i just have a problem with calling a guy "big daddy"),

One problem may be the need to unprotect the worksheet in excel (Tools, unprotect, etc)

 

[jeff4136]

Bigdaddy,


Thanks for posting that. I'll have to check it out.
_ _ _ _ _ _


This is my 'ultra poor' man's batch file. ;^)


@echo off
findstr /i /s /m "%1" *.prt* > where_used_%1.txt
findstr /i /s /m "%1" *.asm* >> where_used_%1.txt
findstr /i /s /m "%1" *.drw* >> where_used_%1.txt
echo ====== Start List ======
type where_used_%1.txt
echo ====== End List ======


Paste in a file named, for instance, isrefd.bat in a directory included
in the OS search path environment.


Open a system window in a project directory and type: isrefd my_part

 

[jbuckl]

This is a great thread....


Many project manager types love excel to 'do everything'....


Is there a way to write an excel sheet that associatively interrogates a pro-e assembly file and outputs a bill of material?

 

[bigdaddy]

dr_gallup,


Did some of your files load, but not others? When did you see that error? Tell me a little more about your assemblies or drawings (how many items, other info). I haven't seen that before and can't seem to find any info on it.


Do you have that log file?