This may not be exactly what you are looking for but this
code may help you. It is inserted Alt F11 into MEO sheet...
Adjust line 12 and 34 to state your specific drill to your
Excel files on your computer.
Dim FSO As Object
Dim cnt As Long
Dim arfiles
Dim level As Long
Sub Folders()
Dim i As Long
Dim sFolder As String
Set FSO = CreateObject("Scripting.FileSystemObject")
arfiles = Array()
cnt = -1
level = 1
sFolder = "C:\Documents and Settings\MY OWN PATH!!!"
ReDim arfiles(1, 0)
If sFolder < "" Then
SelectFiles sFolder
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles,
2)
.Hyperlinks.Add Anchor:=.Cells(i + 1,
arfiles(1, i)), _
Address:=arfiles(0,
i), _
TextToDisplay:=arfiles
(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If
End Sub
'----------------------------------------------------------
-------------
Sub SelectFiles(Optional sPath As String)
'----------------------------------------------------------
-------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
If sPath = "" Then
Set FSO = CreateObject
("SCripting.FileSystemObject")
sPath = "c:\\Documents and Settings\MY OWN PATH"
End If
Set Folder = FSO.GetFolder(sPath)
Set Files = Folder.Files
For Each file In Files
cnt = cnt + 1
ReDim Preserve arfiles(1, cnt)
arfiles(0, cnt) = Folder.Path & "\" & file.Name
arfiles(1, cnt) = level
Next file
level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.Path
Next
End Sub
Then try calling the information up using Sheet1!A1 for
example.
I'm using this code and it was supplied from this forum.
Sorry, the original author credit is buried somewhere in
my work!
HTH
-----Original Message-----
Hello, I am trying to create a formula that references
another spreadsheet. I
am able to do this at a manual level but what I am really
looking to do is to
list a number of file names on one sheet and have the
formula pull the file
names from that sheet and insert it into the reference
formula. Here is an
example of what I have tried that has not worked:
=IF(ISERROR('[ Sheet1!E3 ]Approval Summary'!
$C$13),0,'[ Sheet1!E3
]Approval Summary'!$C$13) + IF(ISERROR('[ Sheet1!E4 ]
Approval
Summary'!$C$13),0,'[ Sheet1!E4 ]Approval Summary'!
$C$13)
Where Sheet1!E3 would hold something like "abc.xls" If
anyone can help with
this I would greatly appreciate it.
Thank You,
Steve
.
|