Solved: Return name of word doc in same folder as excel sheet

[wilg]

I have this formula

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,)

which lets me know the path of my workbook up until my folder the excel workbook sits in.

Is there something similar for a formula that returns the name of a word doc that is in the folder and returns the name of that word doc in a cell in the excel worksheet?

[georgiboy]

I am not sure what you are trying to do here but from what i can work out you would like a list of files in your excel worksheet of the the files that exist within the same folder.

I am not sure how to do this in formulae, but here would be my approach in vba...
Will add a link to all documents in the same file location in active spreadsheet...

Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer

strPath = ThisWorkbook.Path & "\"
strFile = Dir(strPath)

While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend

'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:= _
colFiles(i), TextToDisplay:= _
colFiles(i)
Next i
End If

End Sub

Hope this helps

[wilg]

WOW!
Good work!! I'm sure I will be able to use this for what I want.
Solution works well.