Doing Word VBA inside an Excel Macro

[RonMcK3]

In the past, Click Here, I created an Excel Macro that reads an xls file and writes a Word Document.

I now have a slightly more complicated Word document to create from one of my xls files; I need to write some boiler plate lines and paragraphs, then, I need to build the manuscript for a Table of Contents page.

My challenge comes in trying to set the tab stops. When I record a macro in Word, I have no problem, I can record the macro and I can run the macro, all is well. However, when I copy that part of the macro in to the code for Excel to run it, Excel chokes on the following, giving me a "Compile Error: Sub or Function not defined" and the first instance of 'InchesToPoints' is highlighted.

Set wrdDoc = oWDBasic.Documents.Add
oWDBasic.Visible = True
'add text to top of doc and spaces before table
oWDBasic.ActiveDocument.Select
With oWDBasic.Selection
Selection.ParagraphFormat.TabStops.ClearAll
ActiveDocument.DefaultTabStop = InchesToPoints(0.5)
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(5.58), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(6.13), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.Font.Name = "Arial"

End With
How do I work my way around this? Is there a service pack I have to install? Is there a way from within Excel to set the Word tab stops? Do I need to create a function (naming it InchesToPoints) that turns 1 inch into X points (where X is probably 12pt/in), doing the math to convert each measurement? Or, do I have to build my table of contents as a Word Table loading the cells in Word from the cells in Excel?

Thanks!

[fumei]

Are you using early binding? I notice you seem to be using

Dim ObjWord As Object

rather than

Dim ObjWord As Word.Application

If so, then it does not know what InchesToPoint means (more specifically "Sub or Function not defined"). InchesToPoint is a Function (not a property).

[RonMcK3]

Gerry,

Let me make that change and see how that helps me.

Thanks!

[fumei]

To use early binding you have to have a Reference. Do you?

[RonMcK3]

Gerry,

Here is the code from the beginning down to the point I showed before.

I believe that I invoke word with the following:

Set oWDBasic = CreateObject("Word.Application")
So, is this early or late binding?

Thanks, Ron

Sub XLToWordTable2a()

Dim ObjWord As Object, Rng As Range
Dim wrdDoc As Object, Ocell As Variant, TC As Variant
Dim LastRow As Long, LastCol As Long, Cnt As Long
Dim FirstRow As Long, FirstCol As Long
Dim Grade As Integer, MaxGrade As Integer, Sht As Integer
Dim GradeNum As String
Sht = 1
MaxGrade = 5
FirstRow = 1
Set oWDBasic = CreateObject("Word.Application")
Do While Sht > 0 And Sht <= MaxGrade
GradeNum = "Grade " & Trim(Str(Sht))
Sheets(GradeNum).Select
'set XL range to suit
'determine table size from Xl range (used range in this eg.)
LastRow = Sheets(GradeNum).UsedRange.Rows.Count
' LastCol = Sheets(GradeNum).UsedRange.Columns.Count
LastCol = 3
' Debug.Print LastRow, LastCol

Set wrdDoc = oWDBasic.Documents.Add
oWDBasic.Visible = True
'add text to top of doc and spaces before table
oWDBasic.ActiveDocument.Select
With oWDBasic.Selection
Selection.ParagraphFormat.TabStops.ClearAll
ActiveDocument.DefaultTabStop = InchesToPoints(0.5)
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(5.58), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(6.13), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
Selection.Font.Name = "Arial"

' other code not germaine at the moment

End With
Loop

[RonMcK3]

Gerry,

Commenting out one DIM and adding another (as follows) gives me a new error: Compile error: User-defined type not defined.
' Dim ObjWord As Object, Rng As Range
Dim ObjWord As Word.Application
Dim Rng As Range

Cheers,

[Dave]

To insert tab stops...
With WordApp.activedocument.Content
.ParagraphFormat.TabStops.Add Position:=230, Alignment:=2
End With
To use...

.TypeText Text:="Bart" & vbTab & "Scratchy & vbCrLf

HTH. Dave
edit: this may be helpful http://www.vbaexpress.com/forum/showthread.php?t=28845

[RonMcK3]

Dave,

I'm feeling extra dense today; what is 'WordApp'? An Object? I don't find it in the code of your referenced thread (which I recall you referred me to 6 or 9 months ago, good discussion, thanks, again.)

Changing WordApp to wrdDoc, an object that I have in my code, and running the code gets me as far as: With wrdDoc.activedocument.Content where I get a Runtime Error '438': Object Doesn't support this property or method.

If I remove 'wrdDoc' from the above line and re-run, I get the same error at the same point.

What am I forgetting at the moment?

Thanks,

[Tinbendr]

Dim Rng As Range
Which range? Word or Excel? How does your code know which one?

Dim Rng as Word.Range

[RonMcK3]

Tinbender,

The code here is left over from another program, in that one we have the following code:

Sub XLToWordTable()
Dim ObjWord As Object, Rng As Range

<skipping a bunch of other code

'vba set XL range
Set Rng = Sheets(GradeNum).Range(ActiveSheet.Cells(FirstRow, 1), ActiveSheet.Cells(LastRow, LastCol))
'insert XL cell.value to table location
'table(1) in this example (ie change table to suit)
Cnt = 1
For Each Ocell In Rng
Set TC = oWDBasic.ActiveDocument.Tables(1).Range.Cells(Cnt)
TC.Range.InsertAfter Ocell.Value
Cnt = Cnt + 1
Next Ocell

<skipping more code

End Sub



You can see the entire source program at http://www.vbaexpress.com/forum/showpost.php?p=201019&postcount=18

Does this answer your question? Adding 'excel.' in front of rng will better identify what it contains.

Thanks,

[Dave]

WordApp is the Word application object. Your code...
With oWDBasic.activedocument.Content
.ParagraphFormat.TabStops.Add Position:=230, Alignment:=2
End With
Note: you did not Dim oWDBasic as an object. HTH. Dave

[RonMcK3]

Dave,

So, wherever I use owdBasic, I need to substitute WordApp and then add the line, below, Dim WordApp as Object?
Sub XLToWordTable2a()
Dim ObjWord As Object, Rng As Range
Dim wrdDoc As Object, Ocell As Variant, TC As Variant
Dim LastRow As Long, LastCol As Long, Cnt As Long
Dim FirstRow As Long, FirstCol As Long
Dim Grade As Integer, MaxGrade As Integer, Sht As Integer
Dim GradeNum As String
Dim WordApp As Object
Sht = 1
MaxGrade = 5
FirstRow = 1
Set WordApp = CreateObject("Word.Application")
...
End Sub

[fumei]

Do you, or do you not, have Word Referenced? That is, do you, or do you not, have it listed - Micirosodt Word version Object Library - (checked) under Tools > References?

If you DO, then - as I previously stated - use:

Dim name As Word.Application

NOT Dim name As Object

Yes, you are using Set blah = CreateObject("Word.Application")

but you need to declare it as such. This is early binding, and it gives you full and complete access to the entire Word Object Library.

As for declaring As Range...

It is crucial - especially with ANY coding between Excel and Word (and vice versa) that your range objects be explicitly and fully declared. If you declare the following in Excel:
Dim Rng As Range
then that is an Excel Range, a different beastie altogether from a range object in Word.

[TonyJollans]

I've only skimmed this but it looks like you're all talking around the problem.

Using Early Binding, as Gerry has described, makes it easier to develop and test even if you eventually go with late binding. That said, you are mixing up your objects and not properly qualifying everything you need to.

Your problems start here:

With oWDBasic.Selection
Selection.ParagraphFormat.TabStops.ClearAll
' ... etc.

You are working With the Word Selection, so should be coding like this:

With oWDBasic.Selection
.ParagraphFormat.TabStops.ClearAll
' ... etc.

There isn't anything actually wrong, in principle, coding as you have, but if you want to use the Word Selection instead of the Excel Selection you must explicitly specify it, ...

With oWDBasic.Selection
oWDBasic.Selection.ParagraphFormat.TabStops.ClearAll
' ... etc.

.. which tends to highlight the duplication a little.

Equally, your references to ActiveDocument need properly qualifying, and, causing the problem you first asked about, InchesToPoints is a Word function, and must be qualified: oWDBasic.InchesToPoints. The other Word constants, wdAlignTabLeft and wdTabLeaderSpaces, need the same treatment as well.

[mdmackillop]

Hi Ron,
It's maybe a cop out, but why not leave your formatting macro in the Word template and run it from Excel.


Sub test()
Dim wd
Dim Doc
Set wd = CreateObject("Word.application")
Set Doc = wd.Documents.Add(Template:="C:\PDS\mydoc.dot")
wd.Visible = True
wd.Application.Run "MyTest"
End Sub

[fumei]

rofl

sorry, that is not meant as any criticism...more like "out of the mouths of babes".



Not that you are a babe...or...maybe you are.

In any case, yes, that may be a solution and we have been trying to be way too complicated.

[RonMcK]

Malcolm, et al,

You're on the right track. This code is for an Excel macro/program that will initialize itself and then open an instance of Word after which it will loop through a set of worksheets, one by one, creating a Word doc for each, printing to the file, and saving it, before looping back for the next worksheet.

Anyway, I've read all the advice and suggestions posted today and will pull the project up when I get home and can work on it.

Thanks, all!

[RonMcK3]

Referencing #13, above


Do you, or do you not, have Word Referenced? That is, do you, or do you not, have it listed - Micirosodt Word version Object Library - (checked) under Tools > References?
I'm just beginning to use Excel 2007. Where do I find it in 2007? Alt-T-(and what other letter for References?).

alt-T-E gets me the Scenario Manager.
alt-T-R gets me a Research panel with a drop list of All Reference Books.

Thanks,

[mdmackillop]

Hi Ron,
Try doing it in the Visual Basic Editor (Alt + F11)
Regards
MD

[RonMcK3]

Thank you, Malcolm.

Well, I enabled (clicked on) Microsoft Word 12.0 Object Library.
Then I changed my Dim statement as Gerry (fumei) directed:
Sub XLToWordTable2a()

' Dim ObjWord As Object, Rng As Range
Dim oWDBasic As Word.Application
Dim Rng As Range
When I press F8, Microsoft Visual Basic reports "Compile error: User-defined type not defined and 'objWord As Word.Application' is highlighted.

Suggestions?

Thanks,

[TonyJollans]

Go back to the References dialog and make sure (a) that Word is actually checked - many a time I've thought I've checked a reference only to find I haven't actually succeeded in doing so, and (b) that all other references are correct - and not 'MISSING' - as errors anywhere in the list can cause strange symptoms.

When you get it set properly, you will need to change:

Dim Rng As Range

to

Dim Rng As Word.Range

[mdmackillop]

Hi Ron,
A very basic example. Save both files to the same folder and run the code in the Excel file

[RonMcK]

Hi Ron,
A very basic example. Save both files to the same folder and run the code in the Excel file Hi, Malcolm,

Five or six months ago, with help from you and Dave, primarily, I crafted a solution much similar to this one for copying an Excel table into a Word table. Your sample works very nicely and is even more elegant than the solution I had before.

My current challenge is attempting to read a different Excel table and write out a Word document where I use tab stops and merely print what are essentially Table of Contents entries with a vbTab between each and its page number.

I could treat this Table of Contents as an Excel Table and write it to a Word Table. The drawback in this solution is that it forces the next user in the processing chain to re-write his macros or script for importing simply because I am not delivering the tab-delimited file he (or she) gets currently.

Thanks,