Excel and Word problem

[rharrison]

I am creating a report from data held in excel using VBA. So far I am able to
generate the information onto the report, but I am struggling to format the
text, namely the alignment of text. Although I seem to have programmed it
correctly, it still aligns everything to the left.

This is a basic idea I had, but it doesn't work. Does anyone have an idea of
how to fix this?

Also, how do you program the 'Tab' function?


Set testdoc = CreateObject("Word.Application")
testdoc.Visible = True
testdoc.documents.Add

testdoc.Selection.TypeText "test"
testdoc.Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
testdoc.Selection.TypeParagraph
testdoc.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
testdoc.Selection.TypeText "test2"

 

[Pesach Shelnitz]

Hi,

There are two reasons why you are having a problem with the paragraph
alignment. One is that Excel doesn't know the Word enumeration values. You
can either use their numerical values or define constants for them, as I have
done. The other reason is that the Selection object should be collapsed after
the first paragraph. Otherwise, the new alignment setting will apply to all
the paragraphs in the Selection.

When you open one Office application from another, it is good idea to use an
open instance of the application rather than to open a new instance every
time that you run your code. I have added the code to do this.

Notice how I changed the call to Documents.Add. You don't need to specify
the template, but if you want to use a different template, this example shows
you how to specify it. Note that the template name here is for Word 2007. The
Document object returned can be used in your code to save the new file and do
many other things that you might want to do.

I used the Selection object as you did, but it is more efficient to use a
Range object on the Document object instead.

Notice my use of vbTab and vbCrLf for inserting tabs and carriage returns.

Sub Demo()
Const Error_NotRunning = 429
Const wdAlignParagraphLeft = 0
Const wdAlignParagraphRight = 1
Const wdAlignParagraphCenter = 2
Dim wordApp As Object
Dim doc As Object

On Error Resume Next
Set wordApp = GetObject(, "Word.Application")
If Err.Number = Error_NotRunning Then
Set wordApp = CreateObject("Word.Application")
MsgBox "A new instance of Word was created."
Else
MsgBox "An open instance of Word will be used."
End If
On Error GoTo 0
With wordApp
.Visible = True
.Activate
End With

Set doc = wordApp.Documents.Add(Template:=Environ$("AppData") _
& "\Microsoft\Templates\normal.dotm")
With wordApp.Selection
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.TypeText "test1" & vbCrLf
.Collapse Direction:=wdCollapseEnd
.ParagraphFormat.Alignment = wdAlignParagraphRight
.TypeText "test2" & vbTab & "text3" & vbCrLf
.Collapse Direction:=wdCollapseEnd
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.TypeText "test4" & vbTab & "text5" & vbCrLf
.Collapse Direction:=wdCollapseEnd
End With
End Sub

 

[rharrison]

Hi Pesach,

That works perfectly.

Many thanks,

Rob