CODEWITHSUNDEEP
vbaexcelms-word
Alex
Alex

Reputation: 47

From a VBA Excel file open a word template, enter a number and print

So far I've had great sucess with questions/solutions and obtaining lots of knowledge in excel vba but once again i'm stuck on a different kind of problem.

Here is my scenario:

We do samples that are serialized and tracked in an excel vba spreadsheet. Multiple lines can be added and they are identified by the serial number. Each sample sent contains a information sheet that is also serialized. Right now once we enter the serial number into excel, we have to manually enter the serial number onto the information sheet, print, and repeat. This gets frusterating as sometimes we have to send upwards of 40 samples out at a time.

My Ideal Solution:

I would like to take the userform I have in place for data entry and use a listbox to record all the samples entered to the spreadsheet. From this, if the user decides to print the sheets he can hit a command button to run a macro. This macro would take the listbox items (One at a time) and input the information to a specific part on the word template file, and then print it. Rinse and repeat.

What I am really asking: Can anyone provide a generic code for opening a word file from excel, inputting a line in a specified area, and then printing it. I know some VBA now, but this is a bit out of my league, and by a bit waaaay out of it.

Edit: By specified I ment that I would like to have the option to choose where the text from the list box is placed on the sheet. Ideally it would be the top right hand corner, and middle right hand side. But I would need to adjust the location make it fit perfectly! Thanks for the help, A.

Upvotes: 1

Views: 10038

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

Try with following code. I added some comments inside to explain what you need to change.

Important! use either Option 1 or Option 2 as proposed inside the code.

Sub OpenWord_Place_Some_Text()

    Dim WRD As Object
    Set WRD = CreateObject("Word.Application")

    'optional line to see Word applicaton
    WRD.Visible = True

    Dim DOC As Object
   'Option 1. for new empty document...:
    Set DOC = WRD.Documents.Add

    'or...
    'Option 2. for existing document
    Set DOC = WRD.Documents.Open("C:\your folder\your document.docx")

    'to place your text precisily in word document do it with TextBox
    'set your LEFT, TOP, WIDTH, HEIGHT parameters based on experiments
    With DOC.Shapes.AddTextbox(msoTextOrientationHorizontal, Left:=100, Top:=100, Width:=100, Height:=100)
        .Line.Visible = msoFalse
        .TextFrame.TextRange.Text = "YOUR TEXT HERE"
    End With

    'set active printer to one you use here
    WRD.ActivePrinter = "PDFCreator"
    'print document
    DOC.PrintOut
    'close document without saving
    DOC.Close False
    'close application
    WRD.Quit
    Set WRD = Nothing

End Sub

Upvotes: 3

Related Questions