Referring to a range in another workbook with VBA

Hi Chris,

Somebody has probably got a better idea, because this seems a bit longwinded, but at least it works. I wasn't able to refer to a range directly, but by entering a formula in a given cell which refers to the data in the closed workbook that you require you can then refer to that. Follow me?
The code below uses cell A1 in Sheet1 of your workbook to temporarily store to data, and then resets A1 to the formula that it previously contained. Obviously you'll need to change the name/path of the source book too.

Anyone got a simpler way? It seems like there should be one.

Sub GetDataFromClosedWorkbook()
Dim sExistingFormula As String
Dim vRequiredValue As Variant

sExistingFormula = Sheet1.Range("A1").Formula
Sheet1.Range("A1").Formula = "='C:\Documents and Settings\My Documents\[Test1.xls]Sheet1'!A1"
vRequiredValue = Sheet1.Range("A1")
Sheet1.Range("A1").Formula = sExistingFormula

MsgBox vRequiredValue
End Sub

Check out this great tip from John Walkenbach...http://j-walk.com/ss/excel/tips/tip82.htm

thanks tommygun. i am going through it now. i had a question maybe you can help:
i have :
f = "Budget.xls"

i want to replace budget.xls with whats listed in cell E3
so
f = E3

doesnt seem to be working, tried a few combinations. any idea?

cell E3 will list the .xls filename.

thanks.

Should be...
f = Range("E3").Value