I need to get into an excel worksheet the results of a dir
function, (including sub directories), so that on the first coloumn
let say A will be the directory (including the sub directories),
on coloumn B the file name, and on coloumn C the file extention,
on coloumn D the file size and coloumn E the date the file was
changed.
My OS is windows XP and my excel ver is 2000.
The type of files should not include hidden files of system files
:)

Thanks for the help
rahel

--
Message posted from http://www.ExcelForum.com

Hi
have a look at
http://tinyurl.com/2y9e8

--
Regards
Frank Kabel
Frankfurt, Germany

I need to get into an excel worksheet the results of a dir
function, (including sub directories), so that on the first coloumn
let say A will be the directory (including the sub directories),
on coloumn B the file name, and on coloumn C the file extention,
on coloumn D the file size and coloumn E the date the file was
changed.
My OS is windows XP and my excel ver is 2000.
The type of files should not include hidden files of system files
:)

Thanks for the help
rahela


---
Message posted from http://www.ExcelForum.com/

Frank,

That solution neither handles sub-directories nor ignores hidden or system
files.

Here is my FSO solution.

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(2, 0)
arFiles(0, 0) = GetFolder()
If arFiles(0, 0) < "" Then
arFiles(1, 0) = level
SelectFiles arFiles(0, 0)

Worksheets.Add.Name = "Files"
With ActiveSheet
.Cells(1, 1).Value = "Path"
.Cells(1, 2).Value = "Filename"
.Cells(1, 3).Value = "Date Created"
.Rows(1).Font.Bold = True
.Columns("A:C").EntireColumn.AutoFit
cnt = 1
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
.Cells(i + 1, 1).Value = arFiles(0, i)
.Cells(i + 1, 2).Value = arFiles(1, i)
.Cells(i + 1, 3).Value = arFiles(2, i)
Next
End With
End If

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(ByVal sPath)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
If (file.Attributes And 2 Or _
file.Attributes And 4) Then
'2 is hidden, 4 is system
Else
cnt = cnt + 1
ReDim Preserve arFiles(2, cnt)
arFiles(0, cnt) = Folder.path
arFiles(1, cnt) = file.Name
arFiles(2, cnt) = Format(file.DateCreated, "dd mmm yyyy")
End If
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a folder.")
As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

'----------------------------- end-script -----------------------------


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
have a look at
http://tinyurl.com/2y9e8

--
Regards
Frank Kabel
Frankfurt, Germany

I need to get into an excel worksheet the results of a dir
function, (including sub directories), so that on the first coloumn
let say A will be the directory (including the sub directories),
on coloumn B the file name, and on coloumn C the file extention,
on coloumn D the file size and coloumn E the date the file was
changed.
My OS is windows XP and my excel ver is 2000.
The type of files should not include hidden files of system files
:)

Thanks for the help
rahela


---
Message posted from http://www.ExcelForum.com/

Hi Bob
nice one - stored in my archive

--
Regards
Frank Kabel
Frankfurt, Germany

Bob Phillips wrote:
Frank,

That solution neither handles sub-directories nor ignores hidden or
system files.

Here is my FSO solution.

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(2, 0)
arFiles(0, 0) = GetFolder()
If arFiles(0, 0) < "" Then
arFiles(1, 0) = level
SelectFiles arFiles(0, 0)

Worksheets.Add.Name = "Files"
With ActiveSheet
.Cells(1, 1).Value = "Path"
.Cells(1, 2).Value = "Filename"
.Cells(1, 3).Value = "Date Created"
.Rows(1).Font.Bold = True
.Columns("A:C").EntireColumn.AutoFit
cnt = 1
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
.Cells(i + 1, 1).Value = arFiles(0, i)
.Cells(i + 1, 2).Value = arFiles(1, i)
.Cells(i + 1, 3).Value = arFiles(2, i)
Next
End With
End If

End Sub


'----------------------------------------------------------------------
-
Sub SelectFiles(ByVal sPath)

'----------------------------------------------------------------------
-
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
If (file.Attributes And 2 Or _
file.Attributes And 4) Then
'2 is hidden, 4 is system
Else
cnt = cnt + 1
ReDim Preserve arFiles(2, cnt)
arFiles(0, cnt) = Folder.path
arFiles(1, cnt) = file.Name
arFiles(2, cnt) = Format(file.DateCreated, "dd mmm yyyy")
End If
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a
folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

'----------------------------- end-script
-----------------------------



"Frank Kabel" wrote in message
...
Hi
have a look at
http://tinyurl.com/2y9e8

--
Regards
Frank Kabel
Frankfurt, Germany

I need to get into an excel worksheet the results of a dir
function, (including sub directories), so that on the first coloumn
let say A will be the directory (including the sub directories),
on coloumn B the file name, and on coloumn C the file extention,
on coloumn D the file size and coloumn E the date the file was
changed.
My OS is windows XP and my excel ver is 2000.
The type of files should not include hidden files of system files
:)

