Make a MsgBox show the value of a cell in a closed workbook

[johncassell]

Hi, I have a problem which is really starting to bug me!!

I cannot get a VB MsgBox to display the value of a cell in a different (Closed) workbook.

To test yourself (really appreciate if you could try this):

Make a new workbook on your C Drive C:\JohnCassell1.xls
Then make another one C:\JohnCassell2.xls

In Cell A1 of the JohnCassell2 workbook enter 99.

Then in cell A1 of JohnCassell1 type "=" then click on cell A1 of the other workbook.

Close that workbook.

The formula looks like this: ='C:\[JohnCassell2.xls]Sheet1'!$A$1

and shows a value of 99. So at least I can get a formula to show the value ok!

Then in VB Editor try and make a MsgBox which shows the value of the cell in the closed workbook. This is my attempt...

MsgBox C:\[JohnCassell2.xls]Sheet1'!$A$1.value

This is what I cannot do.

Just comes back with errors.

Any help at all would be really appreciated.


John

[davesexcel]

http://www.exceltip.com/exceltips.ph...tips&tipID=410

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


here's a few sites on pulling values from closed workbooks that could give you some ideas


http://www.erlandsendata.no/english/...dacimportwbado


I suppose if you can link a formula then, just msgbox whatever the range of the formula is

[mccreaso]

C:\JohnCassell2.xls
The formula looks like this: ='C:\[JohnCassell2.xls]Sheet1'!$A$1
MsgBox C:\[JohnCassell2.xls]Sheet1'!$A$1.value
John[/QUOTE]

See if this works

JohnCassell2.xls
A1 = 99

JohnCassell1.xls
A1 = 'C:\[JohnCassell2.xls]Sheet1'!$A$1

create this macro in JohnCassell1.xls

Please Login or Register  to view this content.

this seems to simple, so its probably not what your asking for!

but i hope it helps!

mccreaso

[johncassell]

Awesome! Thanks so much Dave.

Really Really Appreciated!!!

Cheers

John