How to set a variable based on a cell value that is in another not opened excel workbook?

[kjy1989]

Hi all,

my question is very simple. I am a vba beginner and I am having difficulties in some aspects of vba. My problem is how to set a variable based on a cell value that is in an external excel workbook that is also closed.

The code i wrote is this:

Please Login or Register  to view this content.

This code works but what extension do i write before the variable "one" and variable "two" if they are in an external file?
Say the location of the workbook is in C:\Users\Kevin\Google Drive\Pricelist\xyz.xlsm and in Sheet1 of xyz.xlsm. How do I get the value of Cell A1 and Cell B2 in that target location?

Im guessing something like:

Please Login or Register  to view this content.

something like that?

[JapanDave]

Try this,

Please Login or Register  to view this content.

[kjy1989]

Hi Japan Dave,

Your code works but my problem is that i don't want the workbook xyz.xlsm to be opened. I want it to work just in the same way a formula in another workbook (Book1) can call up data from xyz.xlsm by putting ='C:\Users\Kevin\Google Drive\Pricelist\[xyz.xlsm]Sheet1'!$A$1 in a cell in Book1 without opening xyz.xlsm. The reason being is that the xyz.xlsm contains confidential data that i dont want them to see. Plus xyx.xlsm has welcome messages and some macros set in workbook_open that will make it really not feasible.

[JapanDave]

The only way you are going to achieve that is if you link the workbooks via formula and having the data in the workbook that you are running the macro in. Maybe have it on a not visible sheet? You can't do what you are suggesting within code.

Or you could do something like this below which will disable any macros in the other workbook and if there is an error always have the workbook close so that the staff can't see it.

Please Login or Register  to view this content.

[jindon]

Assuming the sheet name is "Sheet1"

Please Login or Register  to view this content.