External File Properties In Cells. "LastSavedTime"

**Bluewhistler** · 02-02-2010, 09:12 AM

Hi,

I was looking for a way to get external file froperties into a cell!
for example:

Last modified date.

this is what I allready found/tried.

Please Login or Register  to view this content.

I use this for other filetypes and it works just fine. Except that I somehow can't seem to make the cell to automaticly update. After the linked file has been edited/saved and has a new lastsave date/time. It doesn't show the new time in that cell. not even if I close/save the workbook and reopen it.
It only changes the Last saved date when I go into the cell and press enter.

The only way this works is when I (for example):

- Put the file link c:\docs\test.txt in cell A1 in another Book2.xls (A1 shows c:\docs\test.txt )
- Link to that cell from Book1-A1 with this formula =C:\docs\[Book2.xls]Sheet1!A1 (result showing in cell is c:\docs\test.txt )
- Then put =Lastsaved(A1) in Book1-A2 (result is last saved date and time (cell properties set to dd:mm:ss)

Now when I reopen the document excel asks for link update.
This works but surely there must be an easier way.

**vikas.bhandari** · 02-02-2010, 11:19 AM

Originally Posted by Bluewhistler

I use this for other filetypes and it works just fine. Except that I somehow can't seem to make the cell to automaticly update.

Automatically means??? If you mean that the cell should be automatically updated as soon as I save the file whose path is stored in the Cell A1. Then I don't think there is any FileWatcher available in VBA to solve this. (Not of my knowledge atlease).

If you mean the macro should update the last modified time "automatically" as soon as you enter a file name. It is the default process.

The way you are using the Function is a little tricky. You cannot force Excel to call that function and update the values by using calculateSheet or CalculateWorkbook option. Excel will calculate the value only when it thinks there is some change in the input and then it calculates(for optimization purpose). You can force excel to calculate a cell by dummying the data. For example, goto the cell where the file name is given, edit the cell(press F2) and then press enter. It will update the value.

However, there is one catch :

Please Login or Register  to view this content.

This line will create the File System object every time you will try to call the function. It is a good practice to create an object at the module level so that you dont have to create it again and again.

Please Login or Register  to view this content.

As a solution, you can write a macro to update the cells with File Names together. As soon as you copy and paste the File Names to the cells, the LastSaved function will be triggered and the values will be updated.

HTH,
Vikas

**Bluewhistler** · 02-02-2010, 11:55 AM

Right it doesn't need to update automatically.
Heres what i I'm trying to get.
I got a library with electrical drawings and .pdf prints or .dwf prints of them. there are allot of them so F2 option is a Killer.
Then there is this .xls file with all the information about each elektrical drawing. which is used by several ppl here. and the drawings are only used by myself.
Now what i want to do is to display the last modified date&time in a cell next to the drawing number or name.
The ppl who open the .xls file need to allways see the lastsaved date.

Schematcs Name Last saved on Hyperlink to pdf/dwf
Utility_1001 ............. Utility_1001.pdf
Utility_1002 ............. Utility_1002.pdf
Utility_1003 ............. Utility_1003.pdf

Last saved on = a formula that gets the last modified date of the file on the right or on the left. and when drawing is opened these formulas need to be rechecked and updated if the data is diffrent.

also I don't understand much about VBA so what I just do is put the code it in a module and test if it works. is that why i got a compile error.

thx for your help vikas.

**vikas.bhandari** · 02-03-2010, 03:23 AM

Hi there!

Ok, I have attached a spreadsheet here and I have verified that everytime I open the spreadsheet, the data is being updated with no problem. It is showing me the last saved and updated time. Probably I am not able to get what you are trying to do or I am getting insane :P

Just kidding. Please let me know if you are looking for the same.

Thanks,
Vikas

**Bluewhistler** · 02-03-2010, 04:07 AM

We're almost there thx for your time.

I made the 4 files on my c:\ to test out your attachment which I saved on my desktop.
Then I opened all the .txt files + the .xls you made.

to test this out I saved the .txt randomly with Ctrl+S(save).
I acpect no change offcourse since exel cant do that so I close and save the .xls and reopen it...
Unfortunatley the Last save dates arent updated.

I expected this would happen because the first time I opened the attachment there were lastsaved times in kollom B. which is impossible because the files didn't exist. If this would have worked correctly then According to your:

Please Login or Register  to view this content.

the formula must have showed Invalid File in all cels in Kollom B

I Can't thank you enough allready.

**Bluewhistler** · 02-03-2010, 04:36 AM

Extra Testing:

It seems thoug that when I go to the options>TAB:Calculate> and change eigther one of these (Presision as displayed / 1904 date system) and click ok the cells get refreshed?

this aplies to both of these codes:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

plz forgive the dubble post as this is an other test

**vikas.bhandari** · 02-03-2010, 08:03 AM

Ok for your problem I have added the following code in the Workbook module and it is updating data as soon as you open it.

Please Login or Register  to view this content.

hth,
Vikas

**vikas.bhandari** · 02-03-2010, 08:27 AM

Oh!!!!! I didnt know it

if you add the following line to your code :

Please Login or Register  to view this content.

It will update the values as you want it to. Please use my previous workbook sample and include the above line in the LastSavedTime function. It should be done now.

Thanks,
Vikas

**Bluewhistler** · 02-03-2010, 09:40 AM

Than you very much.

Please Login or Register  to view this content.

Does the trick!

I did as you told me and this works lika a charm! now when I press F9 the dates are refreshed!

Here's the Result & Solution:

Please Login or Register  to view this content.

Thx again Vikas

**GianIzar** · 04-18-2020, 11:01 AM

Originally Posted by Bluewhistler

Extra Testing:

It seems thoug that when I go to the options>TAB:Calculate> and change eigther one of these (Presision as displayed / 1904 date system) and click ok the cells get refreshed?

this aplies to both of these codes:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

plz forgive the dubble post as this is an other test

I want this to point to a file in another folder but withing the current directory structure.
In other words no matter where I place the directory structure it will work.

**FDibbins** · 04-18-2020, 11:35 PM

Originally Posted by GianIzar

I want this to point to a file in another folder but withing the current directory structure.
In other words no matter where I place the directory structure it will work.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

External File Properties In Cells. "LastSavedTime"

LinkBack

Thread Tools

Rate This Thread

Display

External File Properties In Cells. "LastSavedTime"

Re: External File Properties

Re: External File Properties

Re: External File Properties

Re: External File Properties

Changing Calculation Options Refreshes the Lastsaved Time

Re: External File Properties In Cells

Re: External File Properties In Cells

Problem Solved Thx Vikas Bhandari

Re: Changing Calculation Options Refreshes the Lastsaved Time

Re: Changing Calculation Options Refreshes the Lastsaved Time

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions