Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBScript to copy cell data from MSWord table to Excel based on value of cell

Status
Not open for further replies.

The Rev

Systems Engineer
Apr 9, 2020
1
US
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.

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.
 
 https://files.engineering.com/getfile.aspx?folder=c460f9a3-9926-428a-9488-d5140af8f5c7&file=Upload1.docx
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top