Compare data from one worksheet to another workbook without opening it
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?
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
A1of the current worksheet to cell
Sheet1of 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")
Microsoft Press : How to Combine Data from Multiple Sources in Microsoft Excel 2016 (book excerpt)