alwayshouston
MIS
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
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