Formula to look for a file in a folder not specifying file name

[misiek5510]

Hi,

I'm getting exports in excel file on daily basis, which are then put in a folder. All old versions are deleted prior to this, so there is always just one file in the folder.
I need formula to use the only file from that folder without specifying the file name (in red below). Is there a way to do it?

='C:\Users\user\Desktop\Compliance\Folder\Export\[data.xls]Sheet1'!$A$2

 

[Joe4]

I don't think there is a way to do it without VBA.
Excel formulas are going to want the exact file name.

If you use VBA, you can search the folder to get the file name, and then write that to the formula.
Are you open to a VBA solution?