Please edit this macro. Thanks

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/
:eek:
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:
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
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]
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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;)
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,090
Office Version
2013
Platform
Windows

ADVERTISEMENT

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.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

pedie

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

ADVERTISEMENT

Cross-Posted
Mark
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.
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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.
Michael, could you please navigate me with the exact path as where to go and tick???

Thanks alot, I need this.;)
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
I got it....please ignore me for my ignoarance..it was right there in the top....
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,133
Messages
5,509,353
Members
408,729
Latest member
Rajesh M

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top