Hi Guys,

I need some help, I am new to VB and I am looking to create an excel
sheet that I can point at a folder and and create a file listing from
it. For example:

File Name, Creation date, file type, file size.

I would like to have just one button that asks me to point a the folder
and it does the rest.

any advice would be really helpful

Peter


--
PeterO
------------------------------------------------------------------------
PeterO's Profile: http://www.excelforum.com/member.php...o&userid=24313
View this thread: http://www.excelforum.com/showthread...hreadid=379186

"PeterO" wrote in
message ...
:
: Hi Guys,
:
: I need some help, I am new to VB and I am looking to create an excel
: sheet that I can point at a folder and and create a file listing from
: it. For example:
:
: File Name, Creation date, file type, file size.
:
: I would like to have just one button that asks me to point a the folder
: and it does the rest.
:
: any advice would be really helpful
:
: Peter

Here is a routine I put together using some code I found here and some I
wrote. Place all of this in a new module or in a specific sheet code.
Create a button and point it to run CreateFolderList. I don't provide a
title row but you could easily enough record a macro to add the titles.
Paul D

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 ' used by the function GetFolderName
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

Sub CreateFolderList()
Dim ff As Object, f As Object
Dim temp As Variant
Dim iRow As Integer
Dim fso As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
iRow = 1
Set ff = fso.GetFolder(GetFolderName)
Application.Cursor = xlWait
For Each f In ff.Files
Cells(iRow, 1).Value = f.Name
Cells(iRow, 2).Value = f.DateCreated
Cells(iRow, 3).Value = f.DateLastModified
Cells(iRow, 4).Value = f.Type
Cells(iRow, 5).Value = Format(f.Size / 1024, "#0.0") & "KB"
iRow = iRow + 1
Next
Application.Cursor = xlDefault
Application.ScreenUpdating = True
Set fso = Nothing
End Sub

''VBA macro tip contributed by Erlandsen Data Consulting
''offering Microsoft Excel Application development, template customization,
''support and training solutions

Function GetFolderName(Optional Msg As String) As String
' returns the name of the folder selected by the user
Dim bInfo As BROWSEINFO, path As String, r As Long
Dim X As Long, pos As Integer
bInfo.pidlRoot = 0& ' Root folder = Desktop
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
' the dialog title
Else
bInfo.lpszTitle = Msg ' the dialog title
End If
bInfo.ulFlags = &H1 ' Type of directory to return
X = SHBrowseForFolder(bInfo) ' display the dialog
' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal X, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetFolderName = Left(path, pos - 1)
Else
GetFolderName = ""
End If
End Function

Here is some code. You need to refernce this project to the Microsft
Scripting Runtime library (in the VBE Click Tools - References -Microsoft
Scripting Runtime)

Sub ListFiles()
Dim fso As New FileSystemObject
Dim oCurrentFile As File
Dim oCurrentFolder As Folder
Dim wks As Worksheet
Dim rng As Range

Set wks = Sheets("Sheet1")
Set rng = wks.Range("A2")

Set oCurrentFolder = fso.GetFolder("C:\Windows")

For Each oCurrentFile In oCurrentFolder.Files
rng.Value = oCurrentFile.Name
rng.Offset(0, 1).Value = oCurrentFile.ShortName
rng.Offset(0, 2).Value = oCurrentFile.Path
rng.Offset(0, 3).Value = oCurrentFile.DateCreated
rng.Offset(0, 4).Value = oCurrentFile.DateLastAccessed
rng.Offset(0, 5).Value = oCurrentFile.DateLastModified
rng.Offset(0, 6).Value = oCurrentFile.Size
rng.Offset(0, 7).Value = oCurrentFile.Type
rng.Offset(0, 8).Value = oCurrentFile.Attributes
Set rng = rng.Offset(1, 0)
Next oCurrentFile
End Sub

--
HTH...

Jim Thomlinson


"PeterO" wrote:


Hi Guys,

I need some help, I am new to VB and I am looking to create an excel
sheet that I can point at a folder and and create a file listing from
it. For example:

