Error 642 in Word Automation and Style that is not applied 1

[RagonichaFulva]

Hello,

I am trying to create a code that takes data from an array (in excel) and creates a word document.

The array might be something like:

Log name
Expense Group 1
Lab 1
Person
Person
Person
Lab 2
Person
Person
Lab 3
Person
Person
Person
Person
Lab 3
Person
Expense Group 2
Lab 1
Person
Person
Lab 2
Person
Lab 3
Person
Person
Person
Lab 3
Person
Person
Person

So what my code does is to read each element and applies certain Style according to the element (Title, Expense group, Lab or person)

But this array may vary in the number of labs and persons. That's why I don't use bookmarks.

But my code has two problems:

1.) The second time I execute it it displays a 462 error "server not found"
2.) It applies all the styles except the Heading 1 style. (in red)

here's my code:

==================================================================
Sub createVar15ReportWord(Optional expensearray As Variant, Optional hcarray As Variant)

' You must pick Microsoft Word 14.0 from Tools>References
' in the VB editor to execute Word commands.

Dim appWD As Word.Application
Dim workarray() As Variant
Dim str As String
Dim budgetnum As Integer
'Dim fileWD As Word.Document

budgetnum = 1

'_________ CREATING A NEW INSTANCE OF WORD AND MAKE IT VISIBLE _______________________

Set appWD = CreateObject("Word.Application")

appWD.Visible = True

' Tell Word to create a new document
appWD.Documents.Add

'Set fileWD = appWD.ActiveDocument

'we save the Word Document

'fileWD.SaveAs "Testing.docx"

' Tell Word to paste the contents of the clipboard into the new document
'appWD.Selection.Paste
'_________________________________________________________________________________

'## WRITING THE TITLE OF THE REPORT ##
str = "Log File for Month Actuals Check - " & Strings.format(Now, "ddd, d-mmmm-yyyy hh:mm") & "."
On Error Resume Next
appWD.Selection.Style = ActiveDocument.Styles("Title")
On Error GoTo ErrHandler
appWD.Selection.TypeText Text:=str


For m = 1 To 2
If m = 1 Then
workarray = expensearray
Else
workarray = hcarray
End If

'## WRITING THE BUDGET TITLE ##
str = workarray(0)
If budgetnum = 1 Then
budgetnum = 2
appWD.Selection.TypeParagraph
appWD.Selection.Style = ActiveDocument.Styles("Normal")
appWD.Selection.TypeText Text:=" "
appWD.Selection.TypeParagraph
End If
appWD.Selection.Style = ActiveDocument.Styles("Heading 1")
appWD.Selection.TypeText Text:=str
Debug.Print "Heading 1 es: " & str

'## WRITING THE EXPENSES REPORT DATA ##
For i = 2 To UBound(workarray)
str = workarray(i)
'## WRITING THE EXPENSES LAB TITLE ##
If Left(str, 4) = "Lab:" Then
If i <> UBound(workarray, 1) Then
If Left(workarray(i + 1), 4) <> "Lab:" Then
appWD.Selection.Style = ActiveDocument.Styles("Normal")
appWD.Selection.TypeParagraph
appWD.Selection.TypeParagraph
appWD.Selection.Style = ActiveDocument.Styles("Heading 2")
appWD.Selection.TypeText Text:=str
appWD.Selection.TypeParagraph
appWD.Selection.TypeParagraph
With appWD.ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
.NumberFormat = ChrW(61623)
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleBullet
.NumberPosition = CentimetersToPoints(0.63)
.Alignment = wdListLevelAlignLeft
.TextPosition = CentimetersToPoints(1.27)
.TabPosition = wdUndefined
.ResetOnHigher = 0
.StartAt = 1
With .Font
.Bold = wdUndefined
.Italic = wdUndefined
.Strikethrough = wdUndefined
.Subscript = wdUndefined
.Superscript = wdUndefined
.Shadow = wdUndefined
.Outline = wdUndefined
.Emboss = wdUndefined
.Engrave = wdUndefined
.Allcaps = wdUndefined
.Hidden = wdUndefined
.Underline = wdUndefined
.Color = wdUndefined
.Size = wdUndefined
.Animation = wdUndefined
.DoubleStrikeThrough = wdUndefined
.Name = "Symbol"
End With
.LinkedStyle = ""
End With
appWD.ListGalleries(wdBulletGallery).ListTemplates(1).Name = ""
appWD.Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
wdWord10ListBehavior
End If
End If
Else
str = workarray(i)
appWD.Selection.TypeText Text:=str
appWD.Selection.TypeParagraph

End If

Next

