Print to Word

[xlfan2007]

Hello friends,:hi:

I am no good in VB but like you to help me with this!
I work as Business Development Executive, taking care of analysis & reports/invoicing.


My company requires invoicing in Word, but I like excel which made me design the same Word template represented into excel with all automated calculations with multiple conditions.

In Word, I need to calculate every thing manually and enter into the Word template.

"Save/Print to PDF" option is installed, allowing a PDF copy of the marked PRINT AREA and is not editable.

When there are some customized changes required to be made by my Boss directly, I don't have the Word format for editing.

Please help me with a code, that does the same as "Save/Print to PDF" but also to "Save/Print to Word" which is editable.

I use excel 2007.: pray2:

[Kenneth Hobs]

Welcome to the forum!

What you ask sounds fairly advanced for someone that is not used to VBA. I can't really see what you actually want. Specific details are needed to help you. Luckily, this forum allows you to attach files. If you have more than one, you can zip and attach it. Keep the examples files short. Before and after files are best. Obviously, your after file would be manually created.

If you just want to marry Excel and MSWord, mail merge in MSWord works well. These examples show even more advanced ways to marry the two programs from Excel.

'TypeText method
' http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
' http://www.excelforum.com/showthread.php?p=1946784
' http://vbaexpress.com/forum/showthread.php?p=169877
' http://vbaexpress.com/forum/showthread.php?t=24693

'Copy from Excel, paste to Word
'Lucas, http://vbaexpress.com/forum/showthread.php?p=178364

'FormFields
' http://www.mrexcel.com/forum/showthread.php?p=1639696
' http://www.mrexcel.com/forum/showthread.php?t=333200

'Add Hyperlink to Bookmark
' http://www.excelforum.com/excel-programming/664078-use-excel-vba-to-add-a-hyperlink-to-a-word-document.html#post2006430
'Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054

'Save OLEObject as MSWord Document
' http://vbaexpress.com/forum/showthread.php?t=21619

'Add Table to MSWord
' http://vbaexpress.com/forum/showthread.php?t=23975
' http://vbaexpress.com/forum/showthread.php?p=168731

'Import Word Tables
' vog, http://www.mrexcel.com/forum/showthread.php?t=382541

'Save OLEObject as MSWord DOC
' http://www.mrexcel.com/forum/showthread.php?p=2809902
' http://vbaexpress.com/forum/showthread.php?t=21619

'Get Optionbutton info from MSWord DOC
' http://vbaexpress.com/forum/showthread.php?t=22454

'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html

'Bookmarks
' http://vbaexpress.com/forum/showthread.php?p=185718

[xlfan2007]

Thank you Hobs for great references and solutions.....!:thumb

I have attached, the part and representative data sheet of my Xl workbook and its PDF copy generated using save as PdF option.

My request is a word document same as the Pdf copy.:think:

[Kenneth Hobs]

Here is your next step to set it up so that I can help you. Make an MSWord file with that layout. Add either a formfield or a bookmark for each of the red fields and blank fields that might contain data. Name these with a descriptive name. e.g.
Proforma1, InvoiceDate, Consignee1, Consignee2, Consignee3, etc.

If you want to keep the Inv data, we can use those ranges to complete the MSWord formfields or bookmarks.

[xlfan2007]

Hi Hobs,

Sorry for the late submission of the word template. I have bookmarked the input fields as indicated.


The fields are need to be printed on to the word document from the Excel Invoice template as the invoice details vary as you may have observed.

Kindly, help me with the functional setup required for alternate templates / documents considering for future use..

Your support is really valuable to me.

Thanks:bow:

[Kenneth Hobs]

There were a couple of fields that I was not sure about so I commented them out.

The files are also attached.
Sub FillDoc()
Dim wdApp As Object, WD As Object, rn As Long
Dim s As String
Dim doc As String

doc = ThisWorkbook.Path & "\example.docx"

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

'Set WD = wdApp.Documents.Open(ThisWorkbook.Path & "\example.docx")
With wdApp
'Add makes a copy like from a template even though it may be a DOC file.
Set WD = .Documents.Add(Template:=doc)
'Set WD = .Documents.Open(doc)
.Visible = True
End With

With Worksheets("Inv")
TextInBName WD, "City", .Range("A14").Value2
TextInBName WD, "Company", .Range("A13").Value2
'TextInBName WD, "Country", .Range("").Value2
TextInBName WD, "CountryOfDestination", .Range("E18").Value2
TextInBName WD, "Customer", .Range("A12").Value2
TextInBName WD, "FinalDestination", .Range("E28").Value2
TextInBName WD, "InvoiceDate", .Range("D5").Text
TextInBName WD, "Item1", .Range("B31").Text
TextInBName WD, "Item2", .Range("B32").Text
TextInBName WD, "Item3", .Range("B33").Text
TextInBName WD, "Item4", .Range("B34").Text
TextInBName WD, "Item5", .Range("B35").Text
TextInBName WD, "MfgDate", .Range("A50").Text
TextInBName WD, "NetWeight", .Range("D37").Value2
TextInBName WD, "Packing", .Range("C37").Value2
TextInBName WD, "Phone", .Range("A24").Value2
TextInBName WD, "PortOfDischarge", .Range("D28").Value2
TextInBName WD, "ProformaInvoiceNo", .Range("D3").Value2
TextInBName WD, "RetestDate", .Range("C50").Text
'TextInBName WD, "Street", .Range("").Value2
'TextInBName WD, "", .Range("").Value2
End With

EndNow:
Set WD = Nothing
Set wdApp = Nothing
End Sub


'Similar to Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Requires Tools > References..., Microsoft Word 14.0 Object Libray
Sub TextInBName(ByRef WDoc As Word.Document, ByVal BName As String, ByVal TextIn As String)
With WDoc
If .Bookmarks.Exists(BName) Then
Dim r As Word.Range
Set r = WDoc.Bookmarks(BName).Range
r.Text = TextIn
WDoc.Bookmarks.Add BName, r
Else
Debug.Print "Bookmark not found: " & BName
End If
End With
End Sub

[xlfan2007]

:friends:

Dear Hobs,

Thank you very much for all the help, allowing this fro customisation too.
:bow: :bow: