A Way to Query,(x).Xls files in (x)subfolders

Hi

You could do something like

Please Login or Register  to view this content.

If you want to include this in your extraction, then the

Please Login or Register  to view this content.

will give you the full path, and you can include that into your

Please Login or Register  to view this content.

string. Then you will have to udpate your output cell to be something like

Please Login or Register  to view this content.

have fun....


rylo

Hello bdb1974,

The following macro will open a folder and then open each sub-folder. Each sub-folder will examined for files with names like "BOM*.xls", where the asterisk is a wildcard character. This will not examine sub-folders of sub-folders.

The macro is incomplete as you did not give more details about what you were searching for in column "A". Provide me with the details and I will add that to the macro for you.

Also, I have a few other questions. If the BOM worksheet names vary from file to file, will the worksheet always be in the same position: first sheet, second, etc. or will it be the same as the workbook name, like "BOM1"? If the worksheet name doesn't change then what are you using?

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Ultimately this is what I'd like to have happen.
I will be searching for values: between 360060 & 366447 in Column A, in a BOM*.xls
If found. I would would like
From the following file paths:
L:\Elec Dept Projects\Preliminary\*\BOM*.xls
L:\Elec Dept Projects\Pending\*\BOM*.xls
L:\Elec Dept Projects\Hold\*\BOM*.xls
L:\Elec Dept Projects\Released For Construction\*\BOM*.xls
L:\Elec Dept Projects\Complete\*\BOM*.xls
have the subfolder names: Preliminary,Pending,Hold,Released For Construction, or Complete to be listed in column L. and * (being a wildcard subfolder) be COLOR="Blue"]hyperlink listed[/COLOR] in column N. and the first 3 characters of *(being a wildcard subfolder), be listed in column M. in my workbook "TransformerAllocations" Worksheet"Sheet2" Starting with Row 9 & down,have if the Value found in BOM*.xls matches value in Column A of Worksheet"Sheet2. then be listed in the next available row (where cells in columns L,M,N of that row has no value or data in them). ot the row where th corresponding value in column A is listed. Or it can be set up to list the seached value in the next blank cell in column A.
I know this is a lot to ask, but, it would be great if some conditions could be added to the search and list macro. If the BOM*.xls is in directory Complete, then remove contents from columns A, L,M,N,O if subfolder is already listed in Sheet2 if found where the value on the same Row has value of Preliminary,Pending,Hold,Released,or Issued in Column L.

If the BOM*.xls is in directory,Released For Construction, then remove contents from columns A, L,M,N,O if subfolder is already listed in Sheet2 if found where the value on the same Row has value of Preliminary,Pending,Hold,in Column L.

If the BOM*.xls is in directory,Hold,then remove contents from columns A, L,M,N,O if subfolder is already listed in Sheet2 if found where the value on the same Row has value of Preliminary,Pending,in Column L.

If the BOM*.xls is in directory,Pending,then remove contents from columns A, L,M,N,O if subfolder is already listed in Sheet2 if found where the value on the same Row has value of Preliminary,in Column L.

Again, I know this is alot to ask for, but this would be a significant help. And hopefully
seeing a working code will help me improve my VBA macro skills which are currently at
a newbie level. Thanks again for all your help.

I have tried to use add /merge parts of this macro which adds hyperlinks
to Rylo's provided code. But have not had success, I keep getting syntax
errors,then reworked and getting invalid procedure or call argument.
I'd rather you guys work on my ultimate code wish.
But if not, then a fix to the code below would also be appreciated.
I've also attached a working example of a hyplink listing workbook.
I don't know if the code can be incorporated into making what I ultimately
looking for or not,but hoping it may help you in your efforts to help me.

Thanks again for the help.

Please Login or Register  to view this content.

Hi

Ok, starting small.

1) I created directories (a) C:\Elec Dept Projects\Preliminary\suba and C:\Elec Dept Projects\Preliminary\subb
2) Created file C:\Elec Dept Projects\Preliminary\subb\BOMa.xls
3) In your workbook hyperlinks to projects folder.xls, added the code

Please Login or Register  to view this content.

4) Selected sheet1 of that file and ran the code.

Is this output in columns L:N what you want? If not, then describe how it should be changed.

5) In [hyperlinks]sheet2!A8:A11 I have the values
ID
aaa
bbb
ccc

6) In [boma.xls]sheet1!A1:A4 I have the values
ID
ccc

Using those items, how / where / what do you want to display on sheet2 of hyperlinks. I got a bit lost on your description. How about, if the first part is OK, then you show the output you would expect to see. If I haven't got the file details / structures right to meet your situation, can you update your files and attach.


rylo

Rylo,

