Printing a word document from excel vba

Question

The following code, when finished, is supposed to get user input of a number (here its hardcoded to 50, and doesn't focus on a specific row - it doesn't feed the data in); look up that specific row or rows in a sheet, copy a blank template of the word document, feed that data into a word document in a specific order, and then print the word document.

The code below what it tries to do is, using excel, copy a word document located at C:\original\path\here to C:\original\path\there. Unfortunately, each time I try running this in Microsoft excel, Excel hangs and then I have to restart it.

So why? What needs to be done? Microsoft word Object Library 14 is referenced in the VBA editor.

 Sub UpdateActionsRows()

    Dim userInput As Long
    userInput = 50

' set up word application, document
    Dim objWord As Word.Application
    Dim objDoc As Document

    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("C:\original\path\here")

    copyFile objDoc, userInput

    objDoc.Close
    objWord.Quit
End Sub

copyfile

Function copyFile(sourceFile As Document, inputRows As Long)
    Dim fso As Object
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    Dim targetFile As String
    targetFile = "C:\original\file\there.docx" 
    fso.copyFile sourceFile, targetFile

End Function

You have the file you are trying to copy already open. You either need to copy before opening, or use a 'SaveAs'. — Wayne G. Dunn, Nov 30 '16 at 19:23
If you continue to use your own `copyFile` `Sub` (I refuse to call it a `Function` because you aren't returning a value!), you will also need to change `fso.copyFile sourceFile, targetFile` to `fso.copyFile sourceFile.FullName, targetFile` because the default property of `sourceFile` in that situation is just the `.Name` property (i.e. without a path). — YowE3K, Nov 30 '16 at 20:09
P.S. Changing `sourceFile` to `sourceFile.FullName` would actually allow your code to work (despite the file being open), but it is almost certainly not what you want to happen. (Assuming, of course, that `"C:\original\path\here"` is actually the name of a Word document and not just a path.) — YowE3K, Nov 30 '16 at 20:12

score 0 · Answer 1 · edited May 23 '17 at 12:01

Try something like this:

 Sub UpdateActionsRows()

    Dim userInput As Long
    userInput = 50

' set up word application, document
    Dim objWord As Word.Application
    Dim objDoc As Document

    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("C:\original\path\here")
    objDoc.Close

    FileCopy objDoc, "C:\original\path\there"
    objWord.Quit
End Sub

I have not tested it and it can be written better, but is hopefully works. And as @Wayne G. Dunn, the error is because you are trying to copy opened file. Here you may see more - VBA to copy a file from one directory to another

Once you perform `objDoc.Close` you won't be able to get at its `Name` property (or, better still, its `FullName` property) for use in the `FileCopy` command. — YowE3K, Nov 30 '16 at 20:16

Printing a word document from excel vba

1 Answers1