why is it that for importing data from another spreadsheet this formul
works:

"=[User1.xls]Record!A2"

But,

Workbooks("User1.xls").Worksheets("Sheet1").Range( "A2")

...DOESN'T WORK IN VBA?

Matt

--
mattsva
-----------------------------------------------------------------------
mattsvai's Profile: http://www.excelforum.com/member.php...fo&userid=3113
View this thread: http://www.excelforum.com/showthread.php?threadid=50813

"mattsvai" wrote in
message ...

why is it that for importing data from another spreadsheet this formula
works:

"=[User1.xls]Record!A2"

But,

Workbooks("User1.xls").Worksheets("Sheet1").Range( "A2")

..DOESN'T WORK IN VBA?

Matts

With Workbooks("User1.xls")... User1.xls must be open.
Is it so?

Bruno

Looks like you are using the wrong sheet name

Workbooks("User1.xls").Worksheets("Record").Range( "A2")

Rather than

Workbooks("User1.xls").Worksheets("Sheet1").Range( "A2")


if you address the range correctly, it should work. It won't work on a
closed workbook, however.

--
Regards,
Tom Ogilvy



"mattsvai" wrote in
message ...

why is it that for importing data from another spreadsheet this formula
works:

"=[User1.xls]Record!A2"

But,

Workbooks("User1.xls").Worksheets("Sheet1").Range( "A2")

..DOESN'T WORK IN VBA?

Matts


--
mattsvai
------------------------------------------------------------------------
mattsvai's Profile:
http://www.excelforum.com/member.php...o&userid=31134
View this thread: http://www.excelforum.com/showthread...hreadid=508132

actually "Record" and "Sheet1" was used for hypothetical situation and
not a mistake.. thanks for pointing it out though.

I found out from Toms post in another thread that the workbook()
function cannot include paths of closed workbooks in VBA... so I guess
its a NO GO.


--
mattsvai
------------------------------------------------------------------------
mattsvai's Profile: http://www.excelforum.com/member.php...o&userid=31134
View this thread: http://www.excelforum.com/showthread...hreadid=508132

Tom and other's also suggested some other approachs.

Here is another one although this would be slower.

http://www.j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File


--
Regards,
Tom Ogilvy



"mattsvai" wrote in
message ...

actually "Record" and "Sheet1" was used for hypothetical situation and
not a mistake.. thanks for pointing it out though.

I found out from Toms post in another thread that the workbook()
function cannot include paths of closed workbooks in VBA... so I guess
its a NO GO.


--
mattsvai
------------------------------------------------------------------------
mattsvai's Profile:
http://www.excelforum.com/member.php...o&userid=31134
View this thread: http://www.excelforum.com/showthread...hreadid=508132