Thanks for starting on your assisting my needs.
I inserted the code you created and tried running it.
I'm getting a 400 error code when running the macro.
Also, It appears to be automatically deleting all the contents.
It should only delete the contents of the cells only if the subfolders have
been manually moved to the next stage of completions.
stage of completion/status changes is as follows:
preliminary>pending<or>hold>invoiced>released for construction>complete

And this,
deletion should happen to a row where the status is a past status and
should not delete the most current status list of the subfolder.
only when I run the macro to update.
The subfolders will be manually moved to each parent folder when the previous status has been completed.



I've attached a Rar files w/the folders and some example job folders with BOM's inside. The code should check the BOM for Column A. and then list
as previous describes in my last post.
Theres a folder called: Materials.
This hold the spreadsheet called:TRANSFORMER ALLOCATIONS.xls
This is where the code will be executed, from sheet2.
I've also placed another file in the Materials folder called: Book4.xls
It shows the different values in columnA the code should check for in the BOM.xl's.
I hope all this makes sense.
Again,
thanks for all your help.

Rylo,

here's an updated file of book4.xls showing a couple rows
of how the hyperlinks should be.

thanks,
BDB

Hi

Lets step back even further. I want to build a consistent common structure that we can both use.

1) Using a PC that is not attached to a lan, so only use the C: drive for reference.
2) What is the fixed part of the path. In your original post, you referred to things like L:\Elec Dept Projects\Preliminary\*\BOM*.xls
3) The path structure in this file doesn't seem to resemble that, but this could be a result of (a) not having a lan and (b) where the file sits when opened from the forum
4) Using book4.xls as your macro file, where would it sit. If this is a fixed position and is relative to the BOM files, then it may be that the path of this file can be used as a base.
5) Create a BOM file, and show where it should reside. I know that this should be the 3rd hyperlink, but I just want to make sure. Also include in this file 2 item reference numbers. Make the first one 360060 and the second one 360062. That way I can at least move to getting the hyperlinks working before I worry about the movement from one structure to the next.

rylo

Ryo, I tried posting this file for you yesterday. I did not realize it did not upload due to original being a rar file which, I a invalid file type for this board.

I think this zip will help alot. It contain the directory structure with some example BOM files w/in the sub-folders.

As you mentioned.
L:\Elec Dept Projects\Preliminary\*\BOM*.xls
is the correct path.
But we can use
C:\Elec Dept Projects\Preliminary\*\BOM*.xls (for referencing)
That's an easy fix.
Currently I'm at home so if I want to try it out, I will be using my C:/ drive
at work, it will be L:/ drive.

As you will see, the fixed paths will be:
L:\Elec Dept Projects\Preliminary\
L:\Elec Dept Projects\Pending\
L:\Elec Dept Projects\Hold\
L:\Elec Dept Projects\Released For Construction\
L:\Elec Dept Projects\Complete\
These paths will not change. What will change is where the subfolders and the BOM files within will reside.
Example: (they will be created in:Preliminary
L:\Elec Dept Projects\Preliminary\4GZ University Park\BOM2.xls
It will eventually be moved to:Pending
L:\Elec Dept Projects\Pending\4GZ University Park\BOM2.xls
It may go on hold:
L:\Elec Dept Projects\Hold\4GZ University Park\BOM2.xls
then it will move to:Released For Construction
L:\Elec Dept Projects\Released For Construction\4GZ University Park\BOM2.xls
and eventually to the final complete folder:
L:\Elec Dept Projects\Complete\4GZ University Park\BOM2.xls

Let say when :4GZ University Park\BOM2.xls get's moved from Preliminary to Pending. I don't want it to remain listed in the Preliminary. When I run the
macro, I want it to update (removed the Preliminary hyperlink listing and only
show the new revised, Pending hyperlink listing.) to show current stutus of the subfolder.
Also, don't always assume files will move from:
preliminary>pending<or>hold>invoiced>released for construction>complete

They could move from:
PRELIMINARY> RELEASED FOR CONSTRUCTION

I hope this helps.

BDB

Hi Rylo,

First, I want to thank you for everything you have done.
I tried it out last night at home. I have to say, I was very elated, to see the
the code run and have it do pretty much everything that I was hoping for it to do. I love the way it added the hyperlinks to the columns, I loved the
way it searched the BOM's. One thing I noticed however, that it was a little
slow querrying the folders. I did notice that it was listing the same BOM /JOB folder, on 4 sequencial rows.

I wanted to wait till I was at work and ran the code before, I responded.
I'm sad to say, that I have now tried running the code, w/o any luck.
For some reason it does not seem to be querrying "searching" nor is it listing any job folders. It only cleared the cells.
I changed the file paths from:

C:\Elec Dept Projects\ to
L:\Elec Dept Projects\

It looks like there were only two areas within the code that I had to change the path.

Also, I also tried running a copy of the file from:
L:\Elec Dept Projects\MATERIALS\
and
C:\Documents and Settings\BDB\My Document
Basically for each location, it did not work.

As far as the machine I'm using at work:
Gateway PC, Pentium(R)4 CPU, 3.4 GHz, 3.40 GHz, 0.99 GB of RAM
OS= Microsoft Windows XP Professional Version 2002, SP2

MSExcel : MS Office Excel 2003 (11.8231.8221) SP3
Part of the MS Office Standard Edition 2003

Again, thanks for all your help.

Regards,
BDB
Of, BTW, I was wondering if it was necessary to Querry to Always Querry the Complete Folder. As once the project folder get moved into the Complete
folder, It will be static. It will only have to Querry, for newly moved Folders.
I thought this might speed up the search. It's probably a good thing for it to
querry the BOM's through every Status:
preliminary>pending<or>hold>invoiced>released for construction>complete
...because, BOM's could get changed/updated until it's complete.
But, I'm wondering, if the search could be made to happen by recognizing whether or not the BOM filesize,date created was modified.
Of course this would add/change the coding so that the all clear function
would not delete the lines that were not going to be updated. And, I don't even know if this would make the querry run faster. But, I do know, if it did
not querry the complete folder, which will continuously to accumulate job folders, then that would probably be a big
reduction in time consumption.

Thanks,
BDB

Hi

1) I only had it set up to run from the C: drive in this version. While there is only 1 place to change the path, it does refer to a fixed string to remove the path prefix (c:\elec dept projects). This version does not account for other drive combinations.