If m = 1 Then
appWD.Selection.Range.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
appWD.Selection.TypeParagraph
appWD.Selection.InsertBreak Type:=wdPageBreak
End If
Next

appWD.Selection.Range.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
'__________________________________________________________________________________
' Save the new document with a sequential file name
If username = "" Then username = Environ("username")
docname = ThisWorkbook.Path & "\MS_WORD_Reports_Folder\Flash_Check_File_Var15percent_" & username & "_" & Strings.format(Now, "dd-mmmm-yyyy hh_mm")
appWD.ActiveDocument.SaveAs FILENAME:=docname

' Close this new word document
appWD.ActiveDocument.Close

' Close the Word application
appWD.Quit
Set appWD = Nothing

'Inform the tool that this report has been created.
wordreport = True
Exit Sub

ErrHandler:
MsgBox "It has been detected that an instance of MS Word might be open or stored in memory, so the Copy of the report in MS Word could not be made.", vbCritical, "Word Report not created"
appWD.ActiveDocument.Close False ' close the document without saving
appWD.Quit
Set appWD = Nothing

End Sub
==================================================================

Any help would be much appreciated.

 

[PHV]

All the Word objects should be full qualified, eg:
appWD.Selection.Style = [!]appWD.[/!]ActiveDocument.Styles("Title")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RagonichaFulva]

Hello,

I changed the code according to your instructions (thus, eliminating the WITH structures - loss of efficiency though).

Nevertheless, these lines:

========================

appWD.ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1).NumberPosition = CentimetersToPoints(0.63)
appWD.ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1).TextPosition = CentimetersToPoints(1.27)

========================

draw the same error. I commented them and the result is satisfactory. The word document looks the same.

Do you have any idea why this part of the code might not show the Style as it should?

========================

'## WRITING THE BUDGET TITLE ##
str = workarray(0)
If budgetnum = 1 Then
budgetnum = 2
appWD.Selection.TypeParagraph
appWD.Selection.Style = appWD.ActiveDocument.Styles("Normal")
appWD.Selection.TypeText Text:=" "
appWD.Selection.TypeParagraph
End If
With appWD.Selection
.Style = appWD.ActiveDocument.Styles("Heading 1")
.TypeText Text:=str
End With

Debug.Print "Heading 1 es: " & str
================================

By the way, thank you very much for your feedback.

 

[PHV]

Again, use this:
[!]appWD.[/!]CentimetersToPoints

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RagonichaFulva]

Hello PHV,

Thank you for your feedback. Actually, I am eliminating certain lines of the code, due to the fact that they make reference to the values Word uses by default. Centimeters to points is one of those lines, as you migh have seen in my previous post.

The question is that I can't seem to find the way to solve the style issue.

This line doesn't format the text as it should:

==============
With appWD.Selection
.Style = appWD.ActiveDocument.Styles("Heading 1")
.TypeText Text:=str
End With
==============

Nevertheless, if I use it in another part of the code it works correctly.

Can you imagine why? It must be something around it.

Thanks in advance.

 

[RagonichaFulva]

Hello,

I finally solved it in a quite strange way...

I duplicated the following structure:

===================================
str = workarray(0)
appWD.Selection.Style = appWD.ActiveDocument.Styles("Heading 1")
appWD.Selection.TypeText Text:=str
'We duplicate this structure because if not the style doesn't apply.
appWD.Selection.TypeParagraph
appWD.Selection.Style = appWD.ActiveDocument.Styles("Heading 1")
appWD.Selection.TypeText Text:=" "
appWD.Selection.TypeBackspace
===================================

Curiously, in this case, the style appplies.

Thanks for your aid.

 

[fumei]

The clue is this:

.TypeParagraph

the style is not being applied to the paragraph. That is why MAKING a paragraph does.

 

[RagonichaFulva]

Hello Fumei,

I thought it might be that before getting to the solution, but seems that .TypeParagraph wasn't enough. I used it before, afetr, between lines... I used combinations of 2 and 3 series of .TypeParagraph, but it was useless.

In order to apply the style correctly I had to use the whole structure:

===========================
appWD.Selection.TypeParagraph
appWD.Selection.Style = appWD.ActiveDocument.Styles("Heading 1")
appWD.Selection.TypeText Text:=" "
appWD.Selection.TypeBackspace
===========================

This way, the structure:

===========================
str = workarray(0)
appWD.Selection.Style = appWD.ActiveDocument.Styles("Heading 1")
appWD.Selection.TypeText Text:=str
===========================

I use in the first place can apply the "Heading 1" style.

This second block actually doesn't apply the "Heading 1" style, but allows Word to apply it to the previous one.

I will thank your thoughts about this.