I'm stumbling along with following:
The idea is to get a list of all workbooks INCLUDING addins.

How do I get following (or any xl4 macro command) to return an array?


Function AllBooks() As Variant
Dim v
v = ExecuteExcel4Macro("Documents(3)")
Stop
End Function

For the moment I circumvent this by using a name object...
but I'm sure there must be a more elegant way of doing this.

keepITcool
amsterdam, holland

anyone.. please?,pretty please? I need a response!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


keepitcool wrote:

I'm stumbling along with following:
The idea is to get a list of all workbooks INCLUDING addins.

How do I get following (or any xl4 macro command) to return an array?


Function AllBooks() As Variant
Dim v
v = ExecuteExcel4Macro("Documents(3)")
Stop
End Function

For the moment I circumvent this by using a name object...
but I'm sure there must be a more elegant way of doing this.

keepITcool
amsterdam, holland

Hi KeepItCool

I'm afraid I don't have your answer, only a question.
Apart from needing to use a name object I also need to use
the worksheet, like this:

Sub Tester()
Dim r As Range, c, v()
ActiveWorkbook.Names.Add Name:="test", _
RefersToR1C1:="=DOCUMENTS(3)"
Set r = Rows(1)
r.FormulaArray = "=test"
c = r.SpecialCells(xlCellTypeFormulas, 2).Count
ReDim v(1 To c)
For i = 1 To c
v(i) = r.Cells(i)
Debug.Print v(i)
Next
End Sub

Not elegant! Do you have a method that does not require
the worksheet?

TIA
Peter

-----Original Message-----
anyone.. please?,pretty please? I need a response!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


keepitcool wrote:

I'm stumbling along with following:
The idea is to get a list of all workbooks INCLUDING
addins.

How do I get following (or any xl4 macro command) to
return an array?


Function AllBooks() As Variant
Dim v
v = ExecuteExcel4Macro("Documents(3)")
Stop
End Function

For the moment I circumvent this by using a name
object...
but I'm sure there must be a more elegant way of doing
this.

keepITcool
amsterdam, holland


.

i use:
Sub ff()
With ActiveWorkbook
.Names.Add "tmp", "=Documents(3)"
v = [tmp]
.Names("tmp").Delete
End With
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Peter T" wrote:

Hi KeepItCool

I'm afraid I don't have your answer, only a question.
Apart from needing to use a name object I also need to use
the worksheet, like this:

Sub Tester()
Dim r As Range, c, v()
ActiveWorkbook.Names.Add Name:="test", _
RefersToR1C1:="=DOCUMENTS(3)"
Set r = Rows(1)
r.FormulaArray = "=test"
c = r.SpecialCells(xlCellTypeFormulas, 2).Count
ReDim v(1 To c)
For i = 1 To c
v(i) = r.Cells(i)
Debug.Print v(i)
Next
End Sub

Not elegant! Do you have a method that does not require
the worksheet?

TIA
Peter

-----Original Message-----
anyone.. please?,pretty please? I need a response!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


keepitcool wrote:

I'm stumbling along with following:
The idea is to get a list of all workbooks INCLUDING
addins.

How do I get following (or any xl4 macro command) to
return an array?


Function AllBooks() As Variant
Dim v
v = ExecuteExcel4Macro("Documents(3)")
Stop
End Function

For the moment I circumvent this by using a name
object...
but I'm sure there must be a more elegant way of doing
this.

keepITcool
amsterdam, holland


.

AhHa, thanks

Seems already elegant to me <g

Peter


-----Original Message-----
i use:
Sub ff()
With ActiveWorkbook
.Names.Add "tmp", "=Documents(3)"
v = [tmp]
.Names("tmp").Delete
End With
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Peter T" wrote:

Hi KeepItCool
<snip

but it should be ..nah MUST be .. doable with

evaluate() and some string manipulation involving {}...
I'm sure!

problem is with name objects that some remnants
(hidden namespace) stay in memory...
and may lead to problems.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Peter T" wrote:

AhHa, thanks

Seems already elegant to me <g

Peter


-----Original Message-----
i use:
Sub ff()
With ActiveWorkbook
.Names.Add "tmp", "=Documents(3)"
v = [tmp]
.Names("tmp").Delete
End With
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Peter T" wrote:

Hi KeepItCool
<snip

KeepITcool,

but it should be ..nah MUST be .. doable with

evaluate() and some string manipulation involving {}...
I'm sure!

Hope you're right, I'm not so sure but I'll be watching.

problem is with name objects that some remnants
(hidden namespace) stay in memory...
and may lead to problems.

I trust this only applies to a name that "refersto" an XLM,
I add/delete normal names all the time.

According to Laurent Long
http://www.cpearson.com/excel/hidden.htm
the "hidden namespace" is at application level so I assume
this is not related to a "normal" workbook level name, at
least I hope not!

Regards,
Peter