Forum Discussion
HarrisonBeck
Mar 31, 2021Copper Contributor
Get DB Data From Folder
Hello, Is there a way to do this? Power BI and excel can both get data from a folder, not just a file. I'd like to be able to get the data from the most recent file added to a folder when the DB ...
George_Hepworth
Nov 16, 2023Silver Contributor
Once again, it is not likely Microsoft will implement this.
In the meantime, look into creating a recursive VBA function to do it. Here's one such procedure:
https://stackoverflow.com/questions/30356373/using-ms-access-to-import-multiple-text-files
gdrahal
Nov 16, 2023Copper Contributor
George_Hepworth Will do. And thank you.
- Brian1370Feb 13, 2024Copper Contributor
Replying as I have a similar but different use case and just read through the thread. I think my solution might be useful to you.
My problem: I want an Access database to link to all Excel files in a specified folder. These files contain the same kind of data, and so ideally I would rather they all aggregate into one table as opposed to each being their own.
My solution: I created a new Excel sheet and used PowerQuery (within Excel - Go to Data >> Get Data >> From File >> From Folder) to link to all Excel sheets in my specified folder. In your case, you could then specify within PowerQuery that you want the most recently-updated file or whatever else you need. Save this intermediate Excel sheet somewhere, and then link to it from a database as usual.
One caveat about this solution - I am not sure how data refresh will work here. In an ideal world, each time the database checks the linked file, the linked file should check its linked folder. That might be the case here, but I'm not sure.
Either of you should feel free to let me know if I'm way off here - I am learning and would appreciate being steered straight if I'm off course. Have a great day.