I am after some advice as the best way to tackle my requirements.
I have around 100 (and growing) data files which are currently all CSVs and each contain a couple of thousand records. I do not mind if these data files are changed or imported into Excel and kept that way instead. I want to be able to access the files to process them and to update them. I also want to run analysis on all of them - ie a page showing totals for each, comparing them etc. Finally, I want multiple users to be able to access different files at the same time. I am happy for each individual data file to be locked but other users should be able to access to other 99. I am reasonable competent with VB so can automate processes.
I can think of 2 main ways of doing this.
1. Create one large Excel doc with a sheet per data set which is where the data would then be kept.
My concern with this is that the Excel doc with so many sheets would become unwieldly and quite heavy. It would also make it tricky for multiple users to work on the document as I’ve never really got my head around the sharing feature in Excel where the same doc can be open on multiple computers
2. Create some sort of skeleton read-only Excel file containing no data that sets up queries to each of the CSV files. Each user can the open the Excel doc, import a CSV file to work on and update and then export when done. I am not sure how to prevent multiple users accidentally working on the same CSV file and their updates wiping out the others? It also means, presumably, that I lose any ability for the skeleton file to hold any updated information as it is read only. Again, is having 100+ queries unwieldly?
Is there a third way I haven’t thought of? I don’t really want to learn a whole different system, eg MS Access even though it may be better suited to handling records this way.
Thanks in advance.
I have around 100 (and growing) data files which are currently all CSVs and each contain a couple of thousand records. I do not mind if these data files are changed or imported into Excel and kept that way instead. I want to be able to access the files to process them and to update them. I also want to run analysis on all of them - ie a page showing totals for each, comparing them etc. Finally, I want multiple users to be able to access different files at the same time. I am happy for each individual data file to be locked but other users should be able to access to other 99. I am reasonable competent with VB so can automate processes.
I can think of 2 main ways of doing this.
1. Create one large Excel doc with a sheet per data set which is where the data would then be kept.
My concern with this is that the Excel doc with so many sheets would become unwieldly and quite heavy. It would also make it tricky for multiple users to work on the document as I’ve never really got my head around the sharing feature in Excel where the same doc can be open on multiple computers
2. Create some sort of skeleton read-only Excel file containing no data that sets up queries to each of the CSV files. Each user can the open the Excel doc, import a CSV file to work on and update and then export when done. I am not sure how to prevent multiple users accidentally working on the same CSV file and their updates wiping out the others? It also means, presumably, that I lose any ability for the skeleton file to hold any updated information as it is read only. Again, is having 100+ queries unwieldly?
Is there a third way I haven’t thought of? I don’t really want to learn a whole different system, eg MS Access even though it may be better suited to handling records this way.
Thanks in advance.
