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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
inserting table data from a Word document into an Access table
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" wrote in message ... 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. |
#3
|
|||
|
|||
inserting table data from a Word document into an Access table
On May 30, 1:16*am, "Graham Mayor" 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 |
#4
|
|||
|
|||
inserting table data from a Word document into an Access table
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" wrote in message ... On May 30, 1:16 am, "Graham Mayor" 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 |
#5
|
|||
|
|||
inserting table data from a Word document into an Access table
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" 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 |
#6
|
|||
|
|||
inserting table data from a Word document into an Access table
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 Word MVP web site http://word.mvps.org "s" wrote in message ... 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" 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 |
#7
|
|||
|
|||
inserting table data from a Word document into an Access table
On Jun 2, 1:28*am, "Graham Mayor" wrote:
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 Graham Mayor - Word MVP My web sitewww.gmayor.com Word MVP web sitehttp://word.mvps.org |
#8
|
|||
|
|||
inserting table data from a Word document into an Access table
Thanks I'll have a play around with that tomorrow.
-- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org "s" wrote in message ... On Jun 2, 1:28 am, "Graham Mayor" wrote: 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 Graham Mayor - Word MVP My web sitewww.gmayor.com Word MVP web sitehttp://word.mvps.org |
Thread Tools | |
Display Modes | |
|
|