DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 1 of 1

Thread: need help with code.

  1. #1
    Join Date
    Mar 2008

    need help with code.

    Having said that not sure this is the right forum anyhow here goes.

    I have a excel document and i need to make a register of a range document all within one main folder but also sub folders.

    I found some code and i am sure it should work but every time I even look at it, there is some error happening.

    I want to click on a button to generate update of the folder and subfolders in to a document register.

    I have from A to I in columns however not all of them are going to be prepopulated.

    I have attached snapshot of the document and the headings.
    and below is the code. I need to have the document name and a hyperlink of the document but when i did it with a less code the document name was all of the folder names down to the document.

    can any one help out at all?
    Sub TestListFilesInFolder()
        Workbooks.Add ' create a new workbook for the file list
        ' add headers
        With Range("A1")
            .Formula = "Folder contents:"
            .Font.Bold = True
            .Font.Size = 12
        End With
        Range("B3").Formula = "Doc Name:"
        Range("C3").Formula = "File Type:"
        Range("D3").Formula = "Date:"
        Range("I3").Formula = "File Name:"
        Range("A3:I3").Font.Bold = True
        ListFilesInFolder "G:\CFS\Administration\Contracts\Project 2020", True
        ' list all files included subfolders
    End Sub
    Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    ' lists information about the files in SourceFolder
    ' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
        Set FSO = New Scripting.FileSystemObject
        Set SourceFolder = FSO.GetFolder(SourceFolderName)
        r = Range("A65536").End(xlUp).Row + 1
        For Each FileItem In SourceFolder.Files
            ' display file properties
            Cells(r, 2).Formula = FileItem.ShortPath & FileItem.Name
            Cells(r, 3).Formula = FileItem.Name
            Cells(r, 4).Formula = FileItem.Type
            Cells(r, 10).Formula = FileItem.Path & FileItem.Name
            ' use file methods (not proper in this example)
    '        FileItem.Copy "G:\CFS\Administration\Contracts\Project 2020.txt", True
    '        FileItem.Move "G:\CFS\Administration\Contracts\Project 2020.txt"
    '        FileItem.Delete True
            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
        Set FileItem = Nothing
        Set SourceFolder = Nothing
        Set FSO = Nothing
        ActiveWorkbook.Saved = True
    End Sub
    Attached Files Attached Files

Similar Threads

  1. Jupitermedia Legal Notice -- NO PERMISSION TO USE CODE SAMPLES?!
    By Noryk Rekrap in forum Talk to the Editors
    Replies: 3
    Last Post: 08-23-2007, 03:56 PM
  2. wot is CLR
    By lostguy in forum .NET
    Replies: 8
    Last Post: 04-03-2002, 07:41 PM
  3. .NET equals Efficiency
    By Kevin in forum .NET
    Replies: 150
    Last Post: 03-04-2002, 06:40 PM
  4. Another Language
    By Steven Bell in forum .NET
    Replies: 260
    Last Post: 06-01-2001, 04:32 PM
  5. error code in JSP(please chek the code)
    By satish in forum Java
    Replies: 1
    Last Post: 09-22-2000, 09:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center