Setting a style to a word document created from Excel VBA

[mark hansen]

I have some code I've used in a few projects that creates a new word document (from excel vba) and pastes in a string. I remove the last character (a ") and set the font and font size. What new in this part of the code is I want to set the style to No Space. I recorded the vba from word and paste3d in the lines, but I get an error on the Style line... Any ideas on what I'm doing wrong.

Sub CreateWordDoc()
  Dim wdapp As Word.Application

  Set wdapp = New Word.Application 'Start word
  With wdapp
    .Visible = True  'See Word on Task bar
    .Activate 'Bring word to the front
    .Documents.Add
  End With
  '===========Copy text from cell====================================
   Range("o12").Copy

  '===================================================================
    With wdapp
        .Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
        .Selection.TypeBackspace
        .Selection.HomeKey Unit:=wdStory
        .Selection.Delete Unit:=wdCharacter, Count:=1
        .Selection.WholeStory
        .Selection.Style = ActiveDocument.Styles("No Spacing")
        .Selection.Font.Name = "Arial"
        .Selection.Font.Size = 12
        .Selection.Collapse
    End With
End Sub

Thanks for any ideas
Mark

 

[mark hansen]

I'm still doing some poking around and discovered "Documents.count" and its returning a zero value. Why is that, I have an open document?

 

[mark hansen]

I think I got it.... This seems to work to change the Style of the word document and not leave a zombie process, which I think was also getting in the way.

Sub CreateWordRpt()
    Dim wdapp As Word.Application
On Error GoTo ErrHandler
    Set wdapp = New Word.Application 'Start word
    With wdapp
          .Visible = True  'See Word on Task bar
          .Activate 'Bring word to the front
          .Documents.Add
    End With
'===============copy information==================================
     Range("O12").Copy   'copy excel range
'==================================================================
    With wdapp
         .Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
         .Selection.TypeBackspace
         .Selection.HomeKey Unit:=wdStory
         .Selection.Delete Unit:=wdCharacter, Count:=1
         .Selection.WholeStory  'select all informaiton
         .Selection.Style = ("No Spacing")  'change style
         .Selection.WholeStory  'Selection all informaiton
         .Selection.Range.Font.Name = "Arial"
         .Selection.Range.Font.Size = 11
    End With
    Set wdapp = Nothing
    Exit Sub
ErrHandler:
    Set wdapp = Nothing
 End Sub