Getting Data from another workbook without opening it

[wattsup]

Hi all,

I have written code that does exactly what I want it to do; which is find information from another workbook and populate a form so that a user can edit it. The problem is that my code opens the workbook and then closes it again which means that it takes too long. This function will be used hundreds of times a day by a number of users and taking ten seconds each time you open a record is not an option.

I'm pretty sure that I can do what I want to without opening the other workbook which will save a huge amount of time. Any advice would be greatly appreciated on how to change my code to speed it up: (The bold, italic section is probably the most relevant but I have posted the whole code.)

Please Login or Register  to view this content.

[6StringJazzer]

If you need to directly read from or write to a workbook from VBA, you must open the workbook. Excel formulas in the worksheet can have external references to other workbooks which don't require the workbooks to be open. One alternative might be to put formulas in the workbook someplace that refer to other workbooks, and have the code refer to those values. Another alternative would be to use the code to write those formulas, then read the resulting values (I have never tried this but I can't imagine why it wouldn't work).

By the way I have a small comment on your code. Using Workbook as a variable name is not advisable because Workbook is also a class name in Excel VBA. Although it will work it can get very confusing in some situations. An alternative here might be WorkbookName. Also
[code]
Workbook = "R:\hLog\" & "hLog" & ".xlsm"
' Any reason you didn't do this:
Workbook = "R:\hLog\hLog.xlsm"
[code]