Why...

[mattsvai]

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

[Bruno Campanini]

"mattsvai" <[email protected]> wrote in
message news:[email protected]...
>
> 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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[mattsvai]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>