none
Lockup - Data collected from multiple Excel files, compounded in Access, and presented in another Excel file RRS feed

  • Question

  • Hi,

    I have been searching high and low, but not really found the answer for my question. So here goes:

    I have a number (15-35) of Excel files (backend) which I use for data collection. They are distributed to different departments who enter their data daily - the files are often left open. 

    I then import the data from these excel files in an Access database by linking the tables.

    I then use a union query to combine all the linked tables into one single table.

    This table I then link (export) in another Excel file (frontend) where I can then make a visual presentation of the data collected.

    Now my problem is that if one of the backend excel files are open, my presentation excel file will fail when I try to import the newest data. 

    I used to get around this problem by making all the backend excel files "shared". But this is not a viable solution as it makes the backend excel files operate very slowly, and give quite a few headaches in regards to macros and locked cells.

    Is there some way I can collect the data in my frontend excel even though the backend excel files are open?

    Hope you can help.

     


    • Edited by MadsBro Tuesday, August 9, 2016 11:13 AM
    Tuesday, August 9, 2016 11:12 AM

All replies

  • I used to get around this problem by making all the backend excel files "shared". But this is not a viable solution as it makes the backend excel files operate very slowly, and give quite a few headaches in regards to macros and locked cells.

    Is there some way I can collect the data in my frontend excel even though the backend excel files are open?

    Hi MadsBro,

    Instead of linking to the Excel files, you could import the data in Access, and continue to work from there.

    Imb.

    Tuesday, August 9, 2016 12:08 PM
  • If you can access the spreadsheets using ADO.NET, it does not care if the spreadsheet is open or not. It essentially treats the spreadsheet as a datasource.
    Tuesday, August 9, 2016 2:29 PM
  • If I import the data, I have to do something manually right? This has to be a fluent workflow, so that when the excel files are saved the newest data is in the database, and available for the frontend.
    Tuesday, August 9, 2016 3:58 PM
  • Hi MadsBro,

    >> If I import the data, I have to do something manually right?

    You could import data automatically by RunSavedImportExport or Schedule an import or export operation. Or, I suggest you try suggestion from JimSnyder to use ADO.NET.

    >> Is there some way I can collect the data in my frontend excel even though the backend excel files are open?

    It seems your data is in this process, Excel files->Access database->Excel file. If there is no special requirement, I suggest you transfer data from Excel to Excel.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, August 10, 2016 2:30 AM
  • If you can access the spreadsheets using ADO.NET, it does not care if the spreadsheet is open or not. It essentially treats the spreadsheet as a datasource.

    Hi JimSnyder,

    If I want to use ADO.NET, where exactly will I use it. Is it in the frontend Excel that I should use it to collect the data from the database? Or should I use it in Access to collect the data from the backend files?

    I have googles ADO.NET and I am afraid I am in deep waters here :S

    If I have to link the front end and backend excel files directly via ADO.NET I think I will have a problem with merging it into one table, and sorting our datalines which are not relevant for the frontend. This can be many thousand lines...

    Wednesday, August 10, 2016 7:08 AM
  • Hi MadsBro,

    >> If I import the data, I have to do something manually right?

    You could import data automatically by RunSavedImportExport or Schedule an import or export operation. Or, I suggest you try suggestion from JimSnyder to use ADO.NET.

    >> Is there some way I can collect the data in my frontend excel even though the backend excel files are open?

    It seems your data is in this process, Excel files->Access database->Excel file. If there is no special requirement, I suggest you transfer data from Excel to Excel.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thanks Edward,

    >>It seems your data is in this process, Excel files->Access database->Excel file. If there is no special requirement, I suggest you transfer data from Excel to Excel.

    In the Access database I combine the data from all the backend datafiles, and sort out all the data lines which does not have a value that is relevant for the frontend. This can be between 10% and 90% of the backend data.

    >>You could import data automatically by RunSavedImportExport or Schedule an import or export operation.

    But then don't I still have to do something, or leave a computer turned on? This has to work without being dependent on me doing anything. I also have bad experiences with planned updates, that many times they for some reason don't run. And our departments need this data every day.

    I have asked Jim to clarify a bit about ADO.NET. If you have any pointers there I would be grateful.

    Cheers,

    Mads

    Wednesday, August 10, 2016 7:17 AM
  • If I import the data, I have to do something manually right? This has to be a fluent workflow, so that when the excel files are saved the newest data is in the database, and available for the frontend.

    Hi MadsBro,

    All the steps that have to be performed can be placed in a Sub. Just running this Sub is doing the job.

    In a couple of applications I don not use a scheduler, or something like that, but the first person that opens the application triggers the start of that particular Sub.

    Imb.

    Wednesday, August 10, 2016 7:41 AM
  • If I import the data, I have to do something manually right? This has to be a fluent workflow, so that when the excel files are saved the newest data is in the database, and available for the frontend.

    Hi MadsBro,

    All the steps that have to be performed can be placed in a Sub. Just running this Sub is doing the job.

    In a couple of applications I don not use a scheduler, or something like that, but the first person that opens the application triggers the start of that particular Sub.

    Imb.

    Okay, so if I include a Sub in the frontend excel to importdata in access, won't I still have the same problem that it will not access the data from the backend excel files that are open?

    Wednesday, August 10, 2016 8:07 AM
  • Okay, so if I include a Sub in the frontend excel to importdata in access, won't I still have the same problem that it will not access the data from the backend excel files that are open?

    Hi MadsBro,

    I had in mind that the Sub is part of Access.

    I have not so much experience with Excel files, as I use mostly csv-files. But in a little test while a csv-file was opened for editing, it was possible to make a copy of the original csv-file. Of course, all changes to the file are not in the copied file.

    Imb.

    Wednesday, August 10, 2016 10:37 AM
  • I believe your project/program was intended to be run by a few only and thus was designed for that. Now the project has become larger and suddenly Scalability comes into (my) mind. You need to fundamentally rethink the whole project. You cannot have a lot of Excel files randomly opened by anybody and still maintain a working solution.

    1. Redesign the project to use Access only (one-time import).
    2. If you expect the project to grow even bigger, build a front/backend Access solution from start.
    3. If you expect that many users need access, make the backend a SQL server directly (SQL 2014 Express is free).
    4. When you design new projects, learn from this and have Scalability in mind before painting yourself in a corner.

    Best regards, George


    Wednesday, August 10, 2016 2:55 PM
  • Why do you need Access for this? Just for the Union?  Try the AddIn below.  That will merge all your Excel files into one.

    http://www.rondebruin.nl/win/addins/rdbmerge.htm


    MY BOOK

    Tuesday, August 16, 2016 5:35 PM