Forum Discussion
MS Access 365 - External Link to Folder
The source files the master excel file gets the data are multiple excel files. It uses power query to link to those files and consolidates them. All source excel files have a sheet with the same names and structure format.
On the one hand, you want to eliminate Excel, then, but the current system depends on a number of Excel source sources. I assume those source files won't be eliminated? Just the Master file that consolidates them?
The process of searching the folder for Excel source files and then importing the data from them into Access seems to be one viable option.
Another option would be to engage is a larger project to move all of the source data collection into an Access relational database application.
To be honest, that second option seems to me to be the better choice. It eliminates the problem of multiple versions of the truth found in multiple spreadsheets, plus it consolidates the data to make reporting more straightforward. If I were consulting for your organization, that would be course I would recommend.
I retired some time ago, so I don't have the template code for the File Search operation handy. It may be in an archive file. However, it is always a possibility. The question is how much time and budget do you have available for this project. If you go the folder search and import route, there will be development costs (your time, at the very least, at whatever your hourly rate is to the organization). If you want to replace the current approach, there will be development costs, and probably greater costs. There's also the matter of your experience, time and expertise to do either. Does it fall in the scope of your role? Do you have the experience to tackle either project? Is it worthwhile to your organization to have you gain the necessary knowledge and experience. I'm always eager to recruit new Access developers; it's a great skillset to have, but it doesn't suit everyone.
At the end, though, you would either have a fragile file import process or a more robust relational database.
Think through the factors and discuss it with your colleagues and whoever controls the budget.
- If you would like to pursue a coding approach, let me know. I've archived almost all of the client work I did before retirement. The code I would offer as a template is on some backup on an older, unused, computer. If it would help you, it would be worth while doing a search. Or, as I noted before, you probably need to have a meeting of the minds within your organization to decide how to move forward.
I agree that Excel is not a good solution as you've outlined it, but an appropriate alternative probably needs to come from a conscious selection. The costs alone, let alone the applicability of the solution would indicate that.