Solved

Excel VBA - Insert data into Word doc and as .pdf

Posted on 2014-04-05
2
1,039 Views
Last Modified: 2014-04-06
Hi
I am using the following Excel VBA code to insert data into a Word template and then create a pdf file from the Word doc.
The process will be used to loop through hundreds of Excel rows and do this for each row
How do I change the code to make sure that the process runs as quickly s possible?


Sub oTest()
     Call FindBMark("inv1", "200", "12 Jan 2014", "Mr Jingles", "School A", "Robbie", "Dave", "082 888 888", "dave@abc.com")
End Sub


Sub FindBMark(ByVal oInvoiceNumber As String, ByVal oInvoiceAmount As String, ByVal oInvoiceDate As String, ByVal oBilledTo As String, _
ByVal oSchool As String, ByVal oChildsName As String, ByVal oGuardian As String, ByVal oContactNumber As String, ByVal oEmailAddress As String)

   
   On Error GoTo EH
   
   Dim MYDOC_DIR As String
   MYDOC_DIR = Environ("userprofile") & "\My Documents"
   Dim TEMPLATE_INVOICE As String
   TEMPLATE_INVOICE = MYDOC_DIR & "\Accounting\Customer_Invoices\"
   Dim EMAIL_PDF As String
   EMAIL_PDF = MYDOC_DIR & "\Accounting\Email_PDF\"
   
   Dim WordObj As Word.Application
   Dim WordDoc As Word.Document

   Dim WordRange As Word.Range
   Set WordObj = CreateObject("Word.Application")
   
   Set WordDoc = WordObj.documents.Open _
       (MYDOC_DIR & "\Accounting\Customer_Invoices\Invoice_Template.dotx")
   WordObj.Visible = True

  'WordObj.ActiveWindow.View.ShowBookmarks = True 'Turn on Bookmarks

   ' Go to the bookmark and insert the value held in a variable at that point
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Invoice_Number")
   WordRange.InsertAfter oInvoiceNumber
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Invoice_Amount")
   WordRange.InsertAfter oInvoiceAmount
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Invoice_Date")
   WordRange.InsertAfter oInvoiceDate
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Billed_To")
   WordRange.InsertAfter oBilledTo
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="School")
   WordRange.InsertAfter oSchool
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Childs_Name")
   WordRange.InsertAfter oChildsName
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Guardian")
   WordRange.InsertAfter oGuardian
      Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Contact_Number")
   WordRange.InsertAfter oContactNumber
   Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Email_Address")
   WordRange.InsertAfter oEmailAddress
   

   ' Uncomment the next line of code to print the document.
   ' WordDoc.PrintOut Background:=False

   ' Uncomment the next line of code to save the modified document.
   ' WordDoc.Save
   
     WordDoc.ExportAsFixedFormat OutputFileName:= _
        EMAIL_PDF & "Doc2.pdf", ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
        wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
        IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
        wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
        True, UseISO19005_1:=False

   ' Uncomment the line of code to quit Microsoft Word without

   ' saving changes to the document.
   WordObj.Quit SaveChanges:=wdDoNotSaveChanges
   Set WordObj = Nothing
   Exit Sub
   
EH:
     WordObj.Quit SaveChanges:=wdDoNotSaveChanges
     Set WordObj = Nothing
     Set WordObj = Nothing
     MsgBox Err.Description
     
End Sub
0
Comment
Question by:murbro
2 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39980836
I think this is what you want:
Option Explicit

Dim WordObj As Object
Dim WordDoc As Object

Sub Demo()

    Dim MYDOC_DIR As String
    Dim WordRange As Object

    On Error GoTo EH

    MYDOC_DIR = Environ("userprofile") & "\My Documents"

    Set WordObj = CreateObject("Word.Application")

    WordObj.Visible = True

    Dim i As Integer
    i = 1    ' remove this line if your data doesn't have a header
    For i = 1 To Worksheets("WorksheetName").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        Set WordDoc = WordObj.Documents.Open(MYDOC_DIR & "\Accounting\Customer_Invoices\Invoice_Template.dotx")
        Call FindBMark(Range("A" & i).Value, Range("B" & i).Value, Range("C" & i).Value, Range("D" & i).Value, Range("E" & i).Value, Range("F" & i).Value, Range("G" & i).Value, Range("H" & i).Value, Range("I" & i).Value)
        WordDoc.Close
    Next

    ' Uncomment the line of code to quit Microsoft Word without
    ' saving changes to the document.
    WordObj.Quit SaveChanges:=wdDoNotSaveChanges
    Set WordObj = Nothing
    Exit Sub

EH:
    WordObj.Quit SaveChanges:=wdDoNotSaveChanges
    Set WordObj = Nothing
    Set WordObj = Nothing
    MsgBox Err.Description

End Sub

Sub FindBMark(ByVal oInvoiceNumber As String, ByVal oInvoiceAmount As String, ByVal oInvoiceDate As String, ByVal oBilledTo As String, _
              ByVal oSchool As String, ByVal oChildsName As String, ByVal oGuardian As String, ByVal oContactNumber As String, ByVal oEmailAddress As String)


    Dim TEMPLATE_INVOICE As String
    Dim EMAIL_PDF As String
    
    TEMPLATE_INVOICE = MYDOC_DIR & "\Accounting\Customer_Invoices\"
    EMAIL_PDF = MYDOC_DIR & "\Accounting\Email_PDF\"

    'WordObj.ActiveWindow.View.ShowBookmarks = True 'Turn on Bookmarks

    ' Go to the bookmark and insert the value held in a variable at that point
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Invoice_Number")
    WordRange.InsertAfter oInvoiceNumber
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Invoice_Amount")
    WordRange.InsertAfter oInvoiceAmount
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Invoice_Date")
    WordRange.InsertAfter oInvoiceDate
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Billed_To")
    WordRange.InsertAfter oBilledTo
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="School")
    WordRange.InsertAfter oSchool
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Childs_Name")
    WordRange.InsertAfter oChildsName
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Guardian")
    WordRange.InsertAfter oGuardian
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Contact_Number")
    WordRange.InsertAfter oContactNumber
    Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="Email_Address")
    WordRange.InsertAfter oEmailAddress

    ' Uncomment the next line of code to print the document.
    ' WordDoc.PrintOut Background:=False

    ' Uncomment the next line of code to save the modified document.
    ' WordDoc.Save

    WordDoc.ExportAsFixedFormat OutputFileName:= _
                                EMAIL_PDF & "Doc2.pdf", ExportFormat:=wdExportFormatPDF, _
                                OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
                                wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
                                IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
                                wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
                                True, UseISO19005_1:=False

End Sub

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 39980959
Thanks
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question