want to fill table rows with data from a text file

[Paul]

Hi just wondering if a script could read a text file that has the list of a
bunch of files and file size and read it into a table in a word document?
Anyone know what the script would look like?
Thanks.

 

[Helmut Weber]

Hi Paul,
sure!
Things you could use are:
open pathname for mode .... as filenumber
while not eof(filenumber)
string = input...
' parse input
' check whether there is a table
' if not so create a table
' put result of parsing in table
activedocument.tables(t).cell(row, column).range.text = ...
or create new row etc...

hmm...

I think, you need to break down all into smaller pieces.
Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Paul]

Hi Helmut, I have programmed in visual basic.net but have not done any VBA.
Just wondering if you could provide a link of a simple example that I could
set up for word? Thanks.

 

[Helmut Weber]

Hi Paul,

how does the file containing the docs' names look?
Like this, created using the command or cmd shell?
dir c:\test\*.doc > c:\test\dir.doc

DOK1 DOC 19.968 18.06.04 13:21 Dok1.doc
TEST-L~1 DOC 63.488 04.02.05 14:27 test-lang.doc
TEST-100 DOC 73.728 25.10.04 20:48 test-100.doc
TTTDOC DOC 386.560 18.06.04 13:22 tttdoc.doc
AUTOOPEN DOC 19.968 06.07.04 20:59 Autoopen.doc
JOB DOC 19.968 20.06.04 16:22 JOB.doc
SYMBOL~1 DOC 92.672 04.06.04 11:07 Symbol-01.doc

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Paul]

Hi Helmut, I used this to create a listing,
dir >dir.doc
looks like
*********************************
Volume in drive C has no label.
Volume Serial Number is A343-432D

Directory of C:\Inetpub\wwwroot\newdm1

02/08/2005 02:05p <DIR> .
02/08/2005 02:05p <DIR> ..
08/06/2004 10:52a 3,045,972 040723-F-6740T-220.jpeg
05/04/2004 11:11a 4,591 Arcata_logosidebar.jpeg
04/30/2004 01:00p 1,044 AssemblyInfo.vb
with a bunch more files. It has some directories in it as well.
*************************************************

 

[Paul]

Hi Helmut, I wrote a script that reads in each line, performs a split and
checks some characters but gets the file name into one array and the file
size into another array. Now I just need to create the code that creates the
table in word and populates the cells.
thanks again for the information.

 

[Jay Freedman]

Hi Paul,

As you start on the next step, consider this: Instead of building the
table row by row, it will probably be much easier (not to mention
faster) to insert all the information into the document as plain text,
insert tab characters between the parts of each record, and then use
the .ConvertTextToTable method of the Selection or a Range object to
make the whole thing into a table.

The hardest part of that job may be figuring out where to put the
tabs. If you already have each record parsed into arrays, though, you
should be able to use the Join function and pass vbTab as the optional
delimiter parameter.

 

[Helmut Weber]

Hi Paul,
not difficult at all, but lots of tedious coding.

Do you want to put the data in an existing table?
Would that table be large enough?
Or do you want to create a new table and where?

Would that be adjacent to an existing table?
What table style etc...

A simple example for inserting the data from the arrays
at the end of the document and transforming the inserted
data into a table, as Jay suggested.

I didn't bother about coding style or speed,
this is only a code snippet for showing the principle:


selection.wholestory
Selection.Collapse direction:=wdCollapseEnd
Selection.TypeText Text:=vbCr
r.Start = Selection.Start ' a temorary range
' entries in the array = 18, entry(0) is ignored
For l = 1 To 18
Selection.TypeText Text:=col1(l) & vbTab ' array for column 1
Selection.TypeText Text:=col2(l) & vbCr ' array for column 1
Next
r.End = Selection.Start ' remember where inserting started
r.ConvertToTable _
Separator:=wdSeparateByTabs, _
NumColumns:=2, _
NumRows:=18, _
AutoFitBehavior:=wdAutoFitFixed

....

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Paul]

Hi Jay, thanks for the information. Think I will insert the text and then
convert it to a table as suggested. Just wondering what code to use to
insert what is in a string array into the document? Paul.

 

[Paul]

Hi Helmut, thanks for the response. I created a sub that creates the table
and fills it as shown below. It works but is kind of slow. I pass in the
array size and the two arrays.
Sub Macro2(j As Integer, filename, filesize)

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=j,
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
Selection.TypeText Text:="File Name"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="File Size"
Selection.MoveRight Unit:=wdCell
Dim i As Integer
For i = 0 To j
Selection.TypeText Text:=filename(i)
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=filesize(i)
Selection.MoveRight Unit:=wdCell
Next
End Sub

 

[Jay Freedman]

Hi Paul,

Here's an extremely simplified example. I looked at your latest reply
to Helmut to see how your data was stored.

Sub DirectoryTable()
Dim filename As Variant, filesize As Variant
Dim idx As Integer
Dim oDoc As Document

' dummy data
filename = Array("foo.bat", "bar.exe")
filesize = Array("256", "327,651")

' create a new doc
Set oDoc = Documents.Add

' put the data strings into the doc
For idx = 0 To UBound(filename)
oDoc.Range.InsertAfter _
filename(idx) & vbTab & filesize(idx) & vbCr
Next idx

' turn it into a table
oDoc.Range.ConvertToTable Separator:=vbTab

On Error Resume Next
oDoc.Save

Set oDoc = Nothing
End Sub

If you also want the macro to format the table, it would first be
helpful to make a Table object to refer to it. To do that, add

Dim oTbl As Table

to the declarations at the top. Then change the ConvertToTable
statement like this:

' turn it into a table
Set oTbl = oDoc.Range.ConvertToTable(Separator:=vbTab)

Then you can format it -- for example:

For Each oCell In oTbl.Columns(2).Cells
oCell.Range.ParagraphFormat.TabStops.Add _
Position:=InchesToPoints(1.25), _
Alignment:=wdAlignTabDecimal, _
Leader:=wdTabLeaderSpaces
Next oCell

 

[Helmut Weber]

Hi Paul,

though it would be quite interesting
to find out absolutely fastest way of
filling a table, the following
seems to be fast enough,
that is less than 1 second per column
for a 100 rows table, on a pretty
fast machine, though.


Sub FillTable()
Dim r As Long ' row
Dim s1 As String
Dim s2 As String
Dim t As Double
s1 = "test 04"
s2 = "xxxxxxxxxx"
t = Timer
With ActiveDocument.Tables(1)
For r = 1 To 100
' first cell in the row
.Rows(r).Cells(1).Range.Text = s1
' second cell in the row
.Rows(r).Cells(2).Range.Text = s2
Next
End With
MsgBox Timer - t
End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Paul]

Hi Helmut, 100 rows seems pretty quick, will most likely impliment the
quicker proceedure. I modified the script a bit, fills 3 columns, the file
name, timestamp, and the size, seems to take about 1 second per page (about
25 rows).
Thanks for the information.