Pulling data from other workbooks based on Date Modified

Hi Cressie,
_ 1 )
_ I was wanting to do this, and actually tried your method.

Originally Posted by Cressie

......
Is there a better way of doing this? Could this work?
.....

_ I could not understand how your code worked. So i thought trying to answer this Thread would help us both...I googled, hit some similar stuff, here Norie, tusharm, just to mention a couple
http://www.mrexcel.com/forum/excel-q...ile-names.html

_ I eventually got the point, I think .. and your basic idea for the date stamp I have used.
_ I guess there are many ways to do it, but your basic idea seemed as good as any. Just the logic confused me a bit
_
_ I was actually a bit confused as to what you actually want, or rather how you were trying to do it with one “date stamp”. Maybe I just misunderstood.
_ Anyway I would have thought you need two “Dates” which maybe you did indeed mean by DateLastModified and DateStamp
_ So your basic code is my code 1. Maybe it does nothing more than you already. I have tried to explain as much as I understand in the comments. It seems to work very well
_ For no particular reason I am looking at Cells M40 and M41 for the two dates in any the first sheet in any other File in the same Folder as the File where my master file ( “ wbCodes “ ) is. I am sure you can change the various references to suit
_ here is just the first sheet of a File my code found and updated with Todays Date:

Using Excel 2007

Row\Col	L	M	N
39
40		25.09.2015
41		25.09.2015
42

Sheet1

_ Here is Code1:

http://www.excelforum.com/developmen...ml#post4199910
_
_ Basically this code puts two dates in. One ( DateLastModified ) represents the date this code or anything or anyone changed anything. The other (DateStamp ) is the date this code did the stuff it does ( and when it changed the sheet by virtue of pasting in the two date values ). As i often have problems with date formats when checking greater than or less than , my code simply checks if these two “dates” held as Text Strings are the same. So anyone or anything else changing the sheet must only change the DateLastModified to something else. The criteria for this code doing anything is then simply the match in these two Strings, regardless of how they look . So for example if Fred changes the file, he could write in cell M41 “ ‘Allo! I, Fred, changed the file that day we had an Office party” . If you do want to use Dates, it would be wise to always ( try to **) use the same format bit Format(___ )
_ But you may need to experiment with the formatting of the cell. I find text format is usually the best in doing these things, and even then I do a bit of “extra Belts and Braces” in the code as VBA and Excel has an annoying habit of changing Number Formats erratically .
_
_ ........................................................


_ 2 ) In this bit, I tried to contribute something “different” and Googled a lot . For example I wondered if I could do the check you want to do without opening the workbook...I did not think it could , Some experts here said i could,
Googling said mostly I could not, ( Directly ) but WTF I Googled further and experimented and got something to work.. at least in my version of Excel...
_ ...I guess the answer if it can be done or not is Yes and No. I do not understand yet what is going on. Somehow I “perform “ the reference to a cell in a closed Workbook” . By performing that I get returned what I want, that is to say some version of what is in the cell. The subtlety of exactly what i am doing is lost on me ?

_ So __________ “Perform A Reference”


_ For now I have just done a code to check for the Date Change without opening the File. Again I have tried to explain as much as i understand in the green comments. I guess you could take it further to do what you want to do without opening the file, but I did not want to over complicate it, and anyway i guess it is good to see what is being done. ( To do your Range Copying Stuff you would need to do Looping to bring in each cell value, that is to say i think you must “Perform A Reference” for each cell to get at what is in it )

Code 2 :

http://www.excelforum.com/developmen...ml#post4199910


_ It seems to work OK. I cannot think immediately of a better way, although a few improvements like checking for Sheet existence etc could be easily be done.
_ I guess there are infinite alternatives

_ My questions for anyone watching who could make a contribution:
_1 ) What am I doing / or how is it working my “Perform A Reference” stuff in my second Code ?

_ 2 ) Has anyone got any better, more efficient or just plain alternative solutions for the original Request ?

Thanks
Alan

Hi Cressie,
Yes , sorry I do understand now. I did miss that point which you have clearly stated.
What is clearly missing from my codes and what you already know is that bit about getting the information through a macro as to when a file was last modified, that is to say , I guess what one sees here:

LastModified.JPG

I am sorry I do not know how to do that. But that information must be somewhere and gettable somehow

Try here for example
http://www.techonthenet.com/excel/fo...ledatetime.php


Maybe it would be best now to start a new Thread with a title such as
“VBA to return date last modified for all .xls Files in a Folder”
That is the missing bit now.

You should start that Thread by giving a link to this thread by Including the URL
http://www.excelforum.com/excel-prog...-modified.html

and after explaining the requirement for getting the date last modified you could further ask for your full requirement should you still need help with that. Or after getting that bit start another thread again.

Sorry I could not help more. I did wait sometime before replying and I expect as it was so long without a reply you may have got no other, so at least i “brought it back up for you once “ But maybe a new Thread would now be appropriate unless anyone else catching this thread can help with the VBA code to get the date last modified for all .xls Files in a Folder !!

Good luck with your project

Alan

P.s. If you do start a new Thread please mark this Thread as solved using the Thread tools above, and please reply to this thread giving the link to the new Thread. That way I may be able to follow and help further if no one else picks up the new Thread

OR
.. why don’t I just do it then, as it seem quite easy....( The VBA FILEDATETIME FUNCTION does all you want )

SO
I have my master File in some Folder. The master File has this “Date Stamp” in cell 1, 1


Using Excel 2007

-	A	B
1	29.09.2015
2

