Formating paragaraph in word from excel vba

Prabakar · June 5th, 2011, 12:33 PM

Hi,

I am working on a macro application where i take data from excel and generate a report with images and tables and a lot of text.

And am facing issues already. I have included the word object reference to the vba project, and have used the following code in class CoverGenerator which implements Generator

Code:

Public Sub Generator_printInDoc(ByRef doc As document)
    Dim para As paragraph
    Set para = doc.Paragraphs.Add
    para.Range.Text = "Heading"
    para.Format.Alignment = wdAlignParagraphRight
    On Error Resume Next
    para.Range.Font.Size = 18
    If Err.Number <> 0 Then MsgBox Err.Description
'I get "Method Size of Object _Font failed" as error. why is it considering it as a method? is it a leter in font class? if so why is it failing?
    para.Range.Font.name = "Cambria"
    For i = 0 To 8
        Set para = doc.Paragraphs.Add
        para.Space2
    Next
    Set para = doc.Paragraphs.Add
    With para
        .Range.Text = "Title Text Comes here"
         .Alignment = wdAlignParagraphCenter
        .Format.Space15
        .Range.Font.Size = 14
        .Range.Font.Bold = True

      
    End With
End Sub

Except for the font size, everything else works, even the font name!!
The same code inside the word file doesn't throw any error. Can someone tell where I am going wrong? or if its a problem caused because of manipulating word objects in excel macro?

I am using: office 2011
OS: snow leopard

Please help!

Thanks

Shasur · June 6th, 2011, 05:33 AM

Not sure why the error comes only when you do from Excel. The main reasons these kind of errors occure because

1) Ambiguity - clearly state every declaration and instantiation

Dim oPara as Word.Paragraph
instead of
Dim oPara as Paragraph

Dim oWA = Word.Applictation
Set oWA = CreateObject/GetObject/ New Word.App

then go for document

Set oDoc = oWA.Documents.Open

or

Set oDoc = oWA.Documents("Filename")

etc

2) There are some properties that expect your window to be visible .. check this also

Try to run keeping the document active and visble by pressing f8 and check if you can get to the point

Cheers
Shasur

Prabakar · June 6th, 2011, 06:08 AM

Hi,

Thanks for the reply.

First, Sorry for being lazy while declaring the variables. I'll be very specific from now on. But this didn't solve my problem

Second, I don't want the window to be open while the document gets generated, for 2 reasons,
1) There are lots of data that I have put in the document somewhere around 40 pages, if its visible then the view getting rendered might slow down the speed of execution of the macro.
2) I don't want the user to close it or do something that would crash my macro.

I didn't know hiding window could cause me problem. If i get any other problems while developing, i'll keep this in mind - thanks
I have skipped this font size issue for now and am working on other stuff.
I am still to research on styles. I need some common styles that i have to apply to different tables, if I am able to do that then I'll circumvent this problem by applying styles that change font size rather than directly doing it.

Other than this,
I am a bit surprised that vba doesn't support reflections. I am creating generic sub routines and am calling them with Run() method to get similar functionality. I am a flex/IOS application developer, VBA is a bit new to me, but i should agree its fun :)

Prabakar · June 6th, 2011, 06:27 AM

Code:

para.Range.Style.Font.Size = 18

This actually worked!!
I really don't understand the object model in word macros, can someone point me to some good books or website that I can use to learn from?

Thanks

Shasur · June 6th, 2011, 06:33 AM

Glad that it worked

Here is the Dev Reference link

http://msdn.microsoft.com/en-us/library/ee861527.aspx

Cheers
Shasur

Prabakar · June 6th, 2011, 06:42 AM

OMG!!

Changing the paragraphs style is actually changing the style of all the paragraphs in the document.

Then I have to apply custom style for this paragraph i suppose?

Shasur · June 6th, 2011, 07:19 AM

para.Range.Font.Size = 18 is the correct one

think there are some problem areas in the document you are trying .. might be some tables etc.

Can you try the code in a new document with some text

I tried your code from Excel - no errors were thrown

Code:

Sub ParaStylingFromExcel()

Dim para As Word.Paragraph
Dim oWA As Word.Application
Dim oWd As Word.Document
Set oWA = CreateObject("Word.Application")
Set oWd = oWA.Documents.Open("C:\Users\shasur\Documents\Sa.docx")
Set para = oWd.Paragraphs.Add
para.Range.Text = "Heading"
    para.Format.Alignment = wdAlignParagraphRight
    'On Error Resume Next
    para.Range.Font.Size = 18
oWd.Save
oWd.Close
End Sub

Cheers
Shasur

Shasur · June 6th, 2011, 07:19 AM

para.Range.Font.Size = 18 is the correct one

think there are some problem areas in the document you are trying .. might be some tables etc.

Can you try the code in a new document with some text

I tried your code from Excel - no errors were thrown

Code:

Sub ParaStylingFromExcel()
 
Dim para As Word.Paragraph
Dim oWA As Word.Application
Dim oWd As Word.Document
Set oWA = CreateObject("Word.Application")
Set oWd = oWA.Documents.Open("C:\Users\shasur\Documents\Sa.docx")
Set para = oWd.Paragraphs.Add
para.Range.Text = "Heading"
    para.Format.Alignment = wdAlignParagraphRight
    'On Error Resume Next
    para.Range.Font.Size = 18
oWd.Save
oWd.Close
End Sub

Cheers
Shasur

Prabakar · June 6th, 2011, 07:35 AM

I tried to run the program in my friends windows system, and it run perfectly fine. Guess there is some problems with osx version 2011.
I'll write the code with error handling statements. The end user is going to run it on windows anyways