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.

 

[MrDB4Excel]

ops, ignore post above, here is a proper example
Before
View attachment 17074
After change "= to =
View attachment 17075

Should I assume this all occurs within the context of Power Query? I must be brain dead today because I just don't see it all. For example, the data that shows in the "Date modified" column comes from the relevant file? And how so? By using an equals to the relevant cell in the relevant file? You see how brain dead I am today, sorry I just don't follow.

 

[sandy666]

refresh thread and look at post #10 again at After picture
all is in Power Query except replace "= with =

 

[MrDB4Excel]

refresh thread and look at post #10 again at After picture
all is in Power Query except replace "= with =

Thanks, Sandy, now I get it. I will test this to see what comes of it.

 

[sandy666]

sure, here is an example M code

let
    Source = Folder.Files("D:\test\TestFolder\both"),
    Merge = Table.AddColumn(Source, "Link", each Text.Combine({[Folder Path], [Name]}, ""), type text),
    Prefix = Table.TransformColumns(Merge, {{"Link", each """=HYPERLINK(""" & _, type text}}),
    Suffix = Table.TransformColumns(Prefix, {{"Link", each _ & """)", type text}}),
    TSC = Table.SelectColumns(Suffix,{"Link", "Date modified"})
in
    TSC

 

[MrDB4Excel]

sure, here is an example M code

let
    Source = Folder.Files("D:\test\TestFolder\both"),
    Merge = Table.AddColumn(Source, "Link", each Text.Combine({[Folder Path], [Name]}, ""), type text),
    Prefix = Table.TransformColumns(Merge, {{"Link", each """=HYPERLINK(""" & _, type text}}),
    Suffix = Table.TransformColumns(Prefix, {{"Link", each _ & """)", type text}}),
    TSC = Table.SelectColumns(Suffix,{"Link", "Date modified"})
in
    TSC

Throwing in the towel. I have been using Excel for about 30 years and this one has me completely stumped. I have therefore resigned forget this endeavor and will simply look at file explorer to get the date and time, done.
Thanks anyway for your effort, it is much appreciated. Talk about getting a headache, I have spent the better part of today messing with this thinking that as well developed as Excel is that this would be easier than falling off a log.

 

[sandy666]

Data tab - New Query - From File - From Folder
in new window - Edit
then merge columns: Folder Path and Name
to this column add prefix: "=HYPERLINK("
and suffix: ")
then remove unnecessary columns
close&load

for more info

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

 

[MrDB4Excel]

Data tab - New Query - From File - From Folder
in new window - Edit
then merge columns: Folder Path and Name
to this column add prefix: "=HYPERLINK("
and suffix: ")
then remove unnecessary columns
close&load

for more info

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

Hello Sandy,
Good news. I decided to grab the bull by the horns and wrestle with the Power Query. Success, this was much easier than I thought. Thanks loads for your expert assistance, kudos, and hooyah support.
My first attempt had the PQ in a separate file and if I opened the file CurrencyConversionRatesWebQuery.xlsm it had the edit links message. So then I would first open the DateModifiedList.xlsm file and second open the CurrencyConversionRatesWebQuery.xlsm file and no edit link errors. So then I thought why not have the PQ in the same workbook as the CurrencyConversionRatesWebQuery.xlsm file and now it works seamlessly without the need to first open the DateModifiedList.xlsm file.

 

[sandy666]

You are welcome
Nice to see that:

I decided to grab the bull by the horns and wrestle with the Power Query. Success, this was much easier than I thought

Have a nice day