Forgot to say that you need to add the reference to "Microsoft ActiveX Data
Objects 2.8 Library" in the VBA Tools menu...
--
Regards,
Luc.

"Festina Lente"


"PapaDos" wrote:

Not easy to do.
You need external help for this, in VBA, you can't access closed workbooks.
But you can use ADO objects in a UDF to accomplish what you want.

Try this, for example:

Function getFirstValue(wb_path As String, wb_name As String, name As String)
As Variant
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & wb_path & "\" & wb_name & ".xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
oRS.Open "Select * from " & name, oConn, adOpenStatic
getFirstValue = oRS.Fields(0).Value
oConn.Close
End Function

The first argument is the path to your files, the second the filename
(without extension) and the third is the defined name you are accessing.

I have no clue how heavy this approach is on system resources, or about its
execution speed...
--
Regards,
Luc.

"Festina Lente"


"insomniux" wrote:

Hi,
I have approx 100 excel sheets with exactly the same structure, each
containing a form with entered data. Each form holds approx 200
data-items in named cells (eg. Entry001, Entry002, ..). Now I want to
make a separate excel sheet with rows pointing to all the data-items in
all the forms. This will result in a sheet with approxe 100x200 cells
containing references.
I see two possible options, but I do not know how to implement them.

OPTION 1
Is it possible to use a VBA macro to populate the sheet with all the
formulas? Each formula will have the form of:

=FORM001.xls!Entry001,
=FORM001.xls!Entry002,
=FORM001.xls!Entry003,
...
=FORM002.xls!Entry001,
...
=FORM100.xls!Entry200,

The question is how to put a formula (not a string) in a cell (not the
iteration through the numbers).

OPTION 2
In the first column I make a list with numbers (001-100), in the first
row I make a list with cellnames (Entry001-Entry200). Is it possible to
make a generic formula (eg in B2) which refers to the cell in the first
column and the cell in the first row like:
=ReferenceToFileWithNameFrom(A2)!ReferenceToCellNa meIn(B1)

This solution would have my preference, but I do not know if excel
offers this possibility.

Thanks

Insomniux