Worksheet function or VB?
 

Hi:

Does anyone know of a way to return all of the file names within a folder
automatically. I have to create a formula with cells from over 800 files.....

Thanks,

Worksheet function or VB?
 

Here is some code for searching for files. Run Test. The last two arguments
of ListFiles are optional. The thrid being to specify a specific type of file
to look for and the last being a boolean to indicate whether you want to
seach the sub-directories. To use this code you MUST reference "Microsoft
Scripting Runtime" (Tools - References...)...

Option Explicit
Option Compare Text

Sub test()
Call ListFiles("C:\Test", Sheet1.Range("A2"), "xls", True)
End Sub

Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*", _
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String

'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'-------------------

--
HTH...

Jim Thomlinson


"Theresa" wrote:

Hi:

Does anyone know of a way to return all of the file names within a folder
automatically. I have to create a formula with cells from over 800 files.....

Thanks,

Worksheet function or VB?
 

Try something like

Dim FName As String
Dim Rng As Range
Set Rng = Range("A1")
FName = Dir("H:\Temp\*.*")
Do Until FName = ""
Rng.Value = FName
Set Rng = Rng(2, 1)
FName = Dir()
Loop




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Theresa" wrote in message
...
Hi:

Does anyone know of a way to return all of the file names
within a folder
automatically. I have to create a formula with cells from over
800 files.....

Thanks,

Worksheet function or VB?
 

Theresa,

You can use a macro to list the files: see the first macro, below.

You can also create a link to all the files: see the second macro, below.
The link will pull out the value from cell A1 from Sheet1 - change the A1 to the cell that you want,
and the sheet name as appropriate.

For either, change the .Lookin line to your folder path and name.

HTH,
Bernie
MS Excel MVP

Sub FindFiles2()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub



Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub

"Theresa" wrote in message
...
Hi:

Does anyone know of a way to return all of the file names within a folder
automatically. I have to create a formula with cells from over 800 files.....

Thanks,

Worksheet function or VB?
 

Hi Bernie:

Your second macro is the best solution for me, but it keeps hanging at the
following line:

Cells(myCount, 2).Formula = MyFormula

Any suggestions?

I have to total the same cell from a number of files.

Thanks,

Theresa

"Bernie Deitrick" wrote:

Theresa,

You can use a macro to list the files: see the first macro, below.

You can also create a link to all the files: see the second macro, below.
The link will pull out the value from cell A1 from Sheet1 - change the A1 to the cell that you want,
and the sheet name as appropriate.

For either, change the .Lookin line to your folder path and name.

HTH,
Bernie
MS Excel MVP

Sub FindFiles2()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub



Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub

"Theresa" wrote in message
...
Hi:

Does anyone know of a way to return all of the file names within a folder
automatically. I have to create a formula with cells from over 800 files.....

Thanks,

Worksheet function or VB?
 

Theresa,

It's probably due to the capitalization of the folder name/path. Try changing

MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"

to

MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(LCase(.FoundFiles(i)), LCase(.LookIn) & "\", "") _
& "]Sheet1'!A1"

And make sure that you don't have a \ at the end of the .LookIn = "C....."

HTH,
Bernie
MS Excel MVP


"Theresa" wrote in message
...
Hi Bernie:

Your second macro is the best solution for me, but it keeps hanging at the
following line:

Cells(myCount, 2).Formula = MyFormula

Any suggestions?

I have to total the same cell from a number of files.

Thanks,

Theresa

"Bernie Deitrick" wrote:

Theresa,

You can use a macro to list the files: see the first macro, below.

You can also create a link to all the files: see the second macro, below.
The link will pull out the value from cell A1 from Sheet1 - change the A1 to the cell that you
want,
and the sheet name as appropriate.

For either, change the .Lookin line to your folder path and name.

HTH,
Bernie
MS Excel MVP

Sub FindFiles2()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub



Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub

"Theresa" wrote in message
...
Hi:

Does anyone know of a way to return all of the file names within a folder
automatically. I have to create a formula with cells from over 800 files.....

Thanks,

Worksheet function or VB?
 

Bernie:

Thanks very much for your help. My problem was there were a couple of
erroneous files inside my folder. Everything works great!

"Bernie Deitrick" wrote:

Theresa,

It's probably due to the capitalization of the folder name/path. Try changing

MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"

to

MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(LCase(.FoundFiles(i)), LCase(.LookIn) & "\", "") _
& "]Sheet1'!A1"

And make sure that you don't have a \ at the end of the .LookIn = "C....."

HTH,
Bernie
MS Excel MVP


"Theresa" wrote in message
...
Hi Bernie:

Your second macro is the best solution for me, but it keeps hanging at the
following line:

Cells(myCount, 2).Formula = MyFormula

Any suggestions?

I have to total the same cell from a number of files.

Thanks,

Theresa

"Bernie Deitrick" wrote:

Theresa,

You can use a macro to list the files: see the first macro, below.

You can also create a link to all the files: see the second macro, below.
The link will pull out the value from cell A1 from Sheet1 - change the A1 to the cell that you
want,
and the sheet name as appropriate.

For either, change the .Lookin line to your folder path and name.

HTH,
Bernie
MS Excel MVP

Sub FindFiles2()
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub



Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub

"Theresa" wrote in message
...
Hi:

Does anyone know of a way to return all of the file names within a folder
automatically. I have to create a formula with cells from over 800 files.....

Thanks,