How to get the list of files in a folder

  • Thread starter Thread starter yang
  • Start date Start date
Y

yang

Can someone help me to get name of all the files in the
folder in excel with the hyperlinks to the same within
excel? It should be able to choose the folder by user so
that my all the users can use it. I have tried using macro
recorder but failed. This reqd for the companys huge data
base sorted and kept in different folders on the intranet.
 
It might be better to have the list and then just use a double click event
to goto the file.

This should list the files

Sub GetFileList()
Dim iCtr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\yourfolder"
' .SearchSubFolders = True
.Filename = ".xls"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

Then this should goto the file by a double click on the typed name.
========
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Sheets(ActiveCell.Value).Select
ActiveSheet.Range("a4").Select
End If
Application.DisplayAlerts = True
End Sub

Sub GetWorkbook()
If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows("" & workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
Exit Sub
End Sub
=========
 
Back
Top