How to load the latest excel file in a folder?

[ffan2008]

I am trying to store the name of the latest filename from a batch of files in the folder and recall it when I am loading.

That way I can always load the latest file clicking "reload" without typing in the latest file name again.

I have used this code to extract the file names

load_name:
load *, filename() as Filename

From *.xlsx (txt);

and not sure how to recall that latest file name in load.

Order:
LOAD *

FROM
[C:\Data\orders-2020-05-21.xlsx]
(ooxml, embedded labels, table is Orders);

Would be great if someone can give me a hand.

Thank you very much!

[bhaskar_sm]

Hi, you may try something like this.

TablesList:
LOAD
"TableName"
FROM [C:/TablesDetails.csv]
(txt, utf8, embedded labels, delimiter is '\t', msq);

For i = 0 to (NoOfRows('TablesList')-1);
Let varTableName = Peek('TableName',$(i), 'TablesList');

// Load Tables

$(varTableName):

Load

*

FROM FROM
[C:\Data\$(varTableName).xlsx]
(ooxml, embedded labels, table is Orders);

Next;

Thanks,

Bhaskar

[Saravanan_Desingh]

One solution is..

For Each File in FileList ('C:\Users\...\MyFiles*.xls')
Folder:
Load '$(File)' as Name,
 FileTime( '$(File)' ) as FileTime
 Autogenerate 1;
Next File

//Method 1 -
LatestFile:
First 1
Load
 Name,
 FileTime,
 1 as dummy
Resident Folder
Order By FileTime DESC;
Let vFile=Peek('Name',0,'LatestFile');

Trace << vFile=$(vFile);

[Saravanan_Desingh]

one more..

//Method 2 -
Modified_Time:
Load
     Timestamp(max(FileTime)) as ModifiedTime,
     FirstSortedValue(Name, -FileTime) as LastModifiedFileName
Resident Folder;
Let vModifiedTime=Peek('ModifiedTime',0,'Modified_Time');
Let vLastModifiedFileName=Peek('LastModifiedFileName',0,'Modified_Time');

Trace << vModifiedTime=$(vModifiedTime);
Trace << vLastModifiedFileName=$(vLastModifiedFileName);

[Saravanan_Desingh]

Did you try the above solutions?