Spreadsheet Showing All Things In A Folder?

[Ste_Moore01]

Hi all,

I'm trying to make a Spreadsheet that shows all the files in a folder and automatically updates them when a new file is added.

Is there any way of doing this without having to do it manually?

 

[GlennUK]

I'm curious .... why are you trying to do this in Excel? What will the user do with the list once it's been created?

 

[Ste_Moore01]

I have many spreadsheets in a folder for claims to a courier company and I was hoping to keep a record of the filename in a table as I've named each file as the invoice number I'm claiming against.

I'm also creating a menu which I've spoke about in another topic and I was hoping that I could use this spreadsheet to open each of the claims.

 

[GlennUK]

Think about how you want this to operate. You've said you want the list to update automatically, yet you've hinted that you want to store information in the worksheet regarding what each filename represents .... so when the list updates, how do you want the information to be associated with the changing positions of the filenames? That's if I have got my assumptions correct of course.

 

[Ste_Moore01]

Each spreadsheet in this particular folder is arranged in the same way (i.e. cell B1 is the invoice number, cell A2 is the Consignment Number for the claim and cell A41 is the amount I want to claim) but there are also word documents in this folder.

If I was able to make this sheet I would need a formula in column A or some VBA code to find out all the filenames in the folder and list them in the table. Then column B would need a formula that would workout the "Claim Amount" if column A was an xls file. Column C would then need a hyperlink to each file so I could link to it quickly.

Book1.xls
	A	B	C	D
1	FILENAME	CLAIM AMOUNT	HYPERLINK
2	INV 012345.xls	=if(A2=AN XLS FILE,'C:\CLAIMS & CREDIT REQUESTS\[FILENAME]CLAIM LETTER'!$A$41,"THIS IS NOT A EXCEL FILE")	CLICK HERE
3	INV 012345.xls	=if(A3=AN XLS FILE,'C:\CLAIMS & CREDIT REQUESTS\[FILENAME]CLAIM LETTER'!$A$41,"THIS IS NOT A EXCEL FILE")	CLICK HERE
4	INV 012345.xls	=if(A4=AN XLS FILE,'C:\CLAIMS & CREDIT REQUESTS\[FILENAME]CLAIM LETTER'!$A$41,"THIS IS NOT A EXCEL FILE")	CLICK HERE
5	INV 012345.xls	=if(A5=AN XLS FILE,'C:\CLAIMS & CREDIT REQUESTS\[FILENAME]CLAIM LETTER'!$A$41,"THIS IS NOT A EXCEL FILE")	CLICK HERE
6	DEBIT NOTE 1.doc	=if(A6=AN XLS FILE,'C:\CLAIMS & CREDIT REQUESTS\[FILENAME]CLAIM LETTER'!$A$41,"THIS IS NOT A EXCEL FILE")	CLICK HERE
Sheet1

 

[Dsuperc]

The ASAP Utility, (an add-in, see the add-in folder) has this capability.

 

[Ste_Moore01]

Sorry..

Whereabouts is the add-in folder?

 

[KenCriss]

http://www.mrexcel.com/board2/viewtopic.php?t=131224

 

[Ste_Moore01]

Silly me!

I thought it was a link on the main site not on the board!
lol

Thanks I'm gonna try it now.

 

[wwbwb]

Try this. I worked with this a year or two ago

Sub ListFiles()
    Application.ScreenUpdating = False
    Range("a5:b65536").ClearContents
    Range("a5").Select
    Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim I As Integer
    Dim myfind As Integer, mylen As Integer, mynum As Integer
    fPath = Range("A1")
    fName = Dir(fPath & "*.*")
    While fName <> ""
        I = I + 1
        ReDim Preserve fileList(1 To I)
        fileList(I) = fName
        fName = Dir()
    Wend
    If I = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     For I = 1 To UBound(fileList)
     myfind = WorksheetFunction.Find(".", fileList(I), 1) - 1
     mylen = Len(fileList(I))
     mynum = mylen - myfind
     If Range("b1") = "x" Then
        Range("A" & I + 4).Value = "=hyperlink(""" & fPath & fileList(I) & """)"
        Range("A1").Select
    Else
        Range("A" & I + 4).Value = Left(fileList(I), myfind)
        Range("B" & I + 4).Value = Right(fileList(I), mynum)
        Range("A:A").Select
        Selection.Font.ColorIndex = 1
        Selection.Font.Underline = xlUnderlineStyleNone
        Range("A1").Select
    End If
     Next
ActiveSheet.Cells.EntireColumn.AutoFit


Dim NewName As String, FileName As String, OldName As String

With ActiveSheet
    OldName = .Name 'Get the sheet name which will be changed
    FileName = .Cells(1, 1).Value 'Get the file name from which to generate a new sheet name
End With

NewName = Left(FileName, InStrRev(FileName, "\") - 1) 'Drops the ending "\" from the file name.  The filename is now  "X:\XXX\XXX\...\Name"
NewName = Mid(NewName, Len(Left(NewName, InStrRev(NewName, "\"))) + 1) 'Takes all the characters following the last "\"

Worksheets(OldName).Name = NewName 'Renames sheet


Application.ScreenUpdating = True
    End Sub