Date Modified List

[MrDB4Excel]

I use several worksheets to keep track of pension income, bank statement data, and living expenses.
In the worksheet titled “CurrencyConversionRatesWebQuery.xlsm” I have a sheet titled “Date Modified List” in which I have listed in one column the file names of several worksheets and in the next column the date last modified.
Some of these files are .xlsm and some are .xlsx. I would prefer to not use vba as you can see in the image several files are .xlsx.
Is there a method or formula that auto-updates the specific file’s date last modified when the file is saved using a date and time format. I am currently using this format: ddd-mmm-yyyy -- hh:mm.
Obviously whether it is ddd/mmm/yyyy -- hh:mm or mmm-ddd-yyyy -- hh:mm or mmm/ddd/yyyy -- hh:mm or any such combination is irrelevant. What is important is that the method or formula returns the current date and time when the file is saved.
This specific sheet titled “Date Modified List” which I am using to corral all the worksheets would link to the relevant Excel file’s cell containing the “date last modified” input in the relevant file.
Attached image shows a portion of this list contained on the sheet titled “Date Modified List” for which you see the 2 column labels.
Thanks for any help offered.

 

[sandy666]

you can use Power Query for the name of files and Last Modified Date but, because PQ doesn't support hyperlink, you will need replace = with = manually (if not vba) for each refresh result table

 

[MrDB4Excel]

you can use Power Query for the name of files and Last Modified Date but, because PQ doesn't support hyperlink, you will need replace = with = manually (if not vba) for each refresh result table

Hello Sandy,
I am a bit confused about the "replace = with = manually" so does this mean I do this in each file? Perhaps there is a method to acquire this data from each file but using vba only in the CurrencyConversionRatesWebQuery.xlsm file and thus acquire all file data using vba only in this file? As you see in the image I do have some file types .xlsm, so I am not totally against using vba. I only did not want to turn all the files of type .xlxs into macro files.
Open to all possibilities using vba in one file and or PQ in combination with vba in one file.

 

[sandy666]

so you need data from these files or just filename, LMDate and link to them?

 

[sandy666]

with link in result table it will be as text and looks like
=HYPERLINK("F:\folder1\forlder2\anyfile.xlsx","anyfile.xlsx")
you will need replace = with = (manually or with vba) then it will looks like anyfile.xlsx

if you want data from these files use From Folder feature then you can do what you want with it

 

[MrDB4Excel]

so you need data from these files or just filename, LMDate and link to them?

I only need the date and time each file was last saved and each instance of such to appear in the one file CurrencyConversionRatesWebQuery.xlsm on sheet Date Modified List. the date and time would of course auto-update when each file was opened and saved thus linking that data to CurrencyConversionRatesWebQuery.xlsm on sheet Date Modified List.

 

[MrDB4Excel]

with link in result table it will be as text and looks like
=HYPERLINK("F:\folder1\forlder2\anyfile.xlsx","anyfile.xlsx")
you will need replace = with = (manually or with vba) then it will looks like anyfile.xlsx

if you want data from these files use From Folder feature then you can do what you want with it

I'm sorry, but this has me scratching head wondering how and what to do with this.

 

[sandy666]

Give me a few and I'll create an example

 

[sandy666]

as result
Before

and After

so "= is replaced with =

 

[sandy666]

ops, ignore post above, here is a proper example
Before

After change "= to =