Populate a table based on files in a folder-VBForums
Results 1 to 15 of 15

Thread: Populate a table based on files in a folder

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Populate a table based on files in a folder

    Hello,
    I am trying to build an app that will populate a table based on the file names in a folder. Ex. ("16010 - ELECTRICAL GENERAL.doc" would be placed in a table |16010|ELECTRICAL GENERAL|.) I would like to be able to select a folder and have the list of documents fill in the table. I could change the naming convention as necessary. A search has turned up very little. Any help or ideas on how I could accomplish this would be greatly appreciated.
    Thanks in advance.

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    It is not a difficult task at all. Where do you want to make this application? VBA Excel/Access/Word or where?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    Quote Originally Posted by koolsid View Post
    It is not a difficult task at all. Where do you want to make this application? VBA Excel/Access/Word or where?
    Either a VBA Application that generates a Word Document or a Word Document would be ideal. I want to use this to ensure the table of contents matches the contents of the folder.

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    What do you mean by table of contents? And how are you planning to use this table of contents...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    Quote Originally Posted by koolsid View Post
    What do you mean by table of contents? And how are you planning to use this table of contents...
    We routinly put together specification books for our projects. So a project folder has a subfolder "specifications" that has several word documents in it titled "SECTION NUMBER - SECTION TITLE". I send a table of contents with a list of the specs in the folder in a table out to our clients. I want to automate the table to eliminate errors that have been happening when an engineer changes the specs in the folder without updating the table of contents document.

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    So lets say you have the following files in a folder

    16010 - ELECTRICAL GENERAL1.doc
    17010 - ELECTRICAL GENERAL2.doc
    18010 - ELECTRICAL GENERAL3.doc
    19010 - ELECTRICAL GENERAL4.doc

    so do you want to create a table?

    Code:
    SECTION NUMBER          SECTION TITLE
    16010                 ELECTRICAL GENERAL1
    17010                 ELECTRICAL GENERAL2
    18010                 ELECTRICAL GENERAL3
    19010                 ELECTRICAL GENERAL4
    Last edited by Siddharth Rout; Aug 24th, 2009 at 12:03 PM.
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    Yes. I want to create a two column table with section Numbers and section titles.
    Here is a screen shot from one of my current documents.

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    Ok give me half an hour as I am in the office and this may take some time I will post the code shortly...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  9. #9

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    Thanks.

  10. #10
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    Ok try this... I have commented the code so that you can understand it. I have tested it and it works...

    vb Code:
    1. Sub FilesToTable()
    2.     Dim Mypath As String, MyArray() As String
    3.     Dim Sfile As String, Filescount As Long
    4.     Dim ArrayTemp() As String
    5.    
    6.     '~~> Change this to your folderpath
    7.     Mypath = "C:\Temp\"
    8.        
    9.     '~~> loop Folder to get the file count
    10.     Sfile = Dir(Mypath)
    11.     Do While Sfile > ""
    12.         Filescount = Filescount + 1
    13.         Sfile = Dir()
    14.     Loop
    15.    
    16.     '~~> Resize the array
    17.     ReDim MyArray(Filescount - 1)
    18.    
    19.     '~~> loop Folder to get the file name
    20.     Sfile = Dir(Mypath)
    21.     i = 0
    22.     Do While Sfile > ""
    23.         '~~> Store filenames in array without the extention
    24.         MyArray(i) = Replace(Sfile, ".doc", "")
    25.         i = i + 1
    26.         Sfile = Dir()
    27.     Loop
    28.        
    29.     '~~> Create Table with relevant rows and columns
    30.     ActiveDocument.Tables.Add Range:=Selection.Range, _
    31.     NumRows:=(Filescount + 1), NumColumns:=2, _
    32.     DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
    33.     wdAutoFitFixed
    34.    
    35.     With Selection.Tables(1)
    36.         If .Style <> "Table Grid" Then
    37.             .Style = "Table Grid"
    38.         End If
    39.         .ApplyStyleHeadingRows = True
    40.         .ApplyStyleLastRow = True
    41.         .ApplyStyleFirstColumn = True
    42.         .ApplyStyleLastColumn = True
    43.     End With
    44.    
    45.     '~~> Input Headers for the table
    46.     Selection.TypeText Text:="SECTION NUMBER"
    47.     Selection.MoveRight Unit:=wdCharacter, Count:=1
    48.     Selection.TypeText Text:="SECTION TITLE"
    49.     Selection.MoveRight Unit:=wdCell
    50.    
    51.     '~~> Store the data in the table
    52.     For i = 0 To UBound(MyArray)
    53.         ArrayTemp = Split(MyArray(i), "-")
    54.         Selection.TypeText Text:=ArrayTemp(0)
    55.         Selection.MoveRight Unit:=wdCharacter, Count:=1
    56.         Selection.TypeText Text:=ArrayTemp(1)
    57.         Selection.MoveRight Unit:=wdCell
    58.     Next i
    59. End Sub

    Hope this helps...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  11. #11

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    The macro was working yesterday but today I am getting an error message "Run-time Error '9' Subscript Out of Range". And the debugger highlights this line of code.
    Code:
    '~~> Resize the array
    ReDim MyArray(Filescount - 1)
    I'm at a loss for what could have happened.

  12. #12

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    I was leaving out the \ after the path. It is working again.

    P.S. Is there a way that I could have this macro remove .docx file extensions as well? I have tried a few things but I can't seem to figure it out.
    Last edited by watk6412; Aug 26th, 2009 at 02:04 PM.

  13. #13
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    I have tried a few things but I can't seem to figure it out.
    What have you tried?

    Hint:
    MyArray(i) = Replace(Sfile, ".doc", "")
    The above removes the .doc extention...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  14. #14

    Thread Starter
    New Member
    Join Date
    Jul 2009
    Posts
    10

    Re: Populate a table based on files in a folder

    Your help is very much appreciated.

    I can change the .doc to .docx and that works fine. The problem is that .doc and .docx file are sometimes both used and need to be included. I just recorded a macro to remove the .doc and .DOC file extensions that are used.

    I also added my attempt at error handling, an input box for the folder location and some code to delete any empty rows.

    Here is the code as I have it now.
    Code:
    Sub FilesToTable()
        Dim Mypath As String, MyArray() As String
        Dim Sfile As String, Filescount As Long
        Dim ArrayTemp() As String
    
    On Error Resume Next
        
        '~~> Change this to your folderpath
        'Mypath = "P:\path\"
    
    Folder:
    
       ' Prompt the user for the folder to list.
       Mypath = InputBox(Prompt:="What folder do you want to list?" & vbCr & vbCr _
             & "For example: C:\My Documents", _
             Default:=Options.DefaultFilePath(wdDocumentsPath))
    
       If Mypath = "" Or Mypath = " " Then
          If MsgBox("Either you did not type a folder name correctly" _
                & vbCr & "or you clicked Cancel. Do you want to quit?" _
                & vbCr & vbCr & _
                "If you want to type a folder name, click No." & vbCr & _
                "If you want to quit, click Yes.", vbYesNo) = vbYes Then
             Exit Sub
          Else
             GoTo Folder
          End If
       End If
    
       ' Test if folder exists.
       If Dir(Mypath, vbDirectory) = "" Then
          MsgBox "The folder does not exist. Please try again."
          GoTo Folder
       End If
           
        '~~> loop Folder to get the file count
        Sfile = Dir(Mypath)
        Do While Sfile > ""
            Filescount = Filescount + 1
            Sfile = Dir()
        Loop
        
        '~~> Resize the array
        ReDim MyArray(Filescount - 1)
        
        '~~> loop Folder to get the file name
        Sfile = Dir(Mypath)
        i = 0
        Do While Sfile > ""
            '~~> Store filenames in array without the extention
            MyArray(i) = Replace(Sfile, ".docx", "")
            i = i + 1
            Sfile = Dir()
        Loop
            
        '~~> Create Table with relevant rows and columns
        ActiveDocument.Tables.Add Range:=Selection.Range, _
        NumRows:=(Filescount + 1), NumColumns:=2, _
        DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitContent
        
        With Selection.Tables(1)
            If .Style <> "Table Grid" Then
                .Style = "Table Grid"
            End If
            .ApplyStyleHeadingRows = True
            .ApplyStyleLastRow = True
            .ApplyStyleFirstColumn = True
            .ApplyStyleLastColumn = True
        End With
        
        '~~> Input Headers for the table
        Selection.TypeText Text:="SPECIFICATION"
        Selection.TypeText Text:=vbCrLf & "SECTION"
        Selection.MoveRight Unit:=wdCharacter, Count:=1
        Selection.TypeText Text:="SECTION"
        Selection.TypeText Text:=vbCrLf & "DESCRIPTION"
        Selection.MoveRight Unit:=wdCell
        
        
        '~~> Store the data in the table
        For i = 0 To UBound(MyArray)
            ArrayTemp = Split(MyArray(i), " - ")
            Selection.TypeText Text:=ArrayTemp(0)
            Selection.MoveRight Unit:=wdCharacter, Count:=1
            Selection.TypeText Text:=ArrayTemp(1)
            Selection.MoveRight Unit:=wdCell
        Next i
        
        '-- Remove .DOC and .doc
            Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        With Selection.Find
            .Text = ".DOC"
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
        With Selection.Find
            .Text = ".doc"
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
        
         '--Delete Empty Rows
        Dim oTable As Table, oRow As Range, oCell As Cell, Counter As Long, _
        NumRows As Long, TextInRow As Boolean
    
        ' Specify which table you want to work on.
            Set oTable = Selection.Tables(1)
            ' Set a range variable to the first row's range
            Set oRow = oTable.Rows(1).Range
            NumRows = oTable.Rows.Count
            Application.ScreenUpdating = True
    
        For Counter = 1 To NumRows
    
            StatusBar = "Row " & Counter
            TextInRow = False
    
            For Each oCell In oRow.Rows(1).Cells
                If Len(oCell.Range.Text) > 2 Then
                    'end of cell marker is actually 2 characters
                    TextInRow = True
                    Exit For
                End If
            Next oCell
    
            If TextInRow Then
                Set oRow = oRow.Next(wdRow)
            Else
                oRow.Rows(1).Delete
            End If
    
        Next Counter
    
        Application.ScreenUpdating = True
         
    End Sub
    The only problem I have know is that I get rows with other files in them that I don't want in the table. Is there a way to delete rows with an empty second cell?


    This is an example of the tables I am getting now.

  15. #15
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Populate a table based on files in a folder

    for Doc and Docx use the code below

    vb Code:
    1. '~~> Check for .Doc
    2. If Right(MyArray(i), 4) = ".doc" Then
    3.     MyArray(i) = Replace(Sfile, ".doc", "")
    4. '~~> Check for .Docx
    5. ElseIf Right(MyArray(i), 5) = ".docx" Then
    6.     MyArray(i) = Replace(Sfile, ".docx", "")
    7. End If

    I am in the office so the image that you have uploaded is blocked. I would be home in another 2 hrs. i will check it then...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.