One way:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim FS As FileSearch
Dim myPath As String
Dim myFolderName As String
Dim myFileName As String

myPath = "C:\my documents\excel"

Set FS = Application.FileSearch

With FS
.NewSearch
.LookIn = myPath
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
If FolderAndName(.FoundFiles(i), myFolderName, myFileName) _
= False Then
MsgBox "something's ain't right!"
Else
ActiveSheet.Cells(i, 1).Formula _
= "'" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Function FolderAndName(WholeFileName As String, _
JustFolder As String, JustName As String) As Boolean
Dim iCtr As Long
FolderAndName = False
For iCtr = Len(WholeFileName) To 1 Step -1
If Mid(WholeFileName, iCtr, 1) = "\" Then
JustFolder = Left(WholeFileName, iCtr)
JustName = Mid(WholeFileName, iCtr + 1)
FolderAndName = True
Exit For
End If
Next iCtr
End Function

I actually didn't make it a formula. I figured for testing, you may want to
play with it a bit.

This line:
ActiveSheet.Cells(i, 1).Formula _
= "$$$$$'" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"

I used $$$$$ to make it text (for testing first):

ActiveSheet.Cells(i, 1).Formula _
= "='" & myFolderName & _
"[" & myFileName & "]GEMFEDCCOrderForm'!$K$38"

I'd try it, change it to an equal sign to see if it worked ok.



Darrin Henry wrote:

Thanks Dave, it worked perfectly. I knew I was on the right track, but
was not sure of the syntax to export it to the cell contents. Know that
this problem has been solved, I have another one. Currently the contents
of one cell is: P:\Credit Card Orders\Federal\CCBrogdon040705.xls. I
need to turn that into this: ='P:\Credit Card
Orders\Federal\[CCAshley022405.xls]GEMFEDCCOrderForm'!$K$38

Which will then show me what the contents of that cell is. Any ideas how
to code this? Or do you think there is a better way of doing it. Is it
possible to have the .FoundFiles property, output the results to
variables, something like:

DIM FILE1

FILE1 = NEXT i

I'm not even sure if that would work. But I know with variables, I could
probably do this myself. Also, I recorded a macro, where I manually did
the changes required, it is he

ActiveCell.FormulaR1C1 = _
"='P:\Credit Card _
Orders\Federal\[CCAshley022405.xls]GEMFEDCCOrderForm'!R38C11"

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson