I have a word document. Each page has two tables for well that have exactly the same structure on all 1000+ pages. I need to make a database out of those tables, where each page will become one record.
1. a) Save the document as a text file. The look at the document to see if there is a pattern there. This is a little different than what George suggested in that you would first change the document to text, then look for a pattern.
b) Then, using VBA, read the text document and knowing the order of things within where the tables were, you might be able to "read" each entry in each table to create the fields and records from the table data.
2. Try step b) above with the actual Word document. Using Access VBA, step through the table structure to create each record, data element by data element.
I have looked at the txt file and it looks like the structure is the same for all pages.
I have given the same question at experts exchange and was given the following code:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim tbl As Word.Table
Dim cl As Word.Cell
Dim f As Integer
Dim DB_path As String
DB_path = "D:/data/water_project/water_cadastre/wells/armavir/"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_path & "armavir_wells.mdb;Persist Security Info=False"
rs.Open "wells_armavir", cn, adOpenDynamic, adLockPessimistic, adCmdTable
For Each tbl In ActiveDocument.Tables
f = 0
For Each cl In tbl.Range.Cells
rs.Fields(f).Value = GetCellText(cl)
f = f + 1
Function GetCellText(cl As Word.Cell)
Dim l As Integer
l = Len(cl.Range.Text)
GetCellText = Left$(cl.Range.Text, l - 2)
The VBA doesn't work for some reason and would like you to give your opinion on it too.
I have attached two pages of the tables if it would help.