2) If you tried running it from another location, then when it tried to do the substitute for c:\..., then it would have errored out. As the error handler would have taken it right to the end, it would appear that it was not working.....

3) Where is the searching directory? It wasn't on the example setup you sent, and I was working on getting that example going properly. It should have picked it up as long as it was at or below the c:\elec dept projects directory?

4) I can stop it working through the completed directory, but how will you know if the ID is just not there, or in the completed? If you have a process to cover this off, great, I'll just stop it searching completed. Or do you want to change things so that if I get an entry with completed in the path, it doesn't clear out that item? What happens if it is item 3 in the list? It won't align with the ID.

5) Can you give me an example of where it was listing the same BOM / Job on 4 sequential rows.

6) Does this file with the code always sit in the materials directory in the same drive / location as the data paths? If so, then I can use this as part of the dynamic location process. And will the materials directory always be directly under elec dept projects?

Again, I've only got this set up for the C drive at this stage, so it will fail on any other location.

rylo

1) I only had it set up to run from the C: drive in this version. While there is only 1 place to change the path, it does refer to a fixed string to remove the path prefix (c:\elec dept projects). This version does not account for other drive combinations.

Answer:
I thought maybe both paths had to be changed,
.LookIn = "c:\elec dept projects"
and also I thought,
Ltxtdisp = WorksheetFunction.Substitute(Cells(outrow, "L").Value, "C:\Elec Dept Projects\", "")
Ltxtdisp = WorksheetFunction.Substitute(Cells(outrow, "L")
.Value, "C:\Documents and Settings\brianb\My Documents\Excel Functions\Examples\Elec Dept Projects\

2) If you tried running it from another location, then when it tried to do the substitute for c:\..., then it would have errored out. As the error handler would have taken it right to the end, it would appear that it was not working.....

Answer: Yep, I think that's what was happening. I stepped through the code
and it didn't error... but just would not give me any results.

3) Where is the searching directory? It wasn't on the example setup you sent, and I was working on getting that example going properly. It should have picked it up as long as it was at or below the c:\elec dept projects directory?

Answer:
Yes,It will be ran from Folder Materials
L:\Elec Dept Projects\Materials\TRANSFORMER ALLOCATIONS.xls
L=Work , GLOBAL , Network Drive, file system: NWFS

Or I was thinking from
C:\Documents and Settings\bdb\My Documents
C=Home or Work PC Local Drive
This way I will be able to maintain control of the running the updates
and protect the codes.

It will always be searching:
L:\Elec Dept Projects\Preliminary\
L:\Elec Dept Projects\Pending\
L:\Elec Dept Projects\Hold\
L:\Elec Dept Projects\Released For Construction\
L:\Elec Dept Projects\Complete\


4) I can stop it working through the completed directory, but how will you know if the ID is just not there, or in the completed? If you have a process to cover this off, great, I'll just stop it searching completed. Or do you want to change things so that if I get an entry with completed in the path, it doesn't clear out that item? What happens if it is item 3 in the list? It won't align with the ID.

Answer: This is just a thought about Item#3 listed above, Instead of worrying
aligning with ID( values in column A), just have it list the ID values found in the BOM to Column A. Then I can sort all the common values, in column A
with a list box (as I already have).


5) Can you give me an example of where it was listing the same BOM / Job on 4 sequential rows.

Answer: I'll have to run the code again from home to be more definitive.
I'll be there in about an hour from now.

6) Does this file with the code always sit in the materials directory in the same drive / location as the data paths?
Answer: Yes,it could reside in the Materials directory on the L: drive, but as I stated above in #3, I may want to run it from
my local machines drive to protect the code and have control over the updates taking place.

If so, then I can use this as part of the dynamic location process.

And will the materials directory always be directly under elec dept projects?

Answer: Yes


Rylo, there is one more value I'd like to have listed from the querry.
For every value found in column A of the BOM's, have the value found
in Column D of the corresponding row, be listed in column O along side L-M
of the results sheet. Basically this column shows the quantity of Item.

If this could all happen, I will be jumping with joy for everyday now on.

Thanks again for helping.
BDB

Hi

1) If you are always going to action L:\Elec Dept Projects, then it would be simple to set it up to always action this directory regardless of where the update file is located. Confirm that this is the only location to action and I'll work accordingly. Also, there is a case sensitivity issue. So I'll either need the exact casing of the paths, and they can't change, or I can make the whole thing on the output in UPPER case to get around the problems. How do you want to go?

3) I misinterpreted. I thought searching was another directory.

4) This is a bit of a change of direction. I was looking for the IDs in the BOM files, but if you want to just search the files and bring back all the IDs found, then this would be OK. I can then ignore the completed directory.

5) OK, I'll wait.

6) Depends on the result of (1). If you are only going to action the 1 directory / path, then it doesn't matter where this file resides.

7) Shouldn't be a problem. I'll see how it goes when I get responses to the items above.

Hi

1) If you are always going to action L:\Elec Dept Projects, then it would be simple to set it up to always action this directory regardless of where the update file is located. Confirm that this is the only location to action and I'll work accordingly. Also, there is a case sensitivity issue. So I'll either need the exact casing of the paths, and they can't change, or I can make the whole thing on the output in UPPER case to get around the problems. How do you want to go?
[COLOR="royalblue"]
This is the exact casing of the paths:
L:\Elec Dept Projects\PRELIMINARY\4SR San Gabriel Center Phase 3 UG
L:\Elec Dept Projects\PENDING\4XD Sun City Old Amenity Center Modifications
L:\Elec Dept Projects\INVOICED\4QK Woodgate Bldg
L:\Elec Dept Projects\HOLD\4KS Krystals Restaurant Developer Hold
L:\Elec Dept Projects\COMPLETED\4OB GT10 25Kv Conversion-Sect 2 of 2
L:\Elec Dept Projects\ISSUED\

If UPPER casing works on the complete path than it's OK by me for you to use. But, I do not have the authority to change the casing of the paths.
WYSIWYG.
Again,
Only 1 parent folder:Elec Dept Projects (First Letter CAPPED other,small w/spaces.)
These 6 subfolders, will need to searched. All will be CAPPED.


3) I misinterpreted. I thought searching was another directory.

NP

4) This is a bit of a change of direction. I was looking for the IDs in the BOM files, but if you want to just search the files and bring back all the IDs found, then this would be OK. I can then ignore the completed directory.


Ok, This is very important, I need it to querry the ID's listed in ColumnA.
The BOM's will contain ID's that I do not want to have listed. The ID's is ColumnA in this way will be a controller (allowing me to add/remove the
ID's it Querry's for in the BOM's. I'm thinking this way I can also use
the macro later to control other numbers on another spreadsheet.

I also, want to inform you the BOM's maybe named as *BOM.xls or BOM*.xls
Please have it querry both ways.

Also, please have search the value found in BOM's, Column D of the corresponding ID row, be listed in column O along side the results in L,M,N
of the results sheet.

Also, I was wrong about it listing more than once, I ran it again last night.
What it was doing, was if a job folder had 2 BOM files, It checked both for the
ID, and listed both if the ID was found in both of them. Bascially working like a charm.

I'm thinking It maybe better to leave the Completed folder off the query search and maybe just have a 2nd macro to just search the COMPLETED directory. Then the results coulb be posted to a separate worksheet.
This will reduce the search time significantly in regards to keeping it in
the main macro script.
5) OK, I'll wait.

6) Depends on the result of (1). If you are only going to action the 1 directory / path, then it doesn't matter where this file resides.

7) Shouldn't be a problem. I'll see how it goes when I get responses to the items above.

I hoping this answers all your questions.
I'm really excited and can't wait to see it in action.
Thanks again for your help.

BDB.

Ok, I've just tried running it from both C:/ and L:/
The error box popped up with the following msg:
You have meet a problem. Contact your system admin. Error Number:-2147467259



This happened while trying to run both sub, AAA,& CCC

I sure hope you know what that code means. Because I don't.


Can you resolve this?

Regards,

BDB

Hi

Turn off the error trapping and see where it is falling over. Only run things on the C drive for the moment. You shouldn't have had any trouble at all with ccc so maybe start with that one first.

