Converting a code to a UDF.

[omairhe]

Hello all,

The following code will extract filenames in a sheet called "File Extraction"

Sub Auto_Open()
  Dim FileSystem As Object
  Dim HostFolder As String
    HostFolder = "C:\Users\e314704\Desktop\LCST Gain Share\Reports"
    
  Set FileSystem = CreateObject("Scripting.FileSystemObject")
  DoFolder FileSystem.getFolder(HostFolder)
End Sub
Sub DoFolder(Folder)
  Dim SubFolder
  For Each SubFolder In Folder.SubFolders
    DoFolder SubFolder
  Next
  i = Sheets("File Extraction").Cells(Rows.Count, 1).End(xlUp).Row + 2
  Dim File
  For Each File In Folder.Files
    Sheets("File Extraction").Hyperlinks.Add Anchor:=Cells(i, 1), Address:= _
        File.Path, TextToDisplay:=File.Name
    i = i + 1
  Next
End Sub

However I need it to run as a UDF.

Anyone? Will appreciate. . .

 

[offthelip]

Why do you want to make this a UDF, it is not the sort of function you want to use in a cell on worksheet??