External word documents last saved date in Excel cells.

**tazman_il** · 11-27-2012, 09:34 AM

Hello everyone,

first of all let me say that there are really some very smart people around this forum. I've used many of their solutions and I must say Bravo, to all those that assist with their knowledge.
Now as to my problem, I've roamed this forum and others to get some info, but maybe it's not exactly right the way I'm formulation my question.

I have number of word documents where each doc represents a customer. we use that word doc as a CRM database for that specific customers. so were talking 20-40 word docs all in one directory each with a different company name.

I have an Excel file that is the Master CRM in which each line has some details to the customer, a hyperlink to the word document (so that when you double click the word doc is opened) and some other info. I'd like to add a cell formula that will show the last modified date of the external word file.

Is there some way to do this in a formula so that I just need to copy it as I open each new customers file? Also please note that the docs and the master file will be placed in dropbox folder.

Thanks all and once again kudows to this forum.

Thanks

**kbkumar** · 11-27-2012, 12:57 PM

Hi - Try this:

Please Login or Register  to view this content.

First column gives the File Path, second column the Date of "last modified"

Hope this helps.

**tazman_il** · 11-27-2012, 03:54 PM

Kbkumar

Sorry for the ignorance, but what is the formula that I place in the excel cell?. and where does the hyperlink go?

**kbkumar** · 11-27-2012, 04:27 PM

Sorry, forgot to tell. This is not a formula but a VBA code that will get you the desired results.

Will upload you the File with Macro in a bit...

**kbkumar** · 11-27-2012, 04:38 PM

See the attached file. Save this to your local drive and follow these steps:

1. Open this file, Press ALT + F11. This will open the VBA Screen.
2. Double click "This Workbook" under VBA Project
3. You will see the code mentioned in post #2
4. The line which says MyFolder = "M:\Desktop" & "\" change the folder path mentioned in quotes as per your requirement. Make sure you dont delete or overwrite the last part "\"
5. To run the macro either press F5 while being in the VBA screen or Go to Excel, Go to View - Macros - View Macros and select This Workbook.Test and Click Run.

You will see that Sheet 1 will have the results you were expecting.

Hope this helps and works for you...

**tazman_il** · 11-28-2012, 02:17 AM

Kbkuma;

Thanks!!! this works perfectly and it's great for a different part of project. better than what I had in mind. However, this is not exactly what I needed for this problem.

You see, I have in cell A1 a hyperlink that when clicked opens a word document. the hyperlink in the cell is fixed. i.e. the position doesn't change. next to the hyperlink cell is the last date updated cell. That's where the last modified is.

I'd like to design a subroutine that defines a formula that takes the path of the file in the hyperlink in cell A1 and returns the last modified date of that file (in the hyperlink address). this should occur every time the excel file is opened.

I'm sorry to re-ask you since you already solved one issue for me, but can you help out with this.

Many thanks....

**kbkumar** · 11-29-2012, 08:20 AM

Hi - thanks for further inputs. Have modified the code to pull the "Last Modified Date" in column B using the existing hyperlinks in column A upon opening of the workbook. Copy paste the below code in "This Workbook" as per steps mentioned in post #5:

Please Login or Register  to view this content.

This will get activated everytime the workbook containing the hyperlinks is opened and will generate the "Last Modified Date" for alla files mentioned in column A.

Hope this helps.

**jeronimo1503** · 03-09-2021, 10:50 AM

Thanks kbkumar for this post. This is exactly what i was looking for.

**6StringJazzer** · 03-25-2024, 10:28 PM

Originally Posted by jeronimo1503

Thanks kbkumar for this post. This is exactly what i was looking for.

Welcome to the Forum :-)

You are responding to a post that is over 11 years old and kbkumar has not been active here for seven years. Please comment on threads only you have something to add for the solution.

Thread closed.