Hi Bernie...

Thanks for all your help so far. Try and try as I might I just can't get
this thing to work. I know this might sound cheeky, but is there any chance
that you have a working example of this onto which I can transpose my own
data. I appreciate that this might be beyond your brief but I would
appreciate it massively...

Many thanks...

Gordon...



"Bernie Deitrick" wrote:

Gordon,

Just the cell address goes into row 1.

The macro goes into a codemodule in your workbook.

There are many good guides concerning getting started with macros: Here's
one that I often refer folks to:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The blank cells that you select must have filled in columns A:C in the same
row, and a cell address in row 1 of the same column. You will get that
error if the formula that is being created is invalid.

HTH,
Bernie
MS Excel MVP



"Gordon" wrote in message
...
Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest. Do you mean just the cell address (J12 J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new
table)
your 'blank' table, and run macro. Does this go into a module, workbook or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values, and
delete columns A and B. Not
sure what to do after that - depends on the population of your list (is
it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in
column A),
so I can rejoin them with your code to include brackets and single
commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail
me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list
for this.

And in cell D1, enter your cell address or name that you want to
create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your
'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are
randomly
named, though inside each file in a fixed cell there is a random
string
containing a random product number; I have been able to extract both
the file
path for each file and the random product number within the random
string.

I had tried to map the table using indirect.ext but I just get
#value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets
into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they
need to be found?

There are many ways to make the summary. Do you want links? Do you
want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in
one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that
lists the
directory/path of the 50 files but indirect and indirect.ext is
not working.
Any other ideas.

Thanks in advance

Gordon...