Please edit this macro. Thanks

pedie

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi,

I'm wanting excel to find list of folders, name and size...
it does work, i dont know..it is giving error message.
code source
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1042

also please check this link

http://www.automateexcel.com/2008/09/13/vba-list-of-all-files-contained-within-a-directory/
:eeek:
Option Explicit

Sub GetFileNames()

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$ = "G:\" '<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub

Thanks everyone for helping.

Pedie.:cool:
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
I like to use the Scripting FileSystemObject, which is easy to learn and very handy - well worth a few hours spent getting familiar with it. It's not part of the normal VBA library - so you'll need to set a reference to it for your code to work (this is easy to do - see the comments in the first lines of the code below).

Here's some code from a site that will probably do your job for you - you only need to change the filename you give it (I highlighted this part in blue). It's got an option to include subfolders too. If I screwed up the code in my edits the original site is here (contributed to Excel tips by Erlandsen Consulting):
Excel Tips - files in folder

I thought I saw something in your post about who created the file. This I don't know about.

Code: 
'-----------------------------------------------------------
'You must reference the Microsoft Scripting Runtime library
'    From within the VBE:
'    1) Goto Tools | References
'    2) Check box for Microsoft Scripting Runtime.
'-----------------------------------------------------------

[B]Sub TestListFilesInFolder()[/B]
    Workbooks.Add
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File Name:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "Date Last Accessed:"
    Range("F3").Formula = "Date Last Modified:"
    Range("G3").Formula = "Attributes:"
    Range("H3").Formula = "Short File Name:"
    Range("A3:H3").Font.Bold = True
    
ListFilesInFolder "[B][COLOR="Blue"]C:\FolderName\[/COLOR][/B]", False

[B]End Sub[/B]

'----------------------------------------------------------------------
[B]Sub ListFilesInFolder(SourceFolderName As String, _
            IncludeSubfolders As Boolean)[/B]

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Cells(Rows.Count,1).End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = FileItem.Path & FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
        r = r + 1 ' next row number
    Next FileItem

    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If

    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True

[B]End Sub[/B]
 
Upvote 0
Thanks Alot..i have been for this for long time....

Thanks you soo much. I may I want to get back to you if I need further help with this....please dont mind...Thanks again.


Pedie;)
 
Upvote 0
May I please ask...will this work in EXCEL 2007. Because I use xls 2007.

I am not getting this option in excel 2007
[1.Goto Tools | References
2. Check box for Microsoft Scripting Runtime]

When i try to run this it is giving me this error;

Compile error:
User-defined type not defined.


Please look in to it again.

Thanks.
Pedie
 
Upvote 0
The error is because you haven't ticked the box as required.
I'm using Excel 2007 and it's definitely there to be ticked.
Are you looking down the list in the Microsoft area, about halfway.
 
Upvote 0
Cross-Posted:

http://vbaexpress.com/forum/showthread.php?t=33248

<!-- / message --><!-- sig -->@Pedie:

Please see Here

Please read Mr. Puls' article in the above link. I cannot imagine a kindlier way of advising reference cross-posting; kudos to Ken.

I believe you mention working in 2007. .FileSearch was ditched in 2007. I would suggest you keep after xenou's suggestion, albeit, I would most likely go late-bound.

Mark
 
Upvote 0
GTO said:
Cross-Posted
Mark
Click to expand...

Is all this website run by same people....i was thinking i cound get help faster if i post it in you know different site but nwo i got it:biggrin:


Thanks GTO, thanks everyone..

Yes in excel 2007 I didnt see the tolls and what to tick..i think it is not there in xls 2007.
 
Upvote 0
Michael M said:
The error is because you haven't ticked the box as required.
I'm using Excel 2007 and it's definitely there to be ticked.
Are you looking down the list in the Microsoft area, about halfway.
Click to expand...

Michael, could you please navigate me with the exact path as where to go and tick???

Thanks alot, I need this.;)
 
Upvote 0
I got it....please ignore me for my ignoarance..it was right there in the top....
 
Upvote 0
Thanks everyone for looking into this....

Please excuse me for my ignorance and my poor english...!

I hope i'll do beetter each passing day!
 
Upvote 0
You must log in or register to reply here.

Similar threads

mmn1000
  • Question Question
List folders and files
Replies
1
Views
893
Fuji
F
C
  • Solved
VBA get sheetnames from all excel files in a folder
Replies
5
Views
2K
ijourneaux
I
S
  • Question Question
Adding a new field to the results of my "list the folders inside a specific folder" code
Replies
2
Views
408
John_w
J
R
  • Question Question
Add a code or number at the end of the file name using VBA (Looped)
Replies
3
Views
211
HighAndWilder
H
T
  • Solved
Get size of all files in a directory?
Replies
3
Views
635
JEC
JEC

Forum statistics

Threads
1,225,475
Messages
6,185,194
Members
453,282
Latest member
roger_nz66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top