refering to another closed workbook 2

[Yogi39]

how do I refer to sheet 1
range D1
of workbook Test
residing in C:\johnny

and assign to variable Data ?

 

[flashb]

Perhaps I am misinterpreting your query to be too simplistic?


='C:\Johny\[test.xls]Sheet1'!$D$1

AshB

 

[Yogi39]

will that work in a macro or just as a cell formula, I would like to incorporate in some VB macro I'm building...

 

[flashb]

thinking about a simple solution, you could open the spreadsheet in the VB macro and then close it once you have picked the information you are refering to. Eg.

Workbooks.Open Filename:="C:\Johny\test.xls
Sheets("Sheet1&quot.Select
Range("D1&quot.Select

and then do what you will to the selected cell!

To then auto close the 'test' spreadsheet you could use

Windows("test.xls&quot.Activate
ActiveWindow.Close


this may help, but there may be another way of refering to the external source without actually opening it?

AshB

 

[Yogi39]

Thanks !

 

[Yogi39]

I got this to work and I'm sure there is a way like you say to do this without actualy opening the Workbook.
Thanks again for teh help !

Workbooks.Open Filename:="C:\Johnny\Test.xls"
d = Workbooks("Test.xls").Sheets("Sheet1").Range("$D$1").Value
Workbooks("Test.xls").Close

 

[rmikesmith]

John Walkenbach presents a VBA function to access data from a closed Excel workbook on his website. The link to his code is:

http://www.j-walk.com/ss/excel/tips/tip82.htm

HTH
M. Smith