Sheet1

I created another Folder within the above Folder and called it “CressieFolder” . In tha Folder i put some .xls Files in it

Then I wrote the program I think now that you want. The program looks through in turn at all .xls Files in the the Cressie Folder. It gets the last Modified date ( actually the date that the file was last saved ) . It compares this with the date stamp.
If the two dates are different then the File is opened and a message box pos up saying “Do stuff” ( Obviously you would write there the bit to pull the data etc ). Then the file is closed.
If the two dates are the same then a message box pops up and tells you that no changes have been made in that file since the Date Stamp.

At the end of the program, sheet 1 in the master File is changed thus:

Using Excel 2007

-	A	B
1	01.10.2015
2

Sheet1

Here is the code:

http://www.excelforum.com/developmen...ml#post4205355



Alan

Hi

Originally Posted by Cressie

.....
This is the line that is highlighted when I press debug:

Please Login or Register  to view this content.

......

.. an extra * seems to have crept in there

My code looks like this

Let strExt = "xls"

Alan

P.s.
_ 1 ) Just noticed I did not quite get it right .. you want “...only … time stamp …When the macro last ran to pull the data…“ my code changes the time stamp every time the macro runs… So a simple change there is to do the date stamp bit in the appropriate place just after stuff is done rather than as I had it originally at the end of the macro.. ( I have uploaded a new code along with some other changes to help debug.............

Originally Posted by Cressie

...........
Any thoughts on debugging........

_ 2 ) File Paths can be tricky to get right... to help “DeBug” I have also included some Debug.Print lines –Hit Ctrl-G in the VB Editor just before running the code to display the Immediate Window. Displaying the string in the Immediate Window can help to check correct format

Running the code with these modifications gives the following in the Immediate Window ( You can easilly copy everything in the Immediate window by highlighting what you want then hit Ctrl + C . I would then paste anywhere convenient such as after a ' to make it a comment at a spare space in the code )

F:\ExcelForum\wbSheetMaker\CressieFolder\
F:\ExcelForum\wbSheetMaker\CressieFolder\*.xls
Cressie1.xls
Cressie1.xls
F:\ExcelForum\wbSheetMaker\CressieFolder\Cressie1.xls
Cressie2.xls
F:\ExcelForum\wbSheetMaker\CressieFolder\Cressie2.xls
Cressie3.xls
F:\ExcelForum\wbSheetMaker\CressieFolder\Cressie3.xls

If I look in the Folder where I have made some files to check this code, click on the small drop down arrow, then I see

CresssieFiles.JPG

I can then copy this ( Highlight and Ctrl C , and again paste somewhere convenient ) to compare with the above

F:\ExcelForum\wbSheetMaker\CressieFolder

In this case there is a good match and the code worked well.

I would suggest if you still have difficulties run first the code exactly as it is, having first created the “CressieFolder” in the same Folder as your master file, and put some .xls files in it.
Once you have done this, start changing the various String Paths to suit you , and then proceed preferably by running the code in Debug Mode ( F8 ) and then check that all the various String Paths look correct

Alan

Modified Code Here:

http://www.excelforum.com/developmen...ml#post4206691

Hi Matt

Originally Posted by Cressie

Many thanks for your help with that Alan, It is all working correctly. .....

You're welcome, thanks for the Feedback
Alan

Hi Matt,

Originally Posted by Cressie

......A further question on this. How do I go about getting the macro to search in a number of sub folders? What I mean is the Cressie Folder will have a folder for each of the Cressie workbooks (Using your example from above).
....

_ I guess broadly there are two answers to that, ( but bear in mind I only just learnt the Directory, Dir, stuff by answering this Thread ! ) ......
_ 1 ) If you know the name of each Sub Folder in the CressieFolder , and each folder only has one File in it, then I guess you might know the name of each File ? Then the whole point of using the Dir() to get each name is lost. Then you do not really search for each File, rather you have a list ( An Array maybe ) of their names and then access each one at a time in a loop.
_
_ 2) I guess this may be more like what you have ( or want ) . – Assuming you do not know the names of Folders or Files, ( or do not want to have to list them in case they change or are added to etc ). A nested Loop with 2 Dir() / Dir would probably be tricky. – You get the point I guess that every time you use the unqualified Dir ( call DIR again with no arguments ) it looks again with the same search criteria and picks the “next”. This limits it to be being used once in a loop or loops.
Difficult to explain exactly what i mean there - But say it looped in the “inner loop” to get Files. After that you want to go to the next folder and look for files again. – You would need to start a new Dir() with arguments. But then you would start again at the last Folder, and never “move on”
_ Briefly a solution I can think of would be to first do the Dir to get as list of the Sub Folders. Then for each of those do a version of my code to go through the File or files in each folder.


_ I am not at my Excel Computers now. Have a go , post again here if you are still having difficulty and i may be able to take another look later, And maybe start a new Thread but referencing the link to this one.


Alan

P.s. If you Google you will see there are “attributes” or extra optional arguments in the Dir() which will let you find Folders as well as Files.

Hi,
Just a quick bit of Tidying up Here... I was using this old Thread in another Thread and noticed the Link to the code from Post #5 is incorrect.
That is the First of two codes using the VBA File Date Time Function. Link should have been
http://www.excelforum.com/excel-prog...ml#post4205373

( but the second code version from Post #8 is the newer one anyway )

And BTW, also for anyone catching this Thread in the future it was carried on here:
http://www.excelforum.com/excel-prog...ubfolders.html

Alan