Thanks for the help
rahela


---
Message posted from http://www.ExcelForum.com/

It uses recursion - I love recursion, don't know why, just do.

Bob

"Frank Kabel" wrote in message
...
Hi Bob
nice one - stored in my archive

--
Regards
Frank Kabel
Frankfurt, Germany

Bob Phillips wrote:
Frank,

That solution neither handles sub-directories nor ignores hidden or
system files.

Here is my FSO solution.

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(2, 0)
arFiles(0, 0) = GetFolder()
If arFiles(0, 0) < "" Then
arFiles(1, 0) = level
SelectFiles arFiles(0, 0)

Worksheets.Add.Name = "Files"
With ActiveSheet
.Cells(1, 1).Value = "Path"
.Cells(1, 2).Value = "Filename"
.Cells(1, 3).Value = "Date Created"
.Rows(1).Font.Bold = True
.Columns("A:C").EntireColumn.AutoFit
cnt = 1
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
.Cells(i + 1, 1).Value = arFiles(0, i)
.Cells(i + 1, 2).Value = arFiles(1, i)
.Cells(i + 1, 3).Value = arFiles(2, i)
Next
End With
End If

End Sub


'----------------------------------------------------------------------
-
Sub SelectFiles(ByVal sPath)

'----------------------------------------------------------------------
-
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
If (file.Attributes And 2 Or _
file.Attributes And 4) Then
'2 is hidden, 4 is system
Else
cnt = cnt + 1
ReDim Preserve arFiles(2, cnt)
arFiles(0, cnt) = Folder.path
arFiles(1, cnt) = file.Name
arFiles(2, cnt) = Format(file.DateCreated, "dd mmm yyyy")
End If
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a
folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

'----------------------------- end-script
-----------------------------



"Frank Kabel" wrote in message
...
Hi
have a look at
http://tinyurl.com/2y9e8

--
Regards
Frank Kabel
Frankfurt, Germany

I need to get into an excel worksheet the results of a dir
function, (including sub directories), so that on the first coloumn
let say A will be the directory (including the sub directories),
on coloumn B the file name, and on coloumn C the file extention,
on coloumn D the file size and coloumn E the date the file was
changed.
My OS is windows XP and my excel ver is 2000.
The type of files should not include hidden files of system files
:)

Thanks for the help
rahela


---
Message posted from http://www.ExcelForum.com/

rahela,

Or you could use my Excel add-in "List Files".
Comes with one page Word.doc install/use instructions.
Very easy to use and available - free - upon direct request.
(remove xxx from my e-mail address)

Regards,
Jim Cone
San Francisco, CA
XX

"rahela " wrote in message
...
I need to get into an excel worksheet the results of a dir
function, (including sub directories), so that on the first coloumn
let say A will be the directory (including the sub directories),
on coloumn B the file name, and on coloumn C the file extention,
on coloumn D the file size and coloumn E the date the file was
changed.
My OS is windows XP and my excel ver is 2000.
The type of files should not include hidden files of system files :)
Thanks for the help
rahela

Bob is the "file size" property missing with your codes?

Hi Martyn
change Bob's code as follows (I also corrected the overwriting of the
heading row):

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(3, 0)
arFiles(0, 0) = GetFolder()
If arFiles(0, 0) < "" Then
arFiles(1, 0) = level
SelectFiles arFiles(0, 0)

Worksheets.Add.Name = "Files"
With ActiveSheet
.Cells(1, 1).Value = "Path"
.Cells(1, 2).Value = "Filename"
.Cells(1, 3).Value = "Date Created"
.Cells(1, 4).Value = "Filesize"
.Rows(1).Font.Bold = True
.Columns(4).NumberFormat = "#,##0 "" KB"""
.Columns("A:D").EntireColumn.AutoFit
cnt = 1
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
.Cells(i + 2, 1).Value = arFiles(0, i)
.Cells(i + 2, 2).Value = arFiles(1, i)
.Cells(i + 2, 3).Value = arFiles(2, i)
.Cells(i + 2, 4).Value = arFiles(3, i) / 1024
Next
End With
End If

End Sub

'----------------------------------------------------------------------
-
Sub SelectFiles(ByVal sPath)
'----------------------------------------------------------------------
-
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
If (file.Attributes And 2 Or _
file.Attributes And 4) Then
'2 is hidden, 4 is system
Else
cnt = cnt + 1
ReDim Preserve arFiles(3, cnt)
arFiles(0, cnt) = Folder.path
arFiles(1, cnt) = file.Name
arFiles(2, cnt) = Format(file.DateCreated, "dd mmm yyyy")
arFiles(3, cnt) = file.Size
End If
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a
folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function



--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Bob is the "file size" property missing with your codes?

Hi
you may also make the following change to Bob's (excellent) procedu
Move the line
..Columns("A:D").EntireColumn.AutoFit
directly after the For - Next loop to ensure the autofitting of columns
A-D

