I have a rather large Word questionnaire. It has several similar irregular (meaning some rows have merged cells and some do not) tables in it. I need to read each table for 3 specific values and take the response in the next cell and move it to a new excel spreadsheet so I can import that data into Access. It has been a literal country minute since I did this sort of thing in vbscript. I gathered this script from another forum, but I can't remember how to do what I need to do.
That basically transforms all of my tables into the excel workbook. Not what I need.
What I need to do is read each Word table's first column, and specifically the first cell data of each row, and if the cell says "Objective number", I need it to copy the next cell over's data into the Excel spreadsheet's column A, then the Word doc's data from 2 cells down (technologies) from that first copied text into Excel's column B, and the Word doc's next cell down (questions) into Excel Column C. Then it needs to continue to read the Word doc's first column until it finds the next "Objective number" text, and repeat until it's at the end of the Word doc.
Thanks a million for all the help
The Rev.
Code:
Set objWord = CreateObject("Word.Application")
objWord.Visible = False
objWord.DisplayAlerts = False
objWord.Documents.Open "C:\Users\TheRev\Desktop\AllObj.docx", False, True
Set objDoc = objWord.ActiveDocument
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveSheet
dstRow = 0
For Each tbl In objDoc.Tables
For srcRow = 1 To tbl.Rows.Count
col = 0
For Each cell In tbl.Rows(srcRow).Cells
If cell = "Family number" then
wscript.echo "Found it"
Else
col = col + 1
objSheet.Cells(dstRow+srcRow, col).Value = Left(cell.Range.Text, Len(cell.Range.Text)-1)
End If
Next
Next
dstRow = dstRow + SrcRow
Next
objExcel.ActiveWorkbook.SaveAs "C:\Users\TheRev\Desktop\Test.xlsx"
objExcel.Quit
That basically transforms all of my tables into the excel workbook. Not what I need.
What I need to do is read each Word table's first column, and specifically the first cell data of each row, and if the cell says "Objective number", I need it to copy the next cell over's data into the Excel spreadsheet's column A, then the Word doc's data from 2 cells down (technologies) from that first copied text into Excel's column B, and the Word doc's next cell down (questions) into Excel Column C. Then it needs to continue to read the Word doc's first column until it finds the next "Objective number" text, and repeat until it's at the end of the Word doc.
Thanks a million for all the help
The Rev.