PDA

View Full Version : Import text from word document into excel



postino
01-08-2013, 03:40 AM
Hi,

I am struggling with modifying macro code (attached) to help me extract text from word documents into an excel sheet


Basically: I have hundreds of word documents that contain the same data for different suppliers. I need to modify my macro that would access the word documents one at a time, search the word document for certain words in a 2 column table (e.g. "company" or "policy number") then extract what lies next to that name back into a field in excel.

Example

Public Liability
Company: XYZ trust pty ltd
Policy number: 12345

Professional Indemnity
Company: zxy group pty ltd
policy number: 54321

I really am lost.. and was hoping to get some quick pointers!!

any help much appreciated..

jignesh142
01-09-2013, 03:27 AM
Hi

I checked your code and it seems that your For loop which is parsing the data is not running under the normal modes of the operation. If you set it to false then it executes and transfers some data to excel which means that your earlier assumption is wrong in some case(i have not gone that level, it is difficult to understand which type of the data you want to extract without word file)

The other errors are at the object assignment at the pre processing cleanup process

postino
01-09-2013, 05:10 AM
Hi Jigenesh,

Thanks for the reply. I've attached a sample word document, and highlighted the sections I needed to transfer to the sample excel file.

The purpose of this is so that I can batch extract from the existing files, therefore I can't make changes to the word document.

Thanks once again.

postino
01-09-2013, 05:11 AM
Excel file

snb
01-09-2013, 05:54 AM
Sub M_snb()
c00 = "G:\OF\"
c01 = Dir(c00 & "*.doc")

Do Until c01 = ""
Dim sn(0, 23)
jj = 14

With GetObject(c00 & c01)
For j = 0 To UBound(sn, 2)
sn(0, j) = Trim(Left(.Tables(1).Cell(jj, 2), Len(.Tables(1).Cell(jj, 2).Range) - 2))
jj = jj + 1 + IIf(j = 10, 3, 0) + IIf(j = 11, 3, 0) + IIf(j = 12, 22, 0) + IIf(j = 16, 1, 0) + IIf(j = 19, 1, 0)
Next
.Close False
End With
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(sn, 2) + 1) = sn
c01 = Dir
Loop
End Sub


Replace "G:\OF\" by the directory you want to search for the Word documents.