A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

inserting table data from a Word document into an Access table



 
 
Thread Tools Display Modes
  #1  
Old May 30th, 2010, 02:51 AM posted to comp.databases.ms-access,microsoft.public.access
s
external usenet poster
 
Posts: 52
Default inserting table data from a Word document into an Access table

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.

  #2  
Old May 30th, 2010, 01:22 PM posted to comp.databases.ms-access,microsoft.public.access
Rudi,R.F.
external usenet poster
 
Posts: 1
Default inserting table data from a Word document into an Access table


"s" schrieb im Newsbeitrag
...
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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.