Results 1 to 6 of 6
  1. #1
    Retrofit is offline Novice
    Windows 8 Access 2016
    Join Date
    Nov 2017
    Posts
    3

    Question How to create several Excel files that all report to one Master file

    Hello!



    I want to create a kind of "database" where I can have a multiple number of excel workbooks that all look sends their content to one master file, which saves it all in one workbook so to speak.
    I can then oversee the whole "project" by only looking in the master file.

    As far as my knowledge goes, Access should be able to help me with this somehow. Could someone please link me a guide or explain how to create that sort of bond between workbooks? Or perhaps if you know a better way.

    To explain the practical use of it;
    Several workstations will have a checklist where they fill in after they complete an assembly, and when they save the checklist i want it to send the info to a master file, which I account for and use to print out and save all the workstations checklist as one big checklist.

    Thanks for your time!
    /Retrofit

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I am sure Access VBA could programmatically create links between sheets but why bother with Access if you want to use Excel as data repository? Excel has VBA.

    Personally, I would create a multi-user Access database and eliminate Excel.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Retrofit is offline Novice
    Windows 8 Access 2016
    Join Date
    Nov 2017
    Posts
    3
    Quote Originally Posted by June7 View Post
    Personally, I would create a multi-user Access database and eliminate Excel.
    Then I would think it is a good solution, I will google around and see if I can understand how to do that. To see if it fits my needs.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    I have made in Excel such file systems, when every user works with his own data. The set-up will be:

    1. There is a Master Excel workbook, where:
    a) The info from other workbooks is collected using ODBC queries (I have read info from every workbook into separate hidden sheet, and used UNION query to read the info from hidden sheets in Master workbook into summary sheet in same Master workbook, but probably you can use an UNION query to read data from other workbooks into summary sheet in Master workbook directly). The summary sheet in Master workbook is read-only;
    b) Registries used in all other workbooks are maintained. Only the person maintaining Master workbooks enters info into those registries.
    2. There is a number of child workbooks, with a certain user inserting only his info. Any information needed to use in several/all workbooks is readed from Master workbook (into hidden sheets using ODBC queries). User can enter this info into his table(s) using Data Validation List(s), or this info is read from hidden sheets by formulas.

    All queries are set to be refreshed on open. The user can always refresh queries manually. N.B. ODBC query reads only saved info!

    All workbooks must be on network resource which is available during work time. Avoid changing workbook's locations without dire need - you have to edit data connections every time you change workbook(s) location(s).

    This set-up is more complex compared with shared workbooks, but all users can work at same time without interference form others. Lately the workbook sharing is made better, but some 10 years ago it was practically useless. And anyway the sharing prevents you from using some features, which is not the case with this set-up.

  5. #5
    Retrofit is offline Novice
    Windows 8 Access 2016
    Join Date
    Nov 2017
    Posts
    3
    Quote Originally Posted by ArviLaanemets View Post
    I have made in Excel such file systems, when every user works with his own data. The set-up will be:

    1. There is a Master Excel workbook, where:
    a) The info from other workbooks is collected using ODBC queries (I have read info from every workbook into separate hidden sheet, and used UNION query to read the info from hidden sheets in Master workbook into summary sheet in same Master workbook, but probably you can use an UNION query to read data from other workbooks into summary sheet in Master workbook directly). The summary sheet in Master workbook is read-only;
    b) Registries used in all other workbooks are maintained. Only the person maintaining Master workbooks enters info into those registries.
    2. There is a number of child workbooks, with a certain user inserting only his info. Any information needed to use in several/all workbooks is readed from Master workbook (into hidden sheets using ODBC queries). User can enter this info into his table(s) using Data Validation List(s), or this info is read from hidden sheets by formulas.

    All queries are set to be refreshed on open. The user can always refresh queries manually. N.B. ODBC query reads only saved info!

    All workbooks must be on network resource which is available during work time. Avoid changing workbook's locations without dire need - you have to edit data connections every time you change workbook(s) location(s).

    This set-up is more complex compared with shared workbooks, but all users can work at same time without interference form others. Lately the workbook sharing is made better, but some 10 years ago it was practically useless. And anyway the sharing prevents you from using some features, which is not the case with this set-up.
    This is the EXACT function I need, but I do not know how to follow your information to set up my own system...

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    This is the case, where someone can only advice - the system must be set up on site.

    At first you have to decide, which info will be in which workbook. As this will be assembly information, then I can assume, that in every workstation are made some operations with some parts/articles. To avoid any misspellings, you must have a registry of articles, and a registry of operations. Probably the same article may be worked on different workstations - so article registry must be maintained in master workbook. And when child workbooks are for workstations of for workstation groups, then according operatins registry can be maintained in child workbook (or in master workbook, in case you want more control - and are ready for some extra work). And of course you need a workstation registry too - maintained in main workbook. Every such registry must be on own worksheet, and may advice is you define them as Tables.

    So let's assume you want to maintain all those registries in master workbook (MasterAssembly.xlsx)

    Add a sheet Workstations. On this sheet enter a header for 1st column like "Workstation". You can have additional columns when you have some info for every workstation used in calculations or reports later. Now select all headers, and an additional row (currently empty) below it, and select from menu Insert > Table and check 'My table has headers'. OK. You are directed to Design menu, where at left you can set a name for your table, p.e. tWorkstations. And to use the this workstation registry as source for Data validation list later, you have to define a named range. From menu Formulas select Name Manager, and create a name lWorkstations = tWorkstations[Workstation]. And fill the table at least with workstation codes (the column Workstation).

    Add a sheet Articles. Continue like previous sheet (probably you don't need to use the data validation list for articles in master table, so you may drop the name defining part) and define a table tArticles.

    Add a sheet Operations. On this sheet, the minimum headers you need are "Workstation", "Operation", "WSGroup" (optionally), "WSOperation". And of-course you again can have additional columns when you need, but keep WSOperation column as rightmost one, as you'll have formulas there. Define a table tOperations. Select cells in column Workstation below header (as many rows as you defined as table), and select from Data menu Data Validation > Allow: List, and into field Source enter the formula =lWorkstations. Now you can select workstations you entered into tWorkstations here, and you can't enter anything not in lWorkstations. Fill columns Workstations and Operations. Into column WSOperations enter the formula = [@Workstation] & ": " & [Operation].

    To read data from Excel workbook into other one using ODBC query are several ways. I myself prefer oldest (and foolproofest) one - defining non-dymanic named ranges in source workbook(s).
    Select all data (Headers included. NB! You can't have more than 1 header row for table, and all celected columns must have headers) p.e. from sheet workstations plus any amount of empty rows below your table (so you will be sure your table will never fill the selected range). From Formulas menu select Name Manager > New and set the name p.e. nWorkstations. OK. Repeat the same for other registries. Save the master workbook.

    Now create a child workbook, p.e. Assembly1.xlsx.

    You need registries for Articles and Operations her (and in case you use this workbook for several workstations, the workstation registry too). Add according worksheets.

    Activate cell A1 on sheet Articles, and select from Data menu From Othes Sources > From Microsoft Query, select 'Excel Files*' and press OK. Find and select MaterAssembly1.xlsx - you must see nAarticles listed in available tables. Select it and send all columns into right window. Next. Set the filter condition for Articlen column as 'Is Not Null'. Continue until Finish and in 'Import Data' window activate Properties and select 'Refresh data when opening the file'. OK until article registry is read form master workbook into Assambly1.xlsx. The returned data are defined as table by default - you can select menu Design and rename this table in meaningful way, p.e. tArticles.

    In similar way you can get other registries. When reading operations registry, you may add a filter condition so only operations for one workstation or WSGroup are queried, and it will be better to reorder columns so, that WSOperation is leftmost one (in working sheet you'll use WSOperation instead of Operation to avoid confusion later in master workbook.

    Now you define Article/WSOperation/(optionally Workstation) lists as named ranges. p.e. lArticles=tArticles[Article], lWSOperations=tOperations[WSOperation] etc. After that yo can start with designing the data entry sheet for Assembly1.xlsx.

    Then you define a non-dynamic range for data entry table, add a worksheet Assembly1 into main workbook, and read the info from Assembly1.xlsx into MainAssembly.xlsx.

    After that you can create a copy from Assembly1.xlsx as Assembly2.xlsx, edit queries there, and read data into MasterAssembly.xlsx, etc.

    At last, you define non-dynamic ranges for all Assembly sheets in master workbook, and create an UNION query to get consolidated data (the query syntax will be like SELECT a1.* FROM nAssembly1 a1 UNION SELECT a2.* FROM nAssembly2 a2 ... WHERE 1 Is Not Null)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-29-2017, 12:58 PM
  2. Create Excel File Through File Browser
    By kdbailey in forum Access
    Replies: 6
    Last Post: 04-21-2016, 10:56 PM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Replies: 5
    Last Post: 02-24-2016, 04:32 PM
  5. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums