Hi all, seems like a nice place this!

I'm trying to get a macro that does the following:

Searches in a set folder for excel sheets
Lists them in a sheet
Makes the listed results hyperlinks to the listed sheet

WHich i have managed, and it works a treat.


Code
-------------------
Dim lCount As Long
Sheets("Existing Orders").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Range("B2").Select

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "J:\Purchase Orders\FM"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "Order FM*.xls"
Range("B2").Select
If .Execute 0 Then 'Workbooks in folder
Range("B2").Select
For lCount = 1 To .FoundFiles.Count 'Loop through all.
Range("B2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Cells(lCount, 1), Address:= _
.FoundFiles(lCount), TextToDisplay:= _
Replace(.FoundFiles(lCount), "J:\Purchase Orders\FM\", "")
Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select

End Su
-------------------


I have that running on workbook_open, and it's all fine.

But i really need to:

Specifiy which column and row the results start to show in - at th
moment it's A1, but it's going to be something i plan to roll ou
throughout the little company i work for so need it to look good (henc
not starting in the first column/row, so i can make it all pretty)

In the next column, call up a cell value from the sheet listed from th
filesearch.
For example, in column A it lists all the files found, but in B i wan
it to get cell value C4 from the sheets listed in column A. The shee
is an electronic ordering system (trying to convince the company t
reduce paper usage!) and the macro above lists all orders that hav
been placed. I would love to be able to list which Supplier it was sen
to next to the filesearch'd list of orders.

Any help would be immensely appreciated, thank you

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52347