UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Batch Printing Word Documents, Access 2016    
 
   
Salodor
post Feb 28 2017, 03:17 PM
Post#1



Posts: 9
Joined: 21-December 16



I have been scouring the net for awhile today to try and find some methods to print Word Documents based on either a query result or a field within a table. The best I have found so far was a way to print a Word Document with specifying a file path (below), but I would really like to find a way to print several documents based on either a query or a table. What I am looking to accomplish is printing a set of customer specific instructions in the same order as a report that prints their daily orders. All of their instructions are maintained within a network drive as word documents, updated frequently and unique to each customer. I am using Office 365 (Access 2016 on Windows 7) and can retrieve the order in which all the customer orders printed for the day. I have a table, called "tblTest" that contains just 2 field, 1) the account number and 2) the full file path with name of where their instructions are located at (ie. \\netfiles\account\1234\instructions.docx).

Currently we use a batch printing program that prints all the instructions for a list of customers that could have an order on a given day, regardless if we have any orders or not for them that day. Ideally I would like to use the query result of what printed and then print only those set of instructions for the customer orders that printed, and have the instructions print in the same order as our system to eliminate the manual sorting & matching we currently do. I have been using Access for quite some time, but have not done very much with VBA code until now so this is all relatively foreign to me and any help would be much appreciated.


CODE
Function FnPrint()
    
  Dim objWord

  Dim objDoc
  
  Set objWord = CreateObject("Word.Application")

  Set objDoc = objWord.Documents.Open("Filepath\FileName.docx")

  objWord.Visible = True

  objDoc.PrintOut

  objWord.Quit

End Function

Go to the top of the page
 
MadPiet
post Feb 28 2017, 03:32 PM
Post#2



Posts: 2,084
Joined: 27-February 09



Where is the path to the document(s) that need printing for each customer stored? If it's in a table or can be pulled from a query then you can use this:

CODE
Set objDoc = objWord.Documents.Open("Filepath\FileName.docx")


but instead of hardcoding the "FilePath\FileName.docx" part, you could get it from a column in a table or query.
Go to the top of the page
 
Salodor
post Feb 28 2017, 03:36 PM
Post#3



Posts: 9
Joined: 21-December 16



That is what I would like to do. I can use either a query or a table, makes no difference to me if I do a make table query to dump the results and order or just reference a select query. I am just new to all the VBA coding, but from what research I did today it does look like VBA would be a solution to print the instructions I need each day....it's just I have no idea how to do it other than the manual file path entry I posted here. After hours of searching the net and tinkering with code I am at a lost on how to automate it based on a query or table, so any assistance will be a godsend.
Go to the top of the page
 
MadPiet
post Feb 28 2017, 04:45 PM
Post#4



Posts: 2,084
Joined: 27-February 09



So there is no overlap between Documents and Customers? All of the documents are specific to each customer? (It doesn't really matter, I'm just trying to understand your setup.)
Go to the top of the page
 
Salodor
post Mar 1 2017, 07:59 AM
Post#5



Posts: 9
Joined: 21-December 16



Yes each customer has their own set of unique instructions. We have a network folder with several hundred sub folders containing all the documentation for each customer. Currently we use a batch printing software that uses a text file with a list of complete file paths (network path+file name & extension). The Monday file for the batch software has a list of each and every file (no particular order) for any customer that could have an order come out that day. So come Monday morning someone opens the Batch Printing Software (Print Conductor) and loads the Monday file and starts the print jobs. The program goes down the text list opening each word document listed and prints them, whether a customer had an order that day or not (Example of what that list looks like below). This batch list contains several hundred documents that are printed each day, and these are added in no particular order and never match the order that the daily orders printed in. Since the order does not match, and instructions print for customers that did not have an order, we waste a lot of time sorting and matching the instructions to customer/work orders and throwing away quite a few pages of instructions for customers that did not have any orders that day.

Batch File Example:

V:\AccountDocumentation\Accounts\1234 Customer\Instructions.doc
V:\AccountDocumentation\Accounts\0100 Customer\Instructions.docx
V:\AccountDocumentation\Accounts\9784 Customer\Instructions.docx
V:\AccountDocumentation\Accounts\0001 Customer\Instructions.doc

