Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2011, 12:33 PM
Registered User
 
Join Date: Jun 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Question Formating paragaraph in word from excel vba

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

Last edited by Prabakar; June 5th, 2011 at 12:35 PM.. Reason: added more description
 
Old June 6th, 2011, 05:33 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
Prabakar (June 6th, 2011)
 
Old June 6th, 2011, 06:08 AM
Registered User
 
Join Date: Jun 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Exclamation

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 :)
 
Old June 6th, 2011, 06:27 AM
Registered User
 
Join Date: Jun 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Talking Got a solution!

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
 
Old June 6th, 2011, 06:33 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Glad that it worked

Here is the Dev Reference link

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

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 6th, 2011, 06:42 AM
Registered User
 
Join Date: Jun 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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?
 
Old June 6th, 2011, 07:19 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 6th, 2011, 07:19 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 6th, 2011, 07:35 AM
Registered User
 
Join Date: Jun 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel cell Formating Using C# veeruu C# 5 September 21st, 2011 01:56 PM
Write Excel VBA Code to Target Bookmark in Word Doc slbibs Word VBA 3 July 31st, 2009 09:47 AM
Excel to Word VBA genericcereal Word VBA 1 May 11th, 2009 08:25 PM
Changing Cell-formating in Excel via VBA Gert VB How-To 4 May 10th, 2005 01:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.