Executing code spcifice to M$ Word from Excel

[liddlem1]

Hi Folks
I Know this is an EXCEL forum and I am going to ramble on about WORD, but I really want Excel to do this job for me.

I have a number of sub-contractors who script text for me in *.TXT, *.RTF and *.DBF files.

I pay them for each word that they type.
Presently, the sub-contractor has to check each document (TXT, RTf or DBF) manually to add up the words.

I would like to have this 'invoice' in a spreadsheet. (As there are a number of other macros and formulae already attached to it.)

My problem areas are as follows;
1. I cannot open rtf files via excel because all the formatting garbage opens with it like this.

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl
{\f0\fnil Times New Roman;}{\f1\fnil\fcharset0 Arial;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\cf1\b\f0\fs24
\par \lang7177\f1 You will now have the opportunity to answer questions about the works that you have just learned.
\par
\par You may do this test as many times as you like.
\par
\par Please note that if you skip out of any question, you will not be marked for the entire test.\lang1033
\par }

2. If I open the rtf in word and then temporarily copy / paste into excel for the purpose of counting the words, the VB code that works in M$ Word (e.g. MyCount=Selection.Words.Count) does not work in excel.

3. Just to confuse the issue more, I discovered that you get different values from Word if you use "Tools/Count Words" from the menu and if you use
MyCounter = Selection.Words.Count in a macro.
(The macro option counts commas, hyphens, colons etc as indiviual words)

Anyhow, I have discovered that a reasonably accurate solution is to count the number of spaces between words.


The following code gets the RTF filename which is stored in my spreadsheet. (Is there a way to point Excel to a folder and just open the files in order ?) It then opens the RTF file in Word.

The problem is that I cannot get Excel to carry out code that is Word specific. (See my comments identifiable with asterisks.)


Here's my code for what its worth

Private Sub Button_Click()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range

Set wdApp = Word.Application
'Set wdApp = Application.Open.wdApp(MyFileName)
ChangeFileOpenDirectory "C:\RADDEV\Dbase\Lesson\"

Range("A20").Select
Do While Selection <> ""
If (Right(UCase(Selection), 3)) <> "DBF" Then
MyRTF = "C:\RADDEV\Dbase\Lesson\" & Selection
OpenMyRTF = Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

Documents.Open Filename:=MyRTF, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

MySpace = 0
MyStop = False

' ***********The idea here is to get the position of the last chacracter so that I can set MyStop to TRUE when the 3rd last character is selected. Is there a way to test for EOF() in Word?

' ******** The problem is that the following instructions are not recognised by Excel - Program jumps to the error handler at this point.

Selection.WholeStory
Selection.EndKey Unit:=wdLine
MyEnd = selection.End - 3
Selection.HomeKey Unit:=wdStory


'Evaluate each character and count the spaces or hyphens only
Do While Not MyStop
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
If Selection = "-" Then MySpace = MySpace + 1
If Selection = " " Then MySpace = MySpace + 1
If Selection.End >= MyEnd Then MyStop = True
Loop
MySpace = MySpace + 1

'Message box to display the different results
' MyOk = MsgBox("Word Count = " & Selection.Words.Count & _
' Chr(10) & Chr(13) & _
' "Spaces = " & MySpace & _
' Chr(10) & Chr(13) & _
' "MyCounter = " & MyCounter, vbOKOnly, "Word Counter")

ActiveCell.Offset(0, 3).Select
Selection = MySpace
ActiveCell.Offset(1, -3).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub


Any ideas as to what I am doing wrong?

 

[Ian Mac]

Hi,

Not sure I can answer this fully, but until someone comes along who can...

I think you need to be adding something like:

wdApp.Application.

to the begining of any Word related actions, i.e.

wdApp.Application.Selection.WholeStory
wdApp.Application.Selection.EndKey Unit:=wdLine
MyEnd = wdApp.Application.Selection.End - 3
wdApp.Application.Selection.HomeKey Unit:=wdStory

I doubt the above is entirely correct BUT it may be a starter for 10 for you.

Also, have you added the Microsoft Word #.# Object Library to your references.
You'll need to do this so Excel will understand what your on about.

Hope this get you started.

 

[tusharm]

Word, the program, does know the count of the number of words in a document. Consider using that property as in the example from the Immediate window in Word:
?activedocument.BuiltInDocumentProperties("Number of Words")

Hi Folks
I Know this is an EXCEL forum and I am going to ramble on about WORD, but I really want Excel to do this job for me.

I have a number of sub-contractors who script text for me in *.TXT, *.RTF and *.DBF files.

I pay them for each word that they type.
Presently, the sub-contractor has to check each document (TXT, RTf or DBF) manually to add up the words.

{snip}