File Name, Creation date, file type, file size.

I would like to have just one button that asks me to point a the folder
and it does the rest.

any advice would be really helpful

Peter


--
PeterO
------------------------------------------------------------------------
PeterO's Profile: http://www.excelforum.com/member.php...o&userid=24313
View this thread: http://www.excelforum.com/showthread...hreadid=379186

Hi Jim and Paul,
I have tried both the codes. Works fine but one thing. It is not
recursive.
If there are any sub folders and files in it, macro completly ignores
them.
Can you do something about it? to make it recursive? Or a choise to
list as it is or recursive may be given to user if that is possible.

Awaiting your reply,
Regards,


Jim Thomlinson wrote:
Here is some code. You need to refernce this project to the Microsft
Scripting Runtime library (in the VBE Click Tools - References -Microsoft
Scripting Runtime)

Sub ListFiles()
Dim fso As New FileSystemObject
Dim oCurrentFile As File
Dim oCurrentFolder As Folder
Dim wks As Worksheet
Dim rng As Range

Set wks = Sheets("Sheet1")
Set rng = wks.Range("A2")

Set oCurrentFolder = fso.GetFolder("C:\Windows")

For Each oCurrentFile In oCurrentFolder.Files
rng.Value = oCurrentFile.Name
rng.Offset(0, 1).Value = oCurrentFile.ShortName
rng.Offset(0, 2).Value = oCurrentFile.Path
rng.Offset(0, 3).Value = oCurrentFile.DateCreated
rng.Offset(0, 4).Value = oCurrentFile.DateLastAccessed
rng.Offset(0, 5).Value = oCurrentFile.DateLastModified
rng.Offset(0, 6).Value = oCurrentFile.Size
rng.Offset(0, 7).Value = oCurrentFile.Type
rng.Offset(0, 8).Value = oCurrentFile.Attributes
Set rng = rng.Offset(1, 0)
Next oCurrentFile
End Sub

--
HTH...

Jim Thomlinson


"PeterO" wrote:


Hi Guys,

I need some help, I am new to VB and I am looking to create an excel
sheet that I can point at a folder and and create a file listing from
it. For example:

File Name, Creation date, file type, file size.

I would like to have just one button that asks me to point a the folder
and it does the rest.

any advice would be really helpful

Peter


--
PeterO
------------------------------------------------------------------------
PeterO's Profile: http://www.excelforum.com/member.php...o&userid=24313
View this thread: http://www.excelforum.com/showthread...hreadid=379186

Hi Guys,

Many thanks for ythe code, works a treat, it would be great if we could
mine down through the sub folder structure. I can live without it, but
it would be great.

Many Thanks

Peter


--
PeterO
------------------------------------------------------------------------
PeterO's Profile: http://www.excelforum.com/member.php...o&userid=24313
View this thread: http://www.excelforum.com/showthread...hreadid=379186

try:

Sub StartListFiles()
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Application.ScreenUpdating = False
Worksheets("Sheet1").UsedRange.EntireRow.Delete
Call listfiles(Range("Sheet1!A1"), fso.GetFolder("C:\Windows"))
End Sub

Private Sub listfiles(oRng As Excel.Range, oFld As Scripting.Folder)
Dim oFile As Scripting.File
Dim oSubFld As Scripting.Folder

'Dump files in excel
For Each oFile In oFld.Files
If oRng.Row = 65536 Then
Set oRng = oRng.Offset(-65535, 12)
Else
Set oRng = oRng.Offset(1)
End If

With oFile
oRng.Resize(1, 9) = Array(.Name, .ShortName, .Path, _
.DateCreated, .DateLastAccessed, .DateLastModified, _
.Size, .Type, .Attributes)
End With

Next

'Recurse for subfolders
For Each oSubFld In oFld.SubFolders
Call listfiles(oRng, oSubFld)
Next

End Sub






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


PeterO wrote :


Hi Guys,

Many thanks for ythe code, works a treat, it would be great if we
could mine down through the sub folder structure. I can live without
it, but it would be great.

Many Thanks

Peter