Reference a Closed Excel Workbook

[NickRed18]

First off, I am going to try to avoid using VBA due to other circumstances.

This is how I am operating at the moment:
I have 2 workbooks referencing cells off each other, my active workbook that I will be always using (let's call this Book 1) and my passive workbook (Book 2) that I would like to keep separate from the other. I have both workbooks open on my screen. I input a value into my input cell in Book 1, and then Book 2 pulls this cell value. Book 2 sorts through its sheets and tables to determine what values need to be outputted to its output cells. I then have Book 1 pulling values from Book 2's output cells. So it goes [input into Book 1] --> [Book 2 reads this input] --> [Book 2 processes data] --> [Book 2 outputs] --> [Book 1 reads output from Book 2].

What I want to happen is I want to have Book 2 closed. So when I enter in my input value into Book 1, I do not pull any output values from the close Book 2. Is it possible to fix this without VBA?

Thank you for the help,
Nick

 

[Fazza]

hi, Nick

If you're set up like a database, that sort of thing might be possible - in a limited way. (Depends, I think, on what processing needs be done in Book 2.) Without VBA it will be a bit more work - to manually execute the queries.

Such as from Book 1 run an UPDATE query to have the Book 1 input loaded into the closed Book 2. Without VBA this might mean opening MS Query or however you run queries in your Excel version. Then if the result back from Book 2 can be obtained by querying Book 2, then run that query from Book 1. Again, this would only work if Book 2 is basically data tables, not (whilst it remains a closed file) formulas. If you needed Book 2 to calculate formulas then the file would need to be opened.

With this specific set up - no formulas needing to be calculated in Book 2 - then instead of Book 2 being an Excel file it could be an mdb file, or other storage file/s.

For easier operation, or to have Book 2 calculate formulas or run code, then Book 2 would need to be opened. VBA would certainly make that easier to use.

HTH. regards, Fazza

 

[NickRed18]

Fazza,

Thank you for the reply! I see exactly what you're saying, and I think you solved my problem.
Book 2 is essentially multi-sheet data tables (the actual use of Book 2 is a list of tool numbers used for a certain part - multiple tools are used for each single part and the tools are sorted by what task they perform).
I have a single sheet in Book 2 that "searches" through every data table for all the tool numbers associated with a given part number. I am using formulas in this. I could just move this sheet from Book 2 to Book 1 - keeping Book 2 a static workbook without any formulas.

Thanks again!
Nick

 

[Fazza]

Good work, Nick