Did you find the spreadsheet where there was an entry in row 1 and clear it out? If not, then please do so (or at least change the name of that spreadsheet so it isn't being actioned).


rylo

Hi

In the code there is a ine

Please Login or Register  to view this content.

Just comment out that line and if it comes to an error, then you can debug to the line that is giving the problem.

rylo

I did what you instructed, and found it pointed to the following line:

-> rs.Open "select * from [sheet1$] where F1 = " & ce.Value, cn, 3, 3

Hi

I'm pretty sure that is the error on that you get on the file that has an item in row 1. Do the same thing, and when it errors, go to the immediate window and go ? fs.foundfiles(i) then press enter. This will show which file is giving the error. Have a look at that file and see if there is an entry in the first row. If so, then remove that line and see if the program will continue. I think it was file C:\Elec Dept Projects\subb\1YX - CooperSt.Prj. - PizzaHutt\BOma.xls in the example set you gave me.

How did ccc go? Did it error out? This shouldn't have been a problem in that code.

rylo

Hi

This may sound silly, but the sheet is called sheet1 in your files isn't it????


rylo

Yes, Sheet1, Sheet2, and Sheet3.

Hi

Is this happening on the first entry? Or is it happening after it has done a few?

Are you the only one that will be running this script, or is it going to be handed to others?

rylo

Hi

As there seems to be some problem with the sql approach, here's another way. It uses sheet3 as an iterim step so that sheet needs to be in the file.

See how this goes.

rylo

Please Login or Register  to view this content.

Hi

Just did a quick read and I'll have to come back and review in more detail. But just quickly.

1) The code should be outputting to sheet3 (which it seems to be doing) then updating sheet1 in the correct format. Are you saying that this part is not actioning properly? If so, then where is it going awry.

2) How about a YES/NO message box to determine if you want to include the Completed items in the run instead of separate code?

3) Conditional Formatting: You can only use 3 conditional formats (apart from the base) but you have more than 3 status codes. Why not have the code format the cell based as part of the action?

rylo

Hi

1) Not sure why it is doing things correctly on sheet3, but not bringing it across properly onto sheet1.

2) I've changed a few things - refer later

3) I'll leave the formatting for you.

4) The code should be run from general modules not sheet modules. This could be causing some of your problems.

5) I've changed Released for Construction so it shows as Released.

6) I've managed to get the directory modified date and put it in columm F of the working sheet.

7) Something else I changed on my files, but don't recall specifically mentioning. There can only be 1 appearance of the id in column A. Have a look at the attached. I've removed the additional codes that you had hidden with formatting.

8) Have a look at the attached file. I've changed things so there is (a) a non complete sheet, (b) a complete sheet and (c) a working sheet. There are now 2 codes: ActionNonCompleted and ActionCompleted. These are both in Module 1. I've left the other codes in Module 2 just for reference. I store this file in C:\Elec Dept Projects\Materials, but that shouldn't matter. Just try storing it there to see if you can make things work properly.

Run the codes and see if things finally work properly.

Now for the other things you mentioned. I've got the dates in the working sheet so you should be able to do things with that data if required. Given the number of different options you want to cover, I'll leave that for you to work out. If this code is running properly, then you should be able to create a sheet that will have both complete and non complete on the one sheet. You can use the date to determine if you want to bring it in or not, or create some more variations for the different date combinations that you want.

As for the "user" views, it may pay to have this file as the working file, then create user value paste workbooks that don't have any formulas / codes or working data.

Let me know if the code works OK, and if not, then where you have it, and what falls over. Perhaps add a file with your output in value paste form so I can see what it is producing.

rylo

Rylo, Great work! I like the names for the worksheets. They're very appropriate. Thanks for adding the date column to the working sheet.
This can be very beneficial in sorting data. I think things are really improving.
But, I'm still seeing the issue with the display of the values in columns:L-N,
or last just say was..... Earlier today,our Global drive was down. So, I only
had my work PC to test this lastest version. At first, I was relecutant to
put the test folder on the C:\ as a parent folder. ie: C:\Elec Dept Projects.
I have been using or testing from within MyDocuments folder. This was the
root cause of the following problem, that I mentioned before and showing below:

