inserting table data from a Word document into an Access table

S

s

The Word 2003/2007 document(some files are in
"docx" or Word 2007 and some are in "doc" Word 2003 format)
has data as indicated below:


...some text on line 1 which is not needed..
...some text on line 2 which is not needed..
...some text on line 3 which is not needed..

Table 1
Row 1 has no columns
....some data which is not needed...
Row 2/Column 1 Row 2/Column 2
...data not needed.. ..data needed..
Row 3/Column 1 Row 3/Column 2
...not needed.. ..data needed..
Row 4/Column 1 Row 4/Column 2
...not needed.. ..data needed..
Row 5/Column 1 Row 5/Column 2
...data not needed.. ..data needed..
Row 6/Column 1 Row 6/Column 2
...not needed.. ..data needed..
Row 7/Column 1 Row 7/Column 2
...not needed.. .. data needed..


Table 2
Row 1 has no columns
....some data which is not needed...
Row 2/Column 1 Row 2/Column 2
...data not needed.. ..data needed..
Row 3/Column 1 Row 3/Column 2
...not needed.. ..data needed..
Row 4/Column 1 Row 4/Column 2
...not needed.. ..data needed..
Row 5/Column 1 Row 5/Column 2
...data not needed.. ..data needed..





Table 3
Row 1 has no column
....some data which is not needed...
Row 2 has no column
....some data which is not needed...
Row 3/Column 1 Row 3/Column 2 Row 3/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 4/Column 1 Row 4/Column 2 Row 4/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 5/Column 1 Row 5/Column 2 Row 5/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 6/Column 1 Row 6/Column 2 Row 6/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 7/Column 1 Row 7/Column 2 Row 7/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 8/Column 1 Row 8/Column 2 Row 8/Column 3
...not needed.. .. data not needed.. ..data needed..




Table 4
Row 1 has no column
....some data which is not needed...
Row 2 has no column
....some data which is not needed...
Row 3/Column 1 Row 3/Column 2 Row 3/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 4/Column 1 Row 4/Column 2 Row 4/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 5/Column 1 Row 5/Column 2 Row 5/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 6/Column 1 Row 6/Column 2 Row 6/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 7/Column 1 Row 7/Column 2 Row 7/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 8/Column 1 Row 8/Column 2 Row 8/Column 3

and more tables like Table 4

Table N
Row 1 has no column
....some data which is not needed...
Row 2 has no column
....some data which is not needed...
Row 3/Column 1 Row 3/Column 2 Row 3/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 4/Column 1 Row 4/Column 2 Row 4/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 5/Column 1 Row 5/Column 2 Row 5/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 6/Column 1 Row 6/Column 2 Row 6/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 7/Column 1 Row 7/Column 2 Row 7/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 8/Column 1 Row 8/Column 2 Row 8/Column 3



I have developed some VBA code(which is incomplete and inaccurate)
which can read text from table cells and insert into a Access table
and is
described below:


Sub PopulateTablewithCellContents()

myTable
ID AutoNumber
Row1 String
Row2 String
Row3 String
Row4 String
Row5 String
Row6 String
Row7 String
Row8 String
Row9 String
Row10 String
Row11 String
Row12 String
Row13 String
Row14 String
Row15 String
Row16 String
Row17 String
Row18 String
Row19 String
Row20 String
Row21 String
Row22 String
Row23 String
Row24 String
Row25 String
Row26 String
Row27 String
Row28 String
Row29 String
Row30 String
Row31 String
Row32 String
Row33 String
Row34 String
Row35 String
Row36 String
Row37 String
Row38 String
Row39 String
Row40 String
Row41 String
Row42 String
Row43 String
Row44 String
Row45 String
Row46 String
Row47 String
Row48 String
Row49 String
Row50 String
Row51 String
Row52 String
Row53 String
Row54 String
Row55 String
Row56 String
Row57 String
Row58 String
Row59 String
Row60 String


Dim intCells As Integer
Dim celTable As Cell
Dim strCells() As String
Dim intCount As Integer
Dim rngText As Range
Dim MyDB As DAO.Database
Dim strX As String
Dim strY As String
Dim strSQL as String
Dim firsttable As Boolean 'if data is being read from table 1
Dim secondtable As Boolean 'if data is being read from table 2
Dim thirdtable As Boolean 'if data is being read from table
3,4,....N

