omar23j
08-03-2018, 02:16 PM
Hi, I have a script that extract information from multiple word documents and put them in an excel table. I want to implement an 'error proof'' function to highlight in red any cell (in excel) that contain unexpected characters such as ',' , '%' , etc....
My code is shown below.
Thanks in advance for the help.
Sub extract()Dim doclist As String, sPath As String
Dim i As Integer
Set wordapp = CreateObject("Word.Application")
sPath = "C:\Users\ocherkaoui\Desktop\VBA-DOCS\"
doclist = Dir(sPath & "*.doc*")
i = 1
Do While doclist <> ""
Set worddoc = wordapp.documents.Open(sPath & doclist)
Set tbl = worddoc.tables(1)
Cells(i, 1) = Right(Left(tbl.Cell(1, 2).Range.Text, Len(tbl.Cell(1, 2).Range.Text) - 1), Len(tbl.Cell(1, 2).Range.Text) - 11)
Cells(i, 2) = Left(tbl.Cell(6, 2).Range.Text, Len(tbl.Cell(6, 2).Range.Text) - 1)
i = i + 1
worddoc.Close
Set worddoc = Nothing
Set tbl = Nothing
doclist = Dir()
Loop
End Sub
My code is shown below.
Thanks in advance for the help.
Sub extract()Dim doclist As String, sPath As String
Dim i As Integer
Set wordapp = CreateObject("Word.Application")
sPath = "C:\Users\ocherkaoui\Desktop\VBA-DOCS\"
doclist = Dir(sPath & "*.doc*")
i = 1
Do While doclist <> ""
Set worddoc = wordapp.documents.Open(sPath & doclist)
Set tbl = worddoc.tables(1)
Cells(i, 1) = Right(Left(tbl.Cell(1, 2).Range.Text, Len(tbl.Cell(1, 2).Range.Text) - 1), Len(tbl.Cell(1, 2).Range.Text) - 11)
Cells(i, 2) = Left(tbl.Cell(6, 2).Range.Text, Len(tbl.Cell(6, 2).Range.Text) - 1)
i = i + 1
worddoc.Close
Set worddoc = Nothing
Set tbl = Nothing
doclist = Dir()
Loop
End Sub