Help with looping in subfolders

Hi Matt

_ I lurk through about 30 threads a day learning form the codes i can get working – I could this one... _ So thanks for having a go ! – It works for me and i understand it !!
_
_ But Your implementation puzzles me. Here some notes that might help..

_ 1 ) Note you are 'Using Microsoft Scripting Runtime Dictionary for File System Objects
'-Needs library reference MS Scripting Runtime ( Early Binding ) -
'Tools>>References>>scroll down and check the box next to Microsoft Scripting Runtime

_ 2 ) I do not understand why in
Sub DoOneFolder(FF As Scripting.Folder)
You do this pair of things
Set WB = Workbooks.Open(F.path)
WB.Close
As my code
Code4OpenCloseAllFilesIfChangedSinceLastTime
Does that .

_3 ) The point _2) above gives the clue I think to where you are going wrong. Every time you call
Sub Code4OpenCloseAllFilesIfChangedSinceLastTime()
It does exactly the same thing: Looks through each file in the Folder specified in
Sub Code4OpenCloseAllFilesIfChangedSinceLastTime()
So that makes anything you are doing outside it irrelevant !! Hope that makes sense – it is very difficult to explain from this distance!!

_ Anyways my start point to get it all up and running correctly was ::....
_ The Demo code below is in the master file, ( my File “wbCodes.xlsm” ) in a Folder of full path “F:\ExcelForum\wbSheetMaker”
_ and in that Folder i have a Jumble of Files and Folders

Using Excel 2007

FOLDER
wbSheetMaker	_FILES
	wbCodes.xlsm
	(August).xlsx
	~$wbCodes.xlsm
	Mappe2.xlsx
	Mappe14.xlsm
	(August).xlsm
	wbCodes.xlsx
	CresssieFiles.JPG
	_FOLDER
	CressieFolder	_FILE
		Cressie5.xls
		_FOLDERS
		Matt1_2	_FILES
			Cressie2.xls
			Cressie1.xls
		Matt4	_FILE
			Cressie4.xls
		Matt3	_FILE
			Cressie3.xls

Sheet1

_.... The point is that after running the code below you have gone through all Folders and Files of interest. So you can trash all that Dir and Dir(___) crap as it is no longer needed.

Here the code. Try to get it up and running.

Please Login or Register  to view this content.

_ I have also written a full Code which is doing all that you want. But I think before I give you that it would be wise to

_ 1) Make sure you are happy with the working of the Demo Code, as it may be a big step to the Final code in one go.

_ 2 ) I think we have both a flaw in our Date Stamp thinking. If the Date Stamp is in the master File things will not quite work as i think you want. For example, if only some Files have been modified the Date stamp will be updated in the Master File. Then as the code is currently written those other Files which have not been modified will never the less, ( if they are checked after those other initial files), appear to have a different Last date modified compared with the Date Stamp which has just been updated by the code after doing stuff to those other files. Think about it. It is tricky to explain.
I have written the final code so as to update the Date stamp in each file only if that File has things done to it by the macro. As a result of this it was necessary to use the “Perform A Reference” stuff I mentioned in Post ‚2
http://www.excelforum.com/excel-prog...ml#post4199982
This was necessary to prevent necessarily opening Files to check that Date Stamp.

_ .....
Best would be to have a think about that, get the demo code running, and we can take it from there later today or tomorrow when i am back on.

Alan
_ ........................

EDIT: I have in fact rethought and also done a another code for you that puts the Date Stamp in the master File doing a comparison of of the form
If DateLastModified > DateStamp Then .......
_ .... discuss that all with you when you post again

Hi Matt,

Originally Posted by Cressie

........
With the date stamp, my thinking was that the macro would only look at files with a modified date greater than the date stamp, rather than different to the date stamp. Looking at the original code, my understanding was that this line of code:

Please Login or Register  to view this content.

Was doing the former, rather than the latter .....

..
All my codes until now did something like

Please Login or Register  to view this content.

, so these were the <> or not equal to or different to the date stamp case.

But no problem I will post both the 2 new codes I have done for you when I am back at an Excel Computer, later or tomorrow. One I think is the modified date greater than the date stamp one as you originally wanted.
Alan

Hi Matt

So here are the two codes. You will need of course to change Path lengths, File (and possibly sheet names for the first code ) to suit..
You will also want maybe to strip out a lot of ' Comments and Message Boxes finally. - But maybe in development and especially in Debugging they are useful (and maybe to help learn also)

_ I try to summarise as clear as I can:

_ 2 Codes for you

Code 1) Here
http://www.excelforum.com/developmen...ml#post4213824
The fist code does a = comparison with the date stamps so needed to have a date stamp in each File of interest ( the .xls Files ). I prefer this as greater or less than date comparisons can be tricky due to VBA and Excels annoying habit of changing date formats.


_ ...
Code 2) Here
http://www.excelforum.com/developmen...ml#post4213827
The logic for the second code is the following.
Right At the end of the second code The ( only ) Date stamp in the master is updated. Any Sheets requiring updating will then have been done.
This second code does a DateLastModified ( The date shown as the last saved ) for each file greater than Date Stamp comparison of this form
If DateLastModified > DateStamp Then .......

Both codes appear to work at my end. If you got the demo code to work for your actual Files and Folders then these codes should work as well after you do the necessary changes.

Alan

Hi Matt

Originally Posted by Cressie

... the code seems to no longer be looking into the sub folders and doing the requested "Stuff" only to the file that the macro is in. ....

That is very weird!! - There is even a check in the codes to specifically prevent anything being done to the workbook that the code is in!! ( Apart from the Putting the date stamp in at the end )
The codes are still working at my end. ( That is to say checking criteria for, and then when met opening then closing all .xls files in any Folders or Sub Folders. )*****
Maybe check the demo code again, then apply the full codes to the same Folders and files that you had that working with.
Very difficult working at this distance to guess what is happening at your end.

As always try to go through the code in Debug ( F8) mode and find the point at which it is not doing what it should, then hover over variables with the cursor, and see if they have in them what they should have. For example correct file names and paths etc.
Alan


P.s. ***** Remenber you still need to add the actual code lines you want between any opening or closing of files for the actual main stuff you want done - But that should be obvious from what the message boxes say and the comments in the code:

Please Login or Register  to view this content.