Good evening,

This post is both a question and an answer I figured out years ago coding in Excel VBA through trial, error, and experimentation. My final hack was not mentioned anywhere online
and I'm hoping that this revelation may help someone in a similar situation.

The problem:
My code carried out calculations that involved finding items in a very large database list, and opening up this database workbook in my program was slowing my code down dramatically, sometimes up to 5 -10 minutes simply waiting for the database workbook to open in order to proceed reading off values.

The solution:
Don't open the database workbook. It is possible to actually copy the contents of an entire sheet of a closed workbook by referencing not just the Sheet in question and the workbook, but the entire file path of said workbook as part of articulating which sheet the information is being copied from. This copied information can now be pasted on a new sheet in the active workbook, thus granting you access to all the data from the closed database workbook without the slow process of opening up such a large file. This cut the running time of the 5-10 minutes database-workbook-opening part of the VBA to just 4-5 seconds. You can then simply delete the sheet that you created in order to paste the slow-loading database information onto when your code is done.

The Question:
I can send those interested a copy of the VBA code, which is really long and it has been sometime since I was familiar with the syntax and therefore it would prove quite a choke deciphering my own code again, but could someone kindly create an example VBA code that executes the above solution? Or would anyone be interested in helping me share my solution by having a read through my old VBA code and then pasting the relevant sections here for others who may languish under the burden of referencing workbooks of voluminous databases, the loading of which pollute one's code with a tardiness that render said code an embarrassment to any creator with the audacity to run it in full view of colleges in the workplace.

The Key Principle
You can access information from any closed workbook, by copying the cells/ sheets required, concatenating their referencing with the entire file path and filename of the closed workbook wherein they are contained and pasting the copied information in the open workbook, saving workbook loading times dramatically.


I hope this discovery helps, and I further hope a benevolent volunteer take up my offer make this idea clearer by way of a coded example.