.... and I think I know why, but I maybe wrong.
Yourset the script to read the directory paths from the left, and to go
2 folders deep. C:/folder1/subfolder1/*bom*.xls
(C:\Elec Dept Projects\*jobfolder*\*bom*.xls)

I've setup the test folder in My Documents directory. Therefore the the
folder depth has increased.

C:\Documents and Settings\bdb\My Documents\Elec Dept Projects\**\*\*BOM.xls

where :
** = the Status Folders
* = the Project Folders

Column L , displays:C:\Documents and Settings\brianb

Column M:,displays: My

Column N, displays:My Documents

Another minor issue I've fixed is:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Once, I've moved the test folder up to C:\, "MAGIC!!!!!"

Since then, the global drives have come back up.
I've even tested the NONCOMPLETES code , on the L: drive, an and its functioning, beautifully.

But, I've run into a snag, testing the COMPLETED code. I was getting an error when trying to action it.

I thought maybe, I could reverse,engineer the NONCOMPLETE code to
stop the search for all the NONCOMPLETE folders, and just leave the COMPLETES. Anyways, I've started with just making one change.
Before, I went any further, I just replaced the COMPLETED w/

Please Login or Register  to view this content.

I, pretty sure this will work, I've also changed it to Activate:
Sheets("Completed").Activate

Once, I made the changes,and ran the macro, it started actioning,
but,now,since I've started the macro, it's only actioned 37 of 200 files.
I was wondering if this macro will hang if another user has a folder opened
or an open *.BOM* file? Anyhow, will the following change below work?
Can I nestle a string of folders, for one If instruction?

Please Login or Register  to view this content.

Also, back to the issues, with how it seeks out the path and assigns the
values to cells L-M. Can it possibly be setup to read from the RIGHT instead of the LEFT, therfore, if I want to test from:

C:\Documents and Settings\bdb\My Documents\Elec Dept Projects\**\*\*BOM.xls

it can list starting on the right with \*\, and put first 3 digit of \*\ in Column M. then, \**\ for column L.

I know, I said, I told you that the L:\Elec Dept Projects will never move.
But, I didn't plan on testing updates from another drive/ or from within a
subfolder.

I'll leave this totally up to you. Right, now, I'm totally happy with everything that you have done for me so far. I couldn't be more pleased. Thanks again

and

Happy New YEAR to you !!!!!!!!!

BDB

....C'ya in 2009

BDB

Glad that this thing is finally starting to work for you.

1) Location - earlier on we had a discussion on where this was going to action and you advised it would be in L:\elec dept projects. As this was going to be fixed, I left the fixed numbers to determine the subdirectories. For testing, I put in at the same place in C. Hence I've always referred to that fixed location position and not had it nested any further, or tried to have code that will have to work out the layers. As the final position was not going to change, it was much easier to code.

2) the change for

Please Login or Register  to view this content.

In the BOM files you put in your example, the "whatever this number represents" is in column E (ie 5). Is it really in column D in your real files? I've got nothing in column D so to me this would just bring back a blank cell.

3) The completed code runs OK or me. Really the only difference should be the output file and the INstr test. If it is 0, then it is a "non complete" directory. If it is >0 then it is a completed directory. INSTR finds the position of a string in a larger string. Can you advise what the error was with the code? If we can get both a COMPLETED and NONCOMPLETED working properly on your machine, it should give you a better chance to modify when you want to start actioning things for different dates.

Happy new year and I'm pretty sure we should get this licked before the year is out!!!

rylo

Hi Rylo,

It really looks like everything is running great. I'm still playing around with the
format and seeing what all I can do. As, you stated Column E is really D. These are quantity values. I was able to find and fix that by myself. Thanks.

Examing the working sheet, it looks like the code basically pulls everything off
the BOM's and put's it all on the "Working Sheet". That's hold a good amount of content. That brings ups some nice possibilities. I do have a few questions about the "Working Sheet". But first, now since the code is
running, and doing basically what I first set out for it to, should I close
this Thread as "SOLVED" and start a new one. Or just continue to fine tune
what, I'd like to have the sheet ultimately set up to run.

Here's my questions:

1. How come does it list the file paths in 3 columns (B,C,D). Could it not list
just once and still get the information for the output sheets?

2. I realized the other information on the BOM' would be very useful. How hard would it be to add instructions to the code to output the other information to the working sheet? If possible I'd like to shift the Information from (B,C,D) to (I,J,K) on the Working Sheet keeping the data ouput the same to the COMPLETED and NONCOMPLETED sheets. Then Add B,C,E,F of the BOM to B,C,D,G of the Working Sheet respectively?

3. I think I can then probably use some formulas to place data collected
to other Sheets with other ID #'s

4. So, If I use a formuala something like:

Please Login or Register  to view this content.

What can I use to transfer the hyperlinks?


Again, I can't thank you enough for all that you have done.

Thanks,

BDB

Hi

For the moment, keep this one going. That way I don't have to travel too far if I want to refer back.

1) Yep you could. When I started this approach, I just cannibalised some of the existing code and it output the data 3 times. So you could have it there only once, and copy it across to the output sheet 3 times.

2) Not too hard at all. Here comes the learning part for you. Have a go your self and if (when???) you get stuck then ask questions. ha ha ha ...

3) You could either use formula or have the code bring back the values. Up to you...

4) Not sure what you are asking in this point. The formula brings back the same result regardless. I just did a quick cut and paste on a hyperlink and it seemed to move OK. Or is there more to the question that I'm just not getting.

rylo

BDB

1) Do you mean grouping the sheets? If so, then do it manually and record a macro to see get the syntax. This is a pretty easy way to get code for something like that... If you don't mean grouping, then I don't follow. Do you want to do the same actions on 3 sheets, but they have different structures?

2) Which sheet has the blank? Where is it on that sheet? What do you want to copy??? From where? What are the rules for selection, copying, pasting..... and so on.

rylo

Ok, Rylo, I need some more help please. I've been busting my brains out and
can't seem to the next step to the page to function.
Here's what's new.

I've added another sheet called "Inventory" to the booklet.
It has numbers in column A ,that are identical to what's in column A on
the "Non_Complete" sheet "Completed" sheet except they have a ,03-,
in front of the ID#'s. example: 03-360471. This "Inventory" sheet has values in both columns B,Y and Z. What I want is, If the last 6 numbers.
example: 03-360471 matches the ID # in column A
of the Noncompleted , the, have the value in column Y be placed into
column Q. and the value from Z. be placed into the column U. And for
the value B in the "Inventory" Sheet, have it be placed into column AB of the
Non_Completed and Completed sheets. I've tried to setup another macro
using the code you've already setup for the main function of the booklet.
I'm really struggling getting it to do what I need it to. Can it be set up
also, to only clear only the cells with the new values within the Q , U and not
clearing the every cell within the two columns?

This will be a big huge stress relief, If you could set this up for me.

Thanks,

BDB.

Hi

See if this gets you going. It only works on non-completed at the moment. I'm also not sure of just how Inventory is set up, so you can see what I've used as my base.

rylo

Thanks Rylo,

I'll test it out tomorrow. But, I've just just up my booklet w/all inventory
items. which is over 6000 rows. It's taking a really long time for it to
search each line and compare to ID's within the BOM's. Is there a way to set it up so that once it pulls all the info from the BOM's to the "Working" sheet that it uses the ID's found to list the data to the NONCompleted and
Completed sheets? In other words action off the Working sheet instead
of actioning off the column A of the NonCompleted sheet. I think this
would speed up the search tremendously. What do you think? Am I correct
in my thinking?

BDB

Hi

Time for a step back and think about your entire process and what you really want to achieve at the end.

1) Do you have more data from other sources that you will want to include? Is so, then detail it and where it should go.
2) Can it all be put onto the working sheet?
3) Can it then be transferred from the working sheet to the data sheets?
4) Are you likely to have items in the BOM sheets that aren't on the output sheets? If so, do you want to append them?

What I'm thinking is that you should set up the working sheets with all the data you want, then transfer to the completed / non completed from the working sheet. So you would create the working sheet with all the data from the bom sheets, then bring in the data from the inventory sheet, then data from other sources and so on. Then when you have all the data on the working sheet, you should move it to the output sheets.

rylo

HI

OK, some more questions and requests.

1) Can you put up a copy of your most current working file, with the 4 working sheets etc.
2) Do you want just one piece of code that will action both completed and non-completed in each run through? I thought you wanted separate pieces of code and to run separately, but now I think you want to run the whole thing in one go.
3) Copies of the Inventory and inventory_nec workbooks
4) Yes, the code does run through the IDs on the completed and non completed. Remember that is what we started with. If the BOM sheets are likely to have less data than the output sheets, I'll change the code so that it actions from the working sheets.
5) Do the inventory sheets have more data than the BOM sheets. If so, then I'll only bring in the data for the IDs that have been collected in working1 and working2.
6) Is there a many to one relationship between the inventory data and the BOM data? SO if there are multiple items from the BOM sheets, the same data from the inventory sheets is appended?
7) It may pay to do a full manual output for a couple from the data given just so I can be sure that it is bringing back the right data.
8) Remember that the BOM sheets I have had the data in Column E yet the data you wanted came from Column D. If the base example data has changed, it may pay to redo all the example data so we are working from the same material. Anything I send back will have to be run against that example data.....

think that has it covered, but I'll see when I start working through it all.


rylo

Hi

Change of approach. I've only worked on the completed so far. Have a look at the attached.

1) There are now 4 separate codes to load the 2 inventory sheets and the 2 working sheets.
2) There is now a new actioncompleted code that calls the relevant data load scrips, brings in the descriptions for the items in the working sheet, then processes the working data to the output sheet.
3) This should eventually be broken out further so that when we get the non completed working, it will be able to call code to action both the non completed, and the completed scripts - assuming that I can get it to distinguish the data restriction.

See how the approach goes for you.

rylo

Hi again

Bit more. You now have 2 controlling subs
MainCompeted and MainNonCompleted.

MainCompleted will action all the completed items
MainNonCompleted will action all the non completed and the completed where now() - the date in column F of working_Completed is < 31. I did it this way to cover the time components...

Bit rough but see how things go.

rylo

Hi

1) Completed <31 days - I presume that this is for the non completed run and is OK unless you say otherwise

2) Non completed actioning pending or invoice - this doesn't sound good. Was it actioning them in earlier runs? It was bringing them back OK for me. Can you check the casing on the directories and how it matches the case on the path string.

3) Why don't you build the output sheets entirely on the results of the BOM files? That way you will get all the IDs that are being actioned and don't have to worry about keeping the IDs up to date in the output sheets. The formulas can be added to cover the data returned.

4) How about putting up an example sheet with the formulas and I'll have a look at replicating the output sheets entirely from the BOM data.

Remember, that I'm using the example BOM files that were in your original list, including the directory paths and the casing / spelling on the paths. I've noticed that originally the directory was

complete

but it now seems to have changed to

completed

(not sure of the casing, just working from memory). Point 8 in an earlier post did suggest that you give a complete update to the example files and this would include any change to the directory etc. Also point 7 in the same post suggested you give completely fill out the required results for a couple of IDs from the data in the example area so I can be sure that it is returning exactly what it should. Expand this as per (4) above so I can see all the relevant formulas etc....

rylo

Hi Rylo,

Answers to the following Q's,

1) Completed <31 days - I presume that this is for the non completed run and is OK unless you say otherwise

Yes, It's for the Non Completed output sheet which is also for PRELIMINARY,PENDING,HOLD,RELEASED (RELEASED FOR CONSTRUCTION). It appears, that you have it setup on the Completed output sheet.
2) Non completed actioning pending or invoice - this doesn't sound good. Was it actioning them in earlier runs? It was bringing them back OK for me. Can you check the casing on the directories and how it matches the case on the path string.

My mistake, this was searching the mock setup on C:/ which I had moved the job folders into just the PENDING and INVOICED. I had to change it to search the L: drive. And,it's working right!
3) Why don't you build the output sheets entirely on the results of the BOM files? That way you will get all the IDs that are being actioned and don't have to worry about keeping the IDs up to date in the output sheets. The formulas can be added to cover the data returned.

This sounds great to me and is probably better. I've attached a copy
of the workbook with the formulas that I'm using. You might even have a
solution to one of my formula problems. On the Non_Completed sheet,
in Q&R row, you will see an empty cell with a comment tag ,just below the
QTY. values for each line item. These two cells were to serve as holding
an intial inventory total reference count. A Physical yard inventory count,
which will have to be updated manually. And the System (INCODE) inventory
count, which is now being imported by the Inventory_GUS sheet.

****Ok,Just saw, a problem, what is now being imported needs to be placed
in the cell below the row with the ID. (the cell w/the comment box) because
value in column T is a calculation based on values in P,R&U. If the macro updates the sheet and has changes which affects the Total in P. Then the
count in the System, and Physical Yard count will change and needs to reflect the difference. So the ongoing count will always be formula driven based apon the starting count and the Item ID which changes status from "RELEASED" to "ISSUED".
4) How about putting up an example sheet with the formulas and I'll have a look at replicating the output sheets entirely from the BOM data.

Take a look, I've attached a complete booklet w/formulas.

Remember, that I'm using the example BOM files that were in your original list, including the directory paths and the casing / spelling on the paths. I've noticed that originally the directory was

complete

but it now seems to have changed to

completed

The following is correct:

COMPLETED

(not sure of the casing, just working from memory). Point 8 in an earlier post did suggest that you give a complete update to the example files and this would include any change to the directory etc. Also point 7 in the same post suggested you give completely fill out the required results for a couple of IDs from the data in the example area so I can be sure that it is returning exactly what it should. Expand this as per (4) above so I can see all the relevant formulas etc....

rylo


Hope this covers all.

I know you said its rough and you maybe aware, but when I action the main_Non_Completed, I
get extra job folders posted into the blank rows intended for the first few
ID's listed on the output sheet. Take a look at the output data,I left on
the file I uploaded on yesterdady's first reply post. As, you will see,
All rows for the first 5 ID are completely filled up. I tried to analyze what
maybe happening,but could not come up with any good explanantion.

After the first 5 ID's everything seems to be OK.

Another thing I've noticed, is everytime, I fix some formatting on the output
sheet and run a macro, the formatting, gets taken away. IE: borders created
to distinguish, the rows for each ID group.

Just some tid bit stuff.....

Have a great weekend.

Hi,
I need a li'l bit help as i m new to VBA.
I just wanted to move no of folders to another folder.
I'm not getting how to write VB coding for this.

as i have 20 folders(having name 1...20). In those i want to move half in New Folder"A" and remaining in Folder"B" as such manner written in excel sheet.
Pls Help

//Rj