-
Aug 21st, 2009, 05:42 PM
#1
Thread Starter
New Member
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.
-
Aug 22nd, 2009, 02:44 AM
#2
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?
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 24th, 2009, 11:32 AM
#3
Thread Starter
New Member
Re: Populate a table based on files in a folder
Originally Posted by koolsid
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.
-
Aug 24th, 2009, 11:41 AM
#4
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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 24th, 2009, 11:49 AM
#5
Thread Starter
New Member
Re: Populate a table based on files in a folder
Originally Posted by koolsid
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.
-
Aug 24th, 2009, 11:56 AM
#6
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.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 24th, 2009, 12:03 PM
#7
Thread Starter
New Member
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.
-
Aug 24th, 2009, 12:05 PM
#8
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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 24th, 2009, 12:09 PM
#9
Thread Starter
New Member
Re: Populate a table based on files in a folder
Thanks.
-
Aug 24th, 2009, 12:45 PM
#10
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:
Sub FilesToTable() Dim Mypath As String, MyArray() As String Dim Sfile As String, Filescount As Long Dim ArrayTemp() As String '~~> Change this to your folderpath Mypath = "C:\Temp\" '~~> 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, ".doc", "") 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:= _ wdAutoFitFixed 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:="SECTION NUMBER" Selection.MoveRight Unit:=wdCharacter, Count:=1 Selection.TypeText Text:="SECTION TITLE" 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 End Sub
Hope this helps...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 26th, 2009, 12:50 PM
#11
Thread Starter
New Member
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.
-
Aug 26th, 2009, 01:57 PM
#12
Thread Starter
New Member
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.
-
Aug 26th, 2009, 04:35 PM
#13
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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Aug 27th, 2009, 12:36 PM
#14
Thread Starter
New Member
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.
-
Aug 27th, 2009, 12:42 PM
#15
Re: Populate a table based on files in a folder
for Doc and Docx use the code below
vb Code:
'~~> Check for .Doc If Right(MyArray(i), 4) = ".doc" Then MyArray(i) = Replace(Sfile, ".doc", "") '~~> Check for .Docx ElseIf Right(MyArray(i), 5) = ".docx" Then MyArray(i) = Replace(Sfile, ".docx", "") 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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|