If ActiveDocument.Tables.Count >= 1 Then
With ActiveDocument.Tables(1).Range
intCells = .Cells.Count
ReDim strCells(intCells)
intCount = 1
For Each celTable In .Cells
Set rngText = celTable.Range
rngText.MoveEnd Unit:=wdCharacter, Count:=-1
strCells(intCount) = rngText
intCount = intCount + 1
Next celTable
strSQL = "INSERT INTO myTable VALUES (" & CStr(strCells(1)) & ", "
& CStr(strCells(2)) & " & ", "
& CStr(strCells(3)) & " & ", "
& CStr(strCells(4)) & " & ", "

' and so on
& CStr(strCells(N)) & ");"

MyDB.Execute strSQL, dbFailOnError

End With
End If
End Sub


The above code snippet is not complete.

Questions are

1. How can I ensure if it is reading Table 1 from a document it skips
row1
reads row 2 to 7, for table 2 also it needs to skip row1 and read only
four rows(row 2 to
row 5), for table 3 skip rows 1 and 2, read rows 2 to 7 and continue
till there are no more tables?
The idea is if a row does not have any columns it needs to be skipped
and if a row has columns, only the rightmost column needs to be read
and its data needs to be stored in a table.

2. After all required data is read from the tables, how can it be
concatenated and inserted
as a single row in the table "myTable". Also, a document may have
tables till 12
which would mean it would have a total(6 rows for table 1, 4 rows for
table 2 and 6 rows
for the other 10 tables) of 70 fields of data and the Access table
"myTable"
could have 100 or more fields. I mean how can i insert 70 values in a
table expecting 100 or more values. I realize it is possible, but not
sure how it
can be done using VBA.

3. There is another caveat. Some documents may have Table 3,
Table 4... Table N which will have only 5 rows whose rightmost colums
need to be read. For example, Table 3 might be such that row 1 data
needs to
be skipped, from row 3 to row 7 data in the third column
of the rows needs to be inserted in the Access table in such a way
that the
corresponding row of the Access table
is left blank. I will clarify it with an example

There is a document which has table 3 as below:


Table 3
Row 1 has no column
....some data which is not needed...
Row 2 has no column
....some data which is not needed...
Row 3/Column 1 Row 3/Column 2 Row 3/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 4/Column 1 Row 4/Column 2 Row 4/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 5/Column 1 Row 5/Column 2 Row 5/Column 3
...not needed.. ..data not needed.. ..data needed..
Row 6/Column 1 Row 6/Column 2 Row 6/Column 3
...data not needed.. ..data not needed.. ..data needed..
Row 7/Column 1 Row 7/Column 2 Row 7/Column 3
...not needed.. ..data not needed.. ..data needed..


needs to be in the Access table as

Data from table 3 Data inserted into Access
in Word document table

Row3Column3 Column11
Row4Column3 Column12
Row5Column3 Column13
Row6Column3 Column14
Column15 (this will be a blank so an
empty string will need to be inserted here)
Row7Column3 Column16


Sorry for the very long post, but i thought using examples
would make my question clear.

Any suggestions would be appreciated.
 
G

Graham Mayor

I don't know much about programming Access tables, but the extraction of the
data from the last column of the tables (no matter how many and how
organised) is simple enough. The following will do that - and here displays
them table by table in a message box. Replace that message box with the
commands to send the content to your Access cell.


Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim sText As String
For Each oTable In ActiveDocument.Tables
sText = ""
For Each oRow In oTable.Rows
If oRow.Cells.Count > 1 Then
Set oRng = oRow.Cells(oRow.Cells.Count).Range
oRng.End = oRng.End - 1
sText = sText & oRng.Text & Chr(44)
End If
Next oRow
sText = Left(sText, Len(sText) - 1)
MsgBox sText
'Insert sText into your table as required
Next oTable

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
S

s

I don't know much about programming Access tables, but the extraction of the
data from the last column of the tables (no matter how many and how
organised) is simple enough. The following will do that - and here displays
them table by table in a message box. Replace that message box with the
commands to send the content to your Access cell.

Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim sText As String
For Each oTable In ActiveDocument.Tables
    sText = ""
    For Each oRow In oTable.Rows
        If oRow.Cells.Count > 1 Then
            Set oRng = oRow.Cells(oRow.Cells.Count).Range
            oRng.End = oRng.End - 1
            sText = sText & oRng.Text & Chr(44)
        End If
    Next oRow
    sText = Left(sText, Len(sText) - 1)
    MsgBox sText
    'Insert sText into your table as required
