List the contents of a directory |
|
Author |
Message |
Ugo
|
Posted: Thu Feb 24 15:38:33 CST 2005 |
Top |
Excel Programming >> List the contents of a directory
I have a macro which will find a given directory - what I would like to be
able to do is to pick up a list of all the files in the directory and list
these in an Excel worksheet - starting at A1 and continuing down to A20 (if
there are 20 files listed in the directory)
Cheers
Gazza
Excel234
|
|
|
|
|
Jim
|
Posted: Thu Feb 24 15:38:33 CST 2005 |
Top |
Excel Programming >> List the contents of a directory
Diane,
My Excel add-in "List Files" does that.
Files and folders in the specified folder, directory or drive are listed.
Files in sub-folders are listed if requested.
Specific file types or names can be listed. (*.pdf, *.wav, etc)
Each file name, size, date and type go on the list.
Each file name on the list is hyperlinked.
All arranged in columns and rows that can be sorted.
Available - free - upon direct request.
Remove xxx from my email address
Regards,
Jim Cone
San Francisco, CA
> I have a macro which will find a given directory - what I would like to be
> able to do is to pick up a list of all the files in the directory and list
> these in an Excel worksheet - starting at A1 and continuing down to A20 (if
> there are 20 files listed in the directory)
> Cheers
> Gazza
|
|
|
|
|
jjonethalNOSPAM
|
Posted: Thu Feb 24 15:49:02 CST 2005 |
Top |
Excel Programming >> List the contents of a directory
Hello,
i gues you need something like:
Sub putDirToSheet(name as string)
Row = 1
a = Dir(name, vbDirectory)
Do Until a = ""
Cells(Row, 1) = a
Row = Row + 1
a = Dir()
Loop
End Sub
Afterwards you shold sort them.
Greetings Jonjo
> I have a macro which will find a given directory - what I would like to be
> able to do is to pick up a list of all the files in the directory and list
> these in an Excel worksheet - starting at A1 and continuing down to A20 (if
> there are 20 files listed in the directory)
>
> Cheers
>
> Gazza
>
>
>
|
|
|
|
|
Bob
|
Posted: Thu Feb 24 16:26:31 CST 2005 |
Top |
Excel Programming >> List the contents of a directory
This code lists all files in the folder and sub-folders, and provides
hyperlinks to them
Option Explicit
Dim FSO As Object
Dim cnt As Long
Dim arfiles
Dim level As Long
Sub Folders()
Dim i As Long
Dim sFolder As String
Dim iStart As Long
Dim iEnd As Long
Dim fOutline As Boolean
Set FSO = CreateObject("Scripting.FileSystemObject")
arfiles = Array()
cnt = -1
level = 1
sFolder = "E:\"
ReDim arfiles(2, 0)
If sFolder <> "" Then
SelectFiles sFolder
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Files").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
If arfiles(0, i) = "" Then
If fOutline Then
Rows(iStart + 1 & ":" & iEnd).Rows.Group
End If
With .Cells(i + 1, arfiles(2, i))
.Value = arfiles(1, i)
.Font.Bold = True
End With
iStart = i + 1
iEnd = iStart
fOutline = False
Else
.Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(2, i)), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(1, i)
iEnd = iEnd + 1
fOutline = True
End If
Next
.Columns("A:Z").ColumnWidth = 5
End With
End If
'just in case there is another set to group
If fOutline Then
Rows(iStart + 1 & ":" & iEnd).Rows.Group
End If
Columns("A:Z").ColumnWidth = 5
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.DisplayGridlines = False
End Sub
'-----------------------------------------------------------------------
Sub SelectFiles(Optional sPath As String)
'-----------------------------------------------------------------------
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim arPath
If sPath = "" Then
Set FSO = CreateObject("SCripting.FileSystemObject")
sPath = "c:\myTest"
End If
arPath = Split(sPath, "\")
cnt = cnt + 1
ReDim Preserve arfiles(2, cnt)
arfiles(0, cnt) = ""
arfiles(1, cnt) = arPath(level - 1)
arfiles(2, cnt) = level
Set oFolder = FSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
cnt = cnt + 1
ReDim Preserve arfiles(2, cnt)
arfiles(0, cnt) = oFolder.Path & "\" & oFile.Name
arfiles(1, cnt) = oFile.Name
arfiles(2, cnt) = level + 1
Next oFile
level = level + 1
For Each oSubFolder In oFolder.Subfolders
SelectFiles oSubFolder.Path
Next
level = level - 1
End Sub
#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(sText As String, _
Optional sDelim As String = " ") As Variant
'-----------------------------------------------------------------
Dim i%, sFml$, v0, v1
Const sDQ$ = """"
If sDelim = vbNullChar Then
sDelim = Chr(7)
sText = Replace(sText, vbNullChar, sDelim)
End If
sFml = "{""" & Application.Substitute(sText, sDelim, """,""") & """}"
v1 = Evaluate(sFml)
'Return 0 based for compatibility
ReDim v0(0 To UBound(v1) - 1)
For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next
Split = v0
End Function
'---------------------------------------------------------------------------
-----
Public Function InStrRev(stringcheck As String, _
ByVal stringmatch As String, _
Optional ByVal start As Long = -1)
'---------------------------------------------------------------------------
-----
Dim iStart As Long
Dim iLen As Long
Dim i As Long
If iStart = -1 Then
iStart = Len(stringcheck)
Else
iStart = start
End If
iLen = Len(stringmatch)
For i = iStart To 1 Step -1
If Mid(stringcheck, i, iLen) = stringmatch Then
InStrRev = i
Exit Function
End If
Next i
InStrRev = 0
End Function
'-----------------------------------------------------------------
#End If
--
HTH
RP
(remove nothere from the email address if mailing direct)
> I have a macro which will find a given directory - what I would like to be
> able to do is to pick up a list of all the files in the directory and list
> these in an Excel worksheet - starting at A1 and continuing down to A20
(if
> there are 20 files listed in the directory)
>
> Cheers
>
> Gazza
>
>
|
|
|
|
|
|
|