Refering to a Closed Workbook

[Carly]

I have used the following if statement in VBA

If Range("B2") = "Nov" Then _
MsgBox "Nov", vbInformation Else _
MsgBox "Error", vbInformation

This works fine but only if I open the workbook that I am referring to. Is it possible to work this statement without opening. I tried:

If Workbooks("\\sysdevweb\bw\PricingMethods\Data\Dates.xls").Sheets("Dates").Range("B2") = "Nov" Then _
MsgBox "Nov", vbInformation Else _
MsgBox "Error", vbInformation

but this brings back error - 'Subscript out of range'

Any help appreciated!

Thanks

 

[SIXTH SENSE]

hi!
try this one!
ActiveCell.FormulaR1C1 = "=[phonesasdf.xls]Sheet2!R1C1"

 

[SIXTH SENSE]

hi!
when the workbook is close use this one.

ActiveCell.FormulaR1C1 = "='C:\test\[phonesasdf.xls]Sheet2'!r1c1"

 

[onlyadrafter]

Hello,

Can you not have a cell in your open spreadsheet with = (and select the cell from the other sheet (e.g. =[clock.xls]Sheet2!$B$2).

You can then refer to this cell in you VB Code.

 

[Carly]

I don't want to create a reference within my open workbook to the closed workbook I just want to refer to the contents of the cell in the closed workbook.

i.e. IF (contents of cell in closed workbook) = "NOV" then MsgBox("ok")
else MsgBox("Not OK")

If I used ActiveCell.FormulaR1C1 = "=[phonesasdf.xls]Sheet2!R1C1", I would then have to refer to the activecell in the IF statement and then clear out the contents the remove the reference

 

[Andrew Poulsom]

Maybe you can use this:

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