Next oTable
Thanks, that is very helpful. But, in the Message Box I cannot see all
the contents
of all the columns of all tables in the document. I gather the CString
variable
should be able to store around 2^31 characters as it is a variable
string so it should
have enough capacity

Or, am I missing something else?

Thanks
 
G

Graham Mayor

What you are missing is that the message box is only to illustrate that the
macro works. As suggested earlier *replace* the message box with the
commands to send the content to your Access table. It's the Access
programming I am unfamiliar with.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


I don't know much about programming Access tables, but the extraction of
the
data from the last column of the tables (no matter how many and how
organised) is simple enough. The following will do that - and here
displays
them table by table in a message box. Replace that message box with the
commands to send the content to your Access cell.

Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim sText As String
For Each oTable In ActiveDocument.Tables
sText = ""
For Each oRow In oTable.Rows
If oRow.Cells.Count > 1 Then
Set oRng = oRow.Cells(oRow.Cells.Count).Range
oRng.End = oRng.End - 1
sText = sText & oRng.Text & Chr(44)
End If
Next oRow
sText = Left(sText, Len(sText) - 1)
MsgBox sText
'Insert sText into your table as required
Next oTable
Thanks, that is very helpful. But, in the Message Box I cannot see all
the contents
of all the columns of all tables in the document. I gather the CString
variable
should be able to store around 2^31 characters as it is a variable
string so it should
have enough capacity

Or, am I missing something else?

Thanks
 
S

s

Thanks, yes I replaced it with commands to send content to Access
table and it worked fine.

Thanks again.
 
G

Graham Mayor

Excellent!. Can you post the code you used to send it to your Access table?
You could send it to the link on the home page of my web site. I am
interested to learn what you did.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Thanks, yes I replaced it with commands to send content to Access
table and it worked fine.

Thanks again.
 
S

s

Excellent!. Can you post the code you used to send it to your Access table?
You could send it to the link on the home page of my web site. I am
interested to learn what you did.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web sitewww.gmayor.com
Word MVP web sitehttp://word.mvps.org

The code is below. Whatever I added to send data
to Access has a comment New Addition.

Option Explicit

Sub ReturnTableText()

Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim sText As String
Dim count As Integer
Dim dbMyDB As DAO.Database
Dim myRecordSet As DAO.Recordset


Set dbMyDB = DBEngine.Workspaces(0).OpenDatabase("C:
\mydatabase.accdb") ' New Addition
Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset) ' New
Addition

sText = ""
count = 1

myRecordSet.AddNew ' New Addition
For Each oTable In ActiveDocument.Tables

For Each oRow In oTable.Rows

If oRow.Cells.count > 1 Then
Set oRng = oRow.Cells(oRow.Cells.count).Range
oRng.End = oRng.End - 1


myRecordSet.Fields(count).Value = oRng.Text ' New Addition


count = count + 1

End If

Next oRow

Next oTable
myRecordSet.Update ' New Addition


myRecordSet.Close ' New Addition
dbMyDB.Close ' New Addition

End Sub
 
G

Graham Mayor

Thanks :) I'll have a play around with that tomorrow.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Excellent!. Can you post the code you used to send it to your Access
table?
You could send it to the link on the home page of my web site. I am
interested to learn what you did.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web sitewww.gmayor.com
Word MVP web sitehttp://word.mvps.org

The code is below. Whatever I added to send data
to Access has a comment New Addition.

Option Explicit

Sub ReturnTableText()

Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim sText As String
Dim count As Integer
Dim dbMyDB As DAO.Database
Dim myRecordSet As DAO.Recordset


Set dbMyDB = DBEngine.Workspaces(0).OpenDatabase("C:
\mydatabase.accdb") ' New Addition
Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset) ' New
Addition

sText = ""
count = 1

myRecordSet.AddNew ' New Addition
For Each oTable In ActiveDocument.Tables

For Each oRow In oTable.Rows

If oRow.Cells.count > 1 Then
Set oRng = oRow.Cells(oRow.Cells.count).Range
oRng.End = oRng.End - 1


myRecordSet.Fields(count).Value = oRng.Text ' New Addition


count = count + 1

End If

Next oRow

Next oTable
myRecordSet.Update ' New Addition


myRecordSet.Close ' New Addition
dbMyDB.Close ' New Addition

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top