inserting table data from a Word document into an Access table
in [Document Management]
|
Prev: Word 2003 duplicates two headings in the menu bar
Next: need help..Simple, compound, complex, compound-complex examples
From: s on 29 May 2010 21:51 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.
From: Graham Mayor on 30 May 2010 01:16 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 Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "s" <s(a)mailinator.com> wrote in message news:004c3d6e-a5a2-46ad-8aa7-8290231cbe66(a)g39g2000pri.googlegroups.com... > 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. >
From: s on 31 May 2010 13:46 On May 30, 1:16 am, "Graham Mayor" <gma...(a)REMOVETHISmvps.org> wrote: > 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
From: Graham Mayor on 1 Jun 2010 00:42 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 Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "s" <s(a)mailinator.com> wrote in message news:e3a78a1c-0538-4964-a3e8-1894274e8507(a)y12g2000vbg.googlegroups.com... On May 30, 1:16 am, "Graham Mayor" <gma...(a)REMOVETHISmvps.org> wrote: > 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
From: s on 1 Jun 2010 20:46
Thanks, yes I replaced it with commands to send content to Access table and it worked fine. Thanks again. On Jun 1, 12:42 am, "Graham Mayor" <gma...(a)REMOVETHISmvps.org> wrote: > 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 sitewww.gmayor.com > Word MVP web sitehttp://word.mvps.org |