Compare data from one worksheet to another workbook without opening it

Question

Can anyone help me out with this VBA Macros query? I want to compare a worksheet with another worksheet in a different workbook. However, I don't want to open that workbook when comparing. I just want to changes to be shown in the current worksheet that I am using.

Is there something like a temporary data storage using Macros which stores the data temporarily from another workbook, compares it without ever physically opening the workbook?

Answer 1

You don't need VBA for this. You can use regular worksheets formulas, even if the other file isn't open.

For example, if we want to compare cell A1 of the current worksheet to cell A1 on worksheet Sheet1 of an (open or closed) Excel workbook saved as c:\myPath\myFile.xlsm, we could use:

=IF(A1='C:\myPath\[myFile.xlsm]Sheet1'!A1,"It's a Match!","Not a Match")

---

More Information:

• Office.com : Create an external reference (link) to a cell range in another workbook

• Microsoft Press : How to Combine Data from Multiple Sources in Microsoft Excel 2016 (book excerpt)