Re: From Excel to Word

Jaymond Flurrie · #1 05-08-2011, 10:33 AM

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:

Code:

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 · #2 05-08-2011, 01:45 PM

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

Code:

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 · #3 05-08-2011, 04:36 PM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Empty rules	rene.kamphuis@ciz.nl	Mail Merge	21	04-15-2011 12:34 AM
Shared Calendar Empty	worksmart	Outlook	0	01-21-2011 06:32 AM
Appending Word Docs	Frank	Word	0	02-26-2010 04:38 PM
Word docs. help	freebee2	Word	0	10-02-2008 11:09 AM
Populate an empty .PAB file	imported_contiw	Outlook	2	08-17-2006 08:50 PM