To Bob: Hope you didn't mind I changed your code <vbg


--
Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel wrote:
Hi Martyn
change Bob's code as follows (I also corrected the overwriting of the
heading row):

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object
Dim cnt As Long
Dim level As Long
Dim arFiles

Sub Folders()
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

arFiles = Array()
cnt = 0
level = 1

ReDim arFiles(3, 0)
arFiles(0, 0) = GetFolder()
If arFiles(0, 0) < "" Then
arFiles(1, 0) = level
SelectFiles arFiles(0, 0)

Worksheets.Add.Name = "Files"
With ActiveSheet
.Cells(1, 1).Value = "Path"
.Cells(1, 2).Value = "Filename"
.Cells(1, 3).Value = "Date Created"
.Cells(1, 4).Value = "Filesize"
.Rows(1).Font.Bold = True
.Columns(4).NumberFormat = "#,##0 "" KB"""
.Columns("A:D").EntireColumn.AutoFit
cnt = 1
For i = LBound(arFiles, 2) To UBound(arFiles, 2)
.Cells(i + 2, 1).Value = arFiles(0, i)
.Cells(i + 2, 2).Value = arFiles(1, i)
.Cells(i + 2, 3).Value = arFiles(2, i)
.Cells(i + 2, 4).Value = arFiles(3, i) / 1024
Next
End With
End If

End Sub


'----------------------------------------------------------------------
-
Sub SelectFiles(ByVal sPath)

'----------------------------------------------------------------------
-
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
If (file.Attributes And 2 Or _
file.Attributes And 4) Then
'2 is hidden, 4 is system
Else
cnt = cnt + 1
ReDim Preserve arFiles(3, cnt)
arFiles(0, cnt) = Folder.path
arFiles(1, cnt) = file.Name
arFiles(2, cnt) = Format(file.DateCreated, "dd mmm yyyy")
arFiles(3, cnt) = file.Size
End If
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a
folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function




Martyn wrote:
Bob is the "file size" property missing with your codes?

"Frank Kabel" wrote in message
...
Hi
you may also make the following change to Bob's (excellent) procedu
Move the line
.Columns("A:D").EntireColumn.AutoFit
directly after the For - Next loop to ensure the autofitting of columns
A-D

Thanks, didn't spot that, just added it at the end after testing to save the
OP having to do it manually (when will I ever learn?).



To Bob: Hope you didn't mind I changed your code <vbg



Absolutely not Frank, it is not only helpful, you might do something I would
not have thought of, so all benefit. It's a co-operative here, part of it's
richness, and I am not averse to adding my bit to other's postings. I was
unavailable for the past 2 hours anyway, had to make some flapjacks

Thanks for the additions Frank. Of course most appreciation goes to Bob...

Absolutely not Frank, it is not only helpful, you might do something
I would not have thought of, so all benefit. It's a co-operative
here, part of it's richness, and I am not averse to adding my bit to
other's postings. I was unavailable for the past 2 hours anyway, had
to make some flapjacks

Sound delicious. So you semm to enjoy your weekend :-)
Frank

Frank Kabel wrote:
*
Absolutely not Frank, it is not only helpful, you might d
something
I would not have thought of, so all benefit. It's a co-operative
here, part of it's richness, and I am not averse to adding my bi
to
other's postings. I was unavailable for the past 2 hours anyway
had
to make some flapjacks

Sound delicious. So you semm to enjoy your weekend :-)
Frank *

Hi Dear fellows,
Do I need to choose some library in the vbe, before I could use tha
programe? what I have forgot to mention is that I use
a local net. Would the programe above will be good for a net too?
From the discussion above, and the corrections, which
is the last best version (I am not an expert, and quite frankly
I got lost along the lines........) to use?
Thanks a zilio

--
Message posted from http://www.ExcelForum.com

Rahela,

You don't need a library as the code uses late binding (specifically to
avoid such problems).

It should work on a network as I included a Folder browser, and as long as
you have mapped drives to the network server(s), so you pick your folder to
list.

Use Frank's last amendment.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rahela " wrote in message
...
Frank Kabel wrote:
*
Absolutely not Frank, it is not only helpful, you might do
something
I would not have thought of, so all benefit. It's a co-operative
here, part of it's richness, and I am not averse to adding my bit
to
other's postings. I was unavailable for the past 2 hours anyway,
had
to make some flapjacks

Sound delicious. So you semm to enjoy your weekend :-)
Frank *

Hi Dear fellows,
Do I need to choose some library in the vbe, before I could use that
programe? what I have forgot to mention is that I use
a local net. Would the programe above will be good for a net too?
From the discussion above, and the corrections, which
is the last best version (I am not an expert, and quite frankly
I got lost along the lines........) to use?
Thanks a zilion


---
Message posted from http://www.ExcelForum.com/