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 Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copying text from Word to Excel with VBA

Status
Not open for further replies.
May 29, 2003
73
US
Hi All
I had been trying to copy text from MS Word file into an Excel spreadsheet. I want to loop through each page of MS Word file and find a word "STATUS". I want to copy the text after the STATUS to the end of the page to excel cell. How can I do this via VBA? I started off with the code below, but it does not work and gives me an error on Selection. Please HELP!!! My word document structure is as follow
Page1:
Copyright
Heading 3
Heading 4

STATUS
10002 102.5
10003 102.8
10004 104.9

Page2:
Copyright
Heading 3
Heading 4

STATUS
10005 105.3
10007 107.5

The output in Excel should look like:
10002 102.5
10003 102.8
10004 104.9
10005 105.3
10007 107.5

Thanks in Advance!

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim myPageNum
Dim numpages
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
Workbooks.Add ' create a new workbook
With Range("A1")
.Formula = "Word Document Contents:"
.Font.Bold = True
.Font.Size = 14
.Offset(1, 0).Select
End With
r = 3 ' startrow for the copied text from the Word document
Set wrdApp = CreateObject("Word.Application")
'wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("C:\Test\Test.doc")
' example word operations
With wrdDoc
Do Until .Bookmarks("\Sel") =.Bookmarks("\EndOfDoc")
wrdDoc.Selection.Find.ClearFormatting
With wrdDoc.Selection.Find
.Text = "Status"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
.Find.Execute

If .Find.Found = True Then
'Insert the selection into EXCEL

Else
Exit Do
End If
Loop
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top