I currently have a query that lists the print order of the customer orders on a given day (includes account number), and I also have a simple table that contains the Customer Number and File path of their documentation (Hyperlink Field). I then have a query that uses the print order results and pulls in the file path information from the table (example below). Daily orders vary from one day to the next, and seasonality, so you could have 1,000 orders one day with 200 sets of instructions and then 3,000 the next day with 50 sets of instructions (just to illustrate the variation). I would like to use that query result to have access go down each record and print the listed Word Documents. This would eliminate the need of a manual sort/match to the customer orders (as they would print in the same order) and we would not be printing any instructions for those customers that did not have an order that day/week. So in this example below, Access would print the instructions for customer 123456 & then print the instructions for customer 010001, and so on and so on until the end of the query results.

Query Result Example:

Account | Instructions
123456 | \\networkfiles\AccountDocumentation\Accounts\123456 Customer\Instructions.docx
010001 | \\networkfiles\AccountDocumentation\Accounts\010001 Customer\Instructions.doc

I do thank you and everyone for assistance with this and the patience of someone diving into VBA with very little to no experience in VBA coding.
Go to the top of the page
 
MadPiet
post Mar 1 2017, 02:00 PM
Post#6



Posts: 2,084
Joined: 27-February 09



Okay, before I go off into the woods somewhere, it looks like this is the basic logic of what you want:

Loop through "Active Customers"
For Each "Active Customer", retrieve their "Active Files"
For Each "Active File"
Open File in Word
Print File
Close File
Next "Active File"
Next "Active Customer"

Close "Active File" recordset
Close "Active Customer" recordset
Close Connection to Word.

To retrieve a customer's "active files", you pass the CustomerID from the "active customers" recordset to the "active files" query as a parameter, and then open the (filtered) recordset. Then you loop through that and process them (Print them, mark as printed or whatever,...) then you close that WordFile (leaving Word open) and go to the next file to be printed.

There's code around for this stuff. How soon do you have to have this? I just haven't done this stuff since forever, so I'm really rusty. Your explanation was really helpful! Nice job!
Go to the top of the page
 
Salodor
post Mar 1 2017, 02:15 PM
Post#7



Posts: 9
Joined: 21-December 16



I'm in no particular rush and thank you for the tremendous help! I am always trying to continuously improve things and this current process is one that I am always trying to find ways to improve efficiency and reduce waste. I am glad I was able to explain it clearly for you as it can be a little confusing. I have been reading up a little more on VBA coding so I can try and do more things with VBA and understand the code...it's pretty cool to see what can be done with it.
Go to the top of the page
 
MadPiet
post Mar 1 2017, 02:41 PM
Post#8



Posts: 2,084
Joined: 27-February 09



I slapped together a quick demo for you. It's not pretty and has no interface, but it hopefully demonstrates how to do this. It doesn't automate Word yet, but that's pretty trivial once you get the rest working. Here's the code (so far)... A recordset is kind of an in-memory table, so I'm looping through the active customers (in my example they're just flagged as active with a 1. Then I open their related "files" using the qprmCustomerFiles query. I'm passing the CustomerID (from the outer recordset) into the CustomerFiles parameter query. Then I print the file names to the screen. (You would put the Word automation there.... AppWord.Documents.Add(rsFiles.Fields("FullPathToFile) ..etc etc etc)

CODE
Option Compare Database
Option Explicit

Public Sub ProcessCustomers()
    Dim rsCustomers As DAO.Recordset
    Dim qdfActiveCustomers As DAO.QueryDef
    
    Dim qdfFiles As DAO.QueryDef
    Dim rsFiles As DAO.Recordset
    
    Set qdfActiveCustomers = CurrentDb.QueryDefs("qryActiveCustomers")
    Set rsCustomers = qdfActiveCustomers.OpenRecordset
    Set qdfFiles = CurrentDb.QueryDefs("qprmCustomerFiles")
    
    '-- loop through Active Customers (as defined by "qryActiveCustomers")
    Do Until rsCustomers.EOF
        '--Debug.Print rsCustomers.Fields(0)
        
        '-- open the file list related to current Customer
        qdfFiles.Parameters(0) = rsCustomers.Fields(0)
        Set rsFiles = qdfFiles.OpenRecordset
        Do Until rsFiles.EOF
            Debug.Print rsFiles.Fields(0), rsFiles.Fields(1), rsFiles.Fields(2), rsFiles.Fields(3)
            '
            '  This is where you would call the Print method for the file.
            '
            rsFiles.MoveNext
        Loop
        rsFiles.Close
        
        rsCustomers.MoveNext
    Loop
    
    '-- Cleanup
    Set rsFiles = Nothing
    rsCustomers.Close
    Set rsCustomers = Nothing
    Set qdfActiveCustomers = Nothing
End Sub


