VBA and Excel - Workbook opening problem

[Peter]

If I have workbook "one.xls" open and try to open a second workbook from
within a subroutine using

Workbooks.Open "E:\Data\Excel\ Two.xls"

"Two.xls" opens as expected.

but if I place the same line of code in a Function rather than a subroutine
"Two.xls" does not open!

Any ideas? All I'm trying to do is to extract some data from one sheet &
copy it to another.


--
Peter
To err is human but it takes a computer to really mess things up!

[Peter]

Re: VBA and Excel - Workbook opening problem

and heres another funny
If I use
LastRow = Cells.SpecialCe lls(xlCellTypeL astCell).Row
it works fine in the subroutine but returns 1 from the Function even
when my target workbook (Two.xls) is open

AHHHHHHHHHH
--
Peter
To err is human but it takes a computer to really mess things up!

"Peter" <gammaNO@SPAMbl ueyonder.co.uk> wrote in message
news:chy1e.1421 6$XH5.11163@fe1 .news.blueyonde r.co.uk...[color=blue]
> If I have workbook "one.xls" open and try to open a second workbook from
> within a subroutine using
>
> Workbooks.Open "E:\Data\Excel\ Two.xls"
>
> "Two.xls" opens as expected.
>
> but if I place the same line of code in a Function rather than a
> subroutine "Two.xls" does not open!
>
> Any ideas? All I'm trying to do is to extract some data from one sheet &
> copy it to another.
>
>
> --
> Peter
> To err is human but it takes a computer to really mess things up!
>[/color]

[Elef]

Re: VBA and Excel - Workbook opening problem

Are you aware of the following constraint when building functions to be
called from a worksheet formula (User Defined Functions) ?:

A function used in a formula can return a value. It cannot make any changes
to the workbook.

Hope it helps.
Bye
Elef

"Peter" <gammaNO@SPAMbl ueyonder.co.uk> ha scritto nel messaggio
news:chy1e.1421 6$XH5.11163@fe1 .news.blueyonde r.co.uk...[color=blue]
> If I have workbook "one.xls" open and try to open a second workbook from
> within a subroutine using
>
> Workbooks.Open "E:\Data\Excel\ Two.xls"
>
> "Two.xls" opens as expected.
>
> but if I place the same line of code in a Function rather than a
> subroutine "Two.xls" does not open!
>
> Any ideas? All I'm trying to do is to extract some data from one sheet &
> copy it to another.
>
>
> --
> Peter
> To err is human but it takes a computer to really mess things up!
>[/color]

[Peter]

Re: VBA and Excel - Workbook opening problem

Thanks. I was not aware of this restriction.
Presumably using Workbooks.Open counts as a change!
I did wonder if it was a quirk of using a Function and tried calling a
Subroutine from the Function with the Workbooks.Open command in the
subroutine but that fails too.

Peter


"Elef" <m.p.b@*toglimi *rocketmail.com > wrote in message
news:E8E1e.2397 4$kC3.6054@torn ado.fastwebnet. it...[color=blue]
> Are you aware of the following constraint when building functions to be
> called from a worksheet formula (User Defined Functions) ?:
>
> A function used in a formula can return a value. It cannot make any
> changes to the workbook.
>
> Hope it helps.
> Bye
> Elef
>
> "Peter" <gammaNO@SPAMbl ueyonder.co.uk> ha scritto nel messaggio
> news:chy1e.1421 6$XH5.11163@fe1 .news.blueyonde r.co.uk...[color=green]
>> If I have workbook "one.xls" open and try to open a second workbook from
>> within a subroutine using
>>
>> Workbooks.Open "E:\Data\Excel\ Two.xls"
>>
>> "Two.xls" opens as expected.
>>
>> but if I place the same line of code in a Function rather than a
>> subroutine "Two.xls" does not open!
>>
>> Any ideas? All I'm trying to do is to extract some data from one sheet &
>> copy it to another.
>>
>>
>> --
>> Peter
>> To err is human but it takes a computer to really mess things up!
>>[/color]
>
>[/color]

[Elef]

Re: VBA and Excel - Workbook opening problem

The restriction applies to Subs and functions called by the function also.
The idea behind this restriction is that a worksheet function should never
have side effects.
Bye
Elef

"Peter" <gammaNO@SPAMbl ueyonder.co.uk> ha scritto nel messaggio
news:j7P1e.1520 3$XH5.10489@fe1 .news.blueyonde r.co.uk...[color=blue]
> Thanks. I was not aware of this restriction.
> Presumably using Workbooks.Open counts as a change!
> I did wonder if it was a quirk of using a Function and tried calling a
> Subroutine from the Function with the Workbooks.Open command in the
> subroutine but that fails too.
>
> Peter
>
>
> "Elef" <m.p.b@*toglimi *rocketmail.com > wrote in message
> news:E8E1e.2397 4$kC3.6054@torn ado.fastwebnet. it...[color=green]
>> Are you aware of the following constraint when building functions to be
>> called from a worksheet formula (User Defined Functions) ?:
>>
>> A function used in a formula can return a value. It cannot make any
>> changes to the workbook.
>>
>> Hope it helps.
>> Bye
>> Elef
>>
>> "Peter" <gammaNO@SPAMbl ueyonder.co.uk> ha scritto nel messaggio
>> news:chy1e.1421 6$XH5.11163@fe1 .news.blueyonde r.co.uk...[color=darkred]
>>> If I have workbook "one.xls" open and try to open a second workbook from
>>> within a subroutine using
>>>
>>> Workbooks.Open "E:\Data\Excel\ Two.xls"
>>>
>>> "Two.xls" opens as expected.
>>>
>>> but if I place the same line of code in a Function rather than a
>>> subroutine "Two.xls" does not open!
>>>
>>> Any ideas? All I'm trying to do is to extract some data from one sheet
>>> & copy it to another.
>>>
>>>
>>> --
>>> Peter
>>> To err is human but it takes a computer to really mess things up!
>>>[/color]
>>
>>[/color]
>
>[/color]