collect data automatically from a folder

Dear Friends,

I have just signed here, so I am very new here.

I need your help for my problem:

suppose that i have a folder contains more than 200 text files (.txt) or may be other extension say .jpg, any way, I need a program to extract the names of these files and list them into an Excel sheet and set a hyperlink to these files, so that when i click any one in the Excel sheet it will be opened automatically.

by the way: I am using VBA which installed automatically with Excel 2007.

Thanks

Try to use the VBA code which i have mentioned below according to your need i hope that this will definitely help you to solve this issue.

Code:

---

Public Sub ListNames()
  Dim Directory As String
  Dim FileName As String
  Dim IndexSheet As Worksheet
  Dim r1 As Long
 
  'Change the directory below as needed
  Directory = "D:\Folder_name\"
  If Left(Directory, 1) <> "\" Then
      Directory = Directory & "\"
  End If
 
  r1 = 1
 
  Set IndexSheet = ThisWorkbook.ActiveSheet
     
  FileName = Dir(Directory & "*.*")
  Do While FileName <> ""
      IndexSheet.Cells(rw, 1).Value = FileName
      r1 = r1 + 1
      FileName = Dir
  Loop
 
  Set IndexSheet = Nothing
End Sub

---

Hope you got the result

I got it

thank you very much

regards