To run the code... Open the basProcessCustomers module. Hit Ctrl-G to show the Immediate Window and Type/Copy in "ProcessCustomers" (without the quotes) and hit return. You should get this:

CODE
ProcessCustomers
1            John          Brown         \\Path\Doc1.docx
1            John          Brown         \\Path\Doc2.docx
3            Jim           Beam          \\Path\Doc4.docx


It's better to open an instance of Word in your code and then just Add (open) files to the instance and then print then close the files. At the end of the printing, then you can close/destroy the reference to Word. More on that later.

Have fun!
Attached File(s)
Attached File  LoopCustomers.zip ( 40.79K )Number of downloads: 7
 
Go to the top of the page
 
Salodor
post Mar 6 2017, 11:17 AM
Post#9



Posts: 9
Joined: 21-December 16



Thank you so much for this. I just got back in the office from a long weekend and will get a chance later this week to use the code. I'll let you know how it goes.
Go to the top of the page
 
Salodor
post Mar 20 2017, 10:52 AM
Post#10



Posts: 9
Joined: 21-December 16



MadPiet,

I was finally able to get some time to look at this and I just realized I misunderstood an earlier post regarding the overlap between documents, so I added some tables and queries to the example database you provided to help explain this better. You'll find 2 tables, "SampleCustomerFiles" & "SamplePrintOrder".

Table SampleCustomerFiles is what I would be using with a customer number and file path to their instructions; which there is an overlap as you can see in the table. We can have 1 set of instructions for a customer that covers a set of sub-accounts of theirs and another instruction for other sub-accounts of their main account (i.e. 322008 account, 3220 is the main account number and 08 is the sub-account).

Table SamplePrintOrder represents all orders that were printed/available for the day from our system. It contains a list of all the accounts that printed and the order in which they printed. There can, and are in the example, multiple orders that print for an account as you can see in the table.

I added 2 queries, "qryCustomerFileOrder" & "qryPrintOrderList". The query "qryCustomerFileOrder" is for a visual reference only of what the print order was, with having file paths added to it.

Query qryPrintOrderList lists the instructions that need to be printed and the order in which they should print in. I have this query giving me the 1st record for each set of instructions and in the same order as the daily orders printed in. In the sample table data there were 15 orders, but only 7 sets of instructions needed, and they should print in the order of qryPrintOrderList (Doc1, 2, 3, 5, 4, 6, 7).

This query/result is what I would need to replicate or call in the VBA code to loop through and begin opening/printing in word. How can I use this query result or replicate this in the VBA code you have done?

Again I thank you for all the help with this and your patience.

Attached File  LoopCustomers2.zip ( 50.72K )Number of downloads: 6
Go to the top of the page
 
MadPiet
post Mar 22 2017, 04:27 PM
Post#11



Posts: 2,084
Joined: 27-February 09



HI, I did see your message but I am not home so can't really answer properly from here (on my phone too), but maybe the really simple answer is to group the returned document list by Customer and Document path or title (whatever uniquely identifies a Customer and Document combination and use that query to return to doc list. loop thru docs in Word and print
Go to the top of the page
 
MadPiet
post Mar 28 2017, 08:01 PM
Post#12



Posts: 2,084
Joined: 27-February 09



Something like this should work... Note that I'm passing in the Customer's unique ID and the number of times to print each file.

CODE
Public Sub PrintOutCustomerDocuments(ByVal lngCustomerID As Long, ByVal intNumberOfCopies As Integer)

    Dim qdfFiles As DAO.QueryDef
    Dim rsFiles As DAO.Recordset
    '-- Word Automation variables
    Dim appWord As word.Application
    
    Set qdfFiles = CurrentDb.QueryDefs("qprmCustomerFiles")
    
    '-- open the file list related to current Customer
    qdfFiles.Parameters(0) = lngCustomerID
    
    Set rsFiles = qdfFiles.OpenRecordset
    Set appWord = New word.Application
        
    Do Until rsFiles.EOF
        With appWord
            .Visible = True
            .Documents.Open FileName:=rsFiles.Fields("FilePath")
            .ActiveDocument.PrintOut Copies:=intNumberOfCopies
            .ActiveDocument.Close
        EndWith
        '
        '  This is where you would call the Print method for the file.
        '  PrintTargetWordDoc rsFiles.Fields("Filepath")
        rsFiles.MoveNext
    Loop
        
    rsFiles.Close
    
    '-- close Word
    appWord.Close
    Set appWord = Nothing

    '-- Cleanup
    Set rsFiles = Nothing

End Sub
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th August 2017 - 09:41 PM