Re: From Excel to Word - empty docs?

[Jaymond Flurrie]

I have an Excel file that uses VBA to print stuff into Word. It works otherwise well, but when Word is already running when I run my program, it creates empty files. If I use CTRL+ALT+DEL to kill Word processes from background before running my file, it works just like I want.



Can I avoid this "already existing Word messing things"-problem somehow?

Here's the cleaned code I use right now:

Sub TeeDocTiedostot()
    Dim appWD As Word.Application
    Dim wdRngTable As Word.Range   'create a range variable
    Dim j As Integer
    Dim i As Integer
    Dim strDocTiedosto As String
    Dim strKansionNimi As String
    
    'Käytetään kansion nimenä tätä hetkeä
    strKansionNimi = Format(Now, "dd-mm-yy-hh-mm-ss")
    
    'Tehdään uusi kansio, jonne Word-tiedostot tulostetaan
    MkDir ThisWorkbook.Path & Application.PathSeparator & strKansionNimi
    
    'Avataan ja näytetään Word
    Set appWD = CreateObject("Word.Application.14")
    appWD.Visible = False

    'Käydään palautteen saajat läpi
    For j = LBound(vPalautteet, 1) To UBound(vPalautteet, 1) - 1
        
        'Käske Wordia tekemään uusi dokumentti
        appWD.Documents.Add
    
        'Haetaan Wordista oikea kohta
        Set wdRngTable = ActiveDocument.Content
        wdRngTable.Collapse Direction:=wdCollapseEnd
        
        With ActiveDocument
            'Ja luodaan uusi taulu
            .Tables.Add wdRngTable, 1, 2
            
            With .Tables(1)
                .PreferredWidth = InchesToPoints(10#)
                .Range.Font.Size = 10
                .Range.Font.Name = "Arial"
                .Style = "Table Grid"
            End With
        End With
        
        'Rakennetaan tiedostonimi
        strDocTiedosto = ThisWorkbook.Path & Application.PathSeparator & strKansionNimi & Application.PathSeparator & j & ".doc"
        
        'Tallennetaan tiedosto
        appWD.ActiveDocument.SaveAs (strDocTiedosto)
        
        'Suljetaan tämä dokumentti
        appWD.ActiveDocument.Close
    Next j
    
    'Suljetaan word
    appWD.Quit
    Set appWD = Nothing
End Sub

Do I start or end the use of Word in some bad way when I do like this? What's a better way to handle things?

[macropod]

You need to make sure which instance of Word your code is trying to work with. Try:

Sub TeeDocTiedostot()
Dim appWD As Word.Application
Dim DocWD As Word.Document
Dim wdRngTable As Word.Range   'create a range variable
Dim i As Integer, j As Integer
Dim strDocTiedosto As String, strKansionNimi As String
Dim strSep As String
strSep = Application.PathSeparator
 
'Käytetään kansion nimenä tätä hetkeä
strKansionNimi = Format(Now, "dd-mm-yy-hh-mm-ss")
 
'Tehdään uusi kansio, jonne Word-tiedostot tulostetaan
MkDir ThisWorkbook.Path & Application.PathSeparator & strKansionNimi
 
'Avataan ja näytetään Word
Set appWD = CreateObject("Word.Application")
appWD.Visible = False
 
'Käydään palautteen saajat läpi
For j = LBound(vPalautteet, 1) To UBound(vPalautteet, 1) - 1
 
  'Rakennetaan tiedostonimi
  strDocTiedosto = ThisWorkbook.Path & strSep & strKansionNimi & strSep & j & ".doc"
 
  'Käske Wordia tekemään uusi dokumentti
  Set DocWD = appWD.Documents.Add
 
  With DocWD
    'Haetaan Wordista oikea kohta
    Set wdRngTable = .Range(0, 0)
 
    'Ja luodaan uusi taulu
    .Tables.Add wdRngTable, 1, 2
 
    With .Tables(1)
      .PreferredWidth = InchesToPoints(10#)
      .Range.Font.Size = 10
      .Range.Font.Name = "Arial"
      .Style = "Table Grid"
    End With
 
    'Tallennetaan tiedosto
    .SaveAs (strDocTiedosto)
 
    'Suljetaan tämä dokumentti
    .Close
 
  End With
Next j
 
'Suljetaan word
appWD.Quit
Set wdRngTable = Nothing: Set DocWD = Nothing: Set appWD = Nothing
End Sub

[Jaymond Flurrie]

And solved.

Thank you very much, this helps me a lot!

I haven't worked with Word VBA a lot, but lately my customers have asked me to provide them a functionality to print Word documents (ie. provide the results as Word documents). I can already tell you that I will ask for more help here, but it is nice to see we have here people who are great with Word!