• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

Word Macro: Pausing and then Creating a PDF File

I have a Word macro that creates a final document from a tab delimited file, which has been exported from Filemaker.  Towards the end, I now want to be able to pause the macro so I can make some final adustments to the document and then Print/Save it as a PDF file to get it ready to upload to our website.
1) What is the code to pause a macro.  I looked it up in macro help and couldn't find it.
2) How do you cause the Print command below that is saving the file as a pdf to save it automatically instead of the dialogue box coming up?  Also, the Word macro does not recognize that the PDF dialogue SaveAs box has come up and continues running commands.  If I can't get it to save automatically, I defintely don't want the macro to continue while I am dealing with the PDF SaveAs dialog box.
3) Can you insert a code into the PDF Save as box to tell it the name of the file to SaveAs?  I couldn't but at least I did a workaround in saving the Word file first and the PDF SaveAs just use that name as its suggested file SaveAs name.  Thank you.

    'Pause
    '  "wr_setup_r" & MyValue & ".doc"
   
    ChangeFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\PDFs\"
    ActivePrinter = "CutePDF Writer"
    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
0
rvfowler2
Asked:
rvfowler2
  • 11
  • 8
5 Solutions
 
Chris BottomleySoftware Quality Lead EngineerCommented:
1. To pause execution type stop in your code then f5 to resume
2. Perhaps application.display alerts = false
3. I take it you have tried Application.PrintOut FileName:="filename.pdf" ...

Chris
0
 
PandaPantsCommented:
As far as I know, there isn't a Pause command per se in VBA. You can force the application to wait for a particular condition to be True before continuing, but even then it's tricky to keep VBA from running slightly ahead of itself. I've addressed this in the past in several different ways, including installing a msgbox that must be clicked before continuing, or adding a Wait loop in which it waits a couple seconds, checks for a the value of a variable or custom document property, then either loops again or continues (be sure to use a DoEvents command if you go this route).

Another alternative, obviously, is to break the macro into two pieces, ending the first one with a message box ("Click here to continue") and making the Click event call the second macro.

As to your second and third questions, I think you can insert the name of the file to SaveAs -- that looks like the first parameter of the Application.PrintOut command you provided. Right now it reads  FileName:="", but if you were to put the desired filename in there, between the quotation marks, that might solve your problem. Conceptually, the PDF SaveAs and the Print commands are the same thing, aren't they? When you're saving the document as a PDF, you're printing to a virtual printer, "CutePDF Writer" that works by saving the document as a PDF; if you actually wanted to print the doc to hard copy, you'd have to print the PDF to a different printer. So, I think if you were to define a variable to hold your PDF filename and then feed that to the PrintOut command, you'd be golden.

Here's a thought: Just before the PrintOut command, insert a message box asking for the filename, then pass the result to the PrintOut command:

dim sFileName = InputBox("Enter filename")
Application.PrintOut FileName:=sFileName, Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0

That just might solve both problems at once (although, if you have other tweaks you want to make to the document before continuing, you'll still have to work out the pause issue).
0
 
PandaPantsCommented:
Ah, forgot about the Stop / F5 approach. Should have listed that. Thanks, Chris.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rvfowler2Author Commented:
Good suggestions.  I also assumed the PDF would accept my string the same way Word does, but no such luck.  Seems the PDF save as dialogue is a different beast.  It did not take my string I've commented out above:  "wr_setup_r" & MyValue & ".doc".  I did try placing that between the "" in Application.PrintOut FileName:="", but received an error.  Oh crum, just realized I may have left .doc in there instead of .pdf.  Let me give it a try.
0
 
rvfowler2Author Commented:
Actually, I didn't make a dumb mistake.  The PDF "FileName" is looking for the file to open.  "OutputFileName" is the PDF code for saving the file.  Also,
1. Tried the mssg box, but it won't let me edit Word while it is open.  So I put a Stop in and added a message box (see below); however the stop command opens the VB editor, which I don't want to do if I pass this on to someone less technical.

2. For some reason, the Word doc saves both my rec number and Property Value, but the PDF does not save the recvalue.  Can you see where my code might be off below?  I couldn't find it.

----------------------

'Save As Without Rent Roll (In order to convert to PDF and publish publicly).
    ChangeFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\"
   
    Dim dlg As Dialog
    Set dlg = Dialogs(wdDialogFileSaveAs)
    dlg.Name = "upc_setup_r" & MyValue & PropValue & "test.doc" 'defined and captured at beginning.
    dlg.Show 'later remove this step.
   
    Message = "Make all changes to generic Setup before it auto saves to a PDF.  Press OK now.  To Resume Macro, Press F5."
    Title = "PAUSE FOR EDITS"
    MyValue = InputBox(Message, Title)
   
    Stop
   
    ChangeFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\PDFs\"
    ActivePrinter = "CutePDF Writer"
    Application.PrintOut OutputFileName:="upc_setup_r" & MyValue & PropValue & "test.pdf", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
0
 
PandaPantsCommented:
I'm confused on what you mean by "recvalue". Is that the same as the variable "MyValue"? Are you inviting the user to type a value into the InputBox? If so, it's not clear (to me) from the instructions. In any event, the PDF OutPutFileName value is using the MyValue that has been reset after retrieving whatever the input was from the InputBox (in the line just ahead of the Stop command), which is probably why it's not the same there as earlier. (If you're not actually typing anything into the InputBox, then MyValue is likely to be Null or an empty string.)
0
 
PandaPantsCommented:
If your Dialog object still exists when you get down to the PrintOut section, perhaps you could set the OutputFileName to dlg.Name?

Application.PrintOut OutputFileName:=dlg.Name, Range:= . . .

As to the Stop issue, I'd still be inclined to simply end the macro with your message box (if you're not collecting information, make it a MsgBox instead of an InputBox), and include information on how to proceed with the save to PDF. That could possibly include choosing a menu option from a floating toolbar that you've added to the Word document; the menu option would trigger the print-to-PDF code, which would be the stuff after the Stop command in your last sample. Of course, then you'd have to have a way of saving the dlg.Name data, because it would definitely be out of scope once the first macro ended. You could do that by adding a custom document property to the Word doc, as shown below.

In the second macro, you'd retrieve the value of the custom document property with code similar to this:


   ChangeFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\PDFs\"
    ActivePrinter = "CutePDF Writer"
    Application.PrintOut OutputFileName:=ActiveDocument.CustomDocumentProperties("cdpFileName").Value, Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
Dim sMyCDP As String
    Dim bCDPFound As Boolean
    sMyCDP = "cdpFileName"
    bCDPFound = False

    Dim xx As DocumentProperty
    For Each xx In ActiveDocument.CustomDocumentProperties
        If LCase(xx.Name) = LCase(sMyCDP) Then
            bCDPFound = True
        End If ' xx.Name <> CdpName
    Next xx
    If bCDPFound Then
        ' Populate the CDP with the current name:
        ActiveDocument.CustomDocumentProperties(sMyCDP) = "upc_setup_r" & MyValue & PropValue & "test.doc"
    Else ' bCDPFound = False
        'CDP not found, so create and populate it:
        ActiveDocument.CustomDocumentProperties.Add _
            Name:=sMyCDP, LinkToContent:=False, Value:="upc_setup_r" & MyValue & PropValue & "test.doc", _
            Type:=msoPropertyTypeString
    End If ' bCDPFound

Open in new window

0
 
rvfowler2Author Commented:
Yes, sorry, MyValue is the Value of the Record#.   Yes, I do invite the user to type the value in.  As for the PropValue, I get that from the tab del file itself.  

Thanks.  I really don't need an input box, so either take away the MyValue = or look up a straight message box (It's been awhile since I've been in Word).
0
 
rvfowler2Author Commented:
OK, really dumb question; I'm sure this is easy.  I tried to create a message box, but keep getting a Type Mismatch error on the last line below.  I'm guessing Response needs to be dimmed as a string, integer, etc., but nothing works.  Following is my code copied directly from VB Help.

    Dim Response
    Message = "Make all changes to generic Setup before it auto saves to a PDF.  Press OK now.  To Resume Macro, Press F5."
    Title = "PAUSE FOR EDITS"
    Response = MsgBox(Message, Title)
0
 
PandaPantsCommented:
Not a dumb question. The MsgBox returns an integer value, so declaring Response as an integer should work. You might be having a problem with the second parameter, though, which is the button value. Try writing it like this, explicitly declaring the name of the parameter:

(Note that I've also explicitly defined all three variables, which you may already have done elsewhere in your code. I added the prefix "i" to the integer and "s" to the strings because I'm anal that way, and in order to keep from mixing up the parameter name "Title" with the variable "Title".)



Dim iResponse As Integer, sMessage As String, sTitle As String

    Message = "Make all changes to generic Setup before it auto saves to a PDF.  Press OK now.  To Resume Macro, Press F5."
    Title = "PAUSE FOR EDITS"
    iResponse = MsgBox(prompt:=sMessage, Title:=sTitle)

Open in new window

0
 
PandaPantsCommented:
Oops, having said that about being anal about variable names, I forgot to modify them in the code I pasted. Sorry. Try this:

Dim iResponse As Integer, sMessage As String, sTitle As String

    sMessage = "Make all changes to generic Setup before it auto saves to a PDF.  Press OK now.  To Resume Macro, Press F5."
    sTitle = "PAUSE FOR EDITS"
    iResponse = MsgBox(prompt:=sMessage, Title:=sTitle)

Open in new window

0
 
rvfowler2Author Commented:
Thanks, it worked, but not sure why.  See comparison of your code with mine below.  Message and Title had already been Defined before but with no "as" statement.  However, I thought that it assumed a string as default.  No?

    Dim iResponse As Integer, sMessage As String, sTitle As String
    sMessage = "Make all changes to generic Setup before it auto saves to a PDF.  Press OK now.  To Resume Macro, Press F5."
    sTitle = "PAUSE FOR EDITS"
    iResponse = MsgBox(prompt:=sMessage, Title:=sTitle)

   
    'Dim Response As Integer
    'Message = "Make all changes to generic Setup before it auto saves to a PDF.  Press OK now.  To Resume Macro, Press F5."
    'Title = "PAUSE FOR EDITS"
    'Response = MsgBox(Message, Title)
0
 
rvfowler2Author Commented:
Regarding using the dimmed dialogue, tried it, but received an error "Object does not support this method" on the Application.Printout section.  If in the set dlg line, I put wdDialog SaveAs instead of FilePrint, it works, but I get the Word SaveAs dialog box come up and then the PDF SaveAs dialog box come up right afterwards with the correct dlg.Name, so we are almost there.  (Remember, creating a PDF is a Print function and not a SaveAs function.)

    ChangeFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\PDFs\"
    Dim dlg As Dialog
    Set dlg = Dialogs(wdDialogFilePrint)
    ActivePrinter = "CutePDF Writer"
    Application.PrintOut OutputFileName:=dlg.Name, Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
       
        dlg.Name = "upc_setup_r" & MyValue & PropValue & ".pdf"
        dlg.Show
0
 
PandaPantsCommented:
Regarding the difference between your code and mine two comments back: The explicit naming of the parameters, rather than the declaration of the variables, was almost definitely the difference. Look closely at the Word Help file, which you've referenced, and you'll see that the second parameter of a MsgBox is not Title, but Buttons:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

As I indicated, I'm pretty anal about using indicators to remind me exactly what I'm trying to do, mainly so I can make sense of my code quickly when I have to come back to it months or years later. However, if you want to reference the Title parameter of a MsgBox without explicitly referencing the parameter name, you can do that by inserting a comma to define the end of each preceding parameter:

Response = MsgBox(Message, , Title)

As you probably know, the square brackets around the parameters indicate that the parameters are optional. However, and this is key, the only unnamed parameters that can be ignored are those that follow the last submitted parameter; in your case, everything up to and including the Title parameter has to be acknowledged, if only by placing a comma to indicate that you're moving on.
Finally, I'll just point out that, if you don't plan to do anything with the return value from the MsgBox, you don't have to use it at all. The following will work just as well, if you don't use the parentheses:
MsgBox Message, , Title
or
MsgBox prompt:=Message, Title:=Title


0
 
PandaPantsCommented:
As to your last question, perhaps we can skip the SaveAs dialog completely? It seems to me that you already know where to save the file and what to call it, and it certainly looks as if the saved filename is appearing correctly in the PrintOut command...or is it? I'm getting confused.

I'd be inclined to try something like this:
Public Sub savetest()
    ' Defining and populating variables for test purposes; you probably already have these handled'
    Dim sFileOpenDirectory As String, sFileName As String, MyValue As Integer, PropValue As String
    sFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\"
    MyValue = 1
    PropValue = "_99"
    ' Here is the real code. Note that I have removed the file extension ".doc", so we can reuse the sFileName value for the PDF:'
    sFileName = "upc_setup_r" & MyValue & PropValue & "test"
    ActiveDocument.SaveAs sFileOpenDirectory & sFileName & ".doc"
    
    ' Finally, we get to the PDF printing part, which seems to be working:'
    ChangeFileOpenDirectory "R:\fmExports\Uploads\UPC_SETUPS\PDFs\"
    ActivePrinter = "CutePDF Writer"
    Application.PrintOut OutputFileName:=sFileName, Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0

End Sub

Open in new window

0
 
rvfowler2Author Commented:
Great stuff, it worked.  However, as for the Word Save As, I want to leave it with the dlg.Show to give me one last chance to think whether I want to save over a finalized doc.  Lastly, is there any way around the Stop command as it pulls up Visual Basic.  Another way to give the user a pause as they finalize the Word doc and before the save to PDF (in case I turn this over to someone else)?
0
 
rvfowler2Author Commented:
Oh, and is there an input box that has two fields and takes two inputs (as in Filemaker)?  I also want to separate out the PDF script as its own self-contained code and need to capture both the rec# and Prop Code.  I also see your code above for adding these two pieces of data to a custom document property -- a great idea, but I think I need it explained in English.  I think I have too many different script projects at once and my brain's not inputting the data.
0
 
PandaPantsCommented:
I know what you mean about the brain processing the data...I'm a bit on overload today, myself. Just in case it helps....

Here's the deal with custom document properties, as used in the code sample earlier in this thread. We want to create a place in the document to keep information that will remain there even after the macros have been run. I like to use custom document properties (CDPs) for this purpose because they don't appear in the document text anywhere (unless you want them to) and so are not prone to accidental erasure.

Creating a CDP is easy, with a simple Add to the CustomDocumentProperties collection:
ActiveDocument.CustomDocumentProperties.Add _
            Name:=sMyCDP, LinkToContent:=False, Value:="upc_setup_r" & MyValue & PropValue & "test.doc", _
            Type:=msoPropertyTypeString

Parameters:
* The Name parameter can be anything (no reason not to hardcode it, if you're only going to be working with the one CDP).
* LinkToContent ties it to a value in the document, which I never do. I set it to False based on a choice I made in the late 90s, but you can probably just ignore it. (I don't remember anymore why I did that.)
* Value is what it says it is.
* Type is where you declare it as a string (you can also choose dates, numbers, etc.).

So, that much is easy. I've run into problems, though, trying to Add a CDP that already exists, so I've gotten into the habit of always checking whether it exists, first. I do this by cycling through all the CDPs in the collection and comparing each name to the one I want to Add (or Update):

    Dim xx As DocumentProperty
    For Each xx In ActiveDocument.CustomDocumentProperties
        If LCase(xx.Name) = LCase(sMyCDP) Then
            bCDPFound = True
        End If ' xx.Name <> CdpName
    Next xx

As before, you can hardcode the name if you prefer (I'm using the string variable sMyCDP in the example). There's also no particular reason to use a boolean to track whether the CDP was found; that's another bit of legacy code that follows me around. To ditch it, you can replace the "bCDPFound = True" statement with the code that sets the value of an existing CDP:

ActiveDocument.CustomDocumentProperties(sMyCDP) = "upc_setup_r" & MyValue & PropValue & "test.doc"

In that case (skipping the boolean), you'd want to put the CDP creation code in the Else side of the If..Then..Else..End If section. Here's the whole thing in that format:

   Dim xx As DocumentProperty
    For Each xx In ActiveDocument.CustomDocumentProperties
        If LCase(xx.Name) = LCase(sMyCDP) Then
            ' Populate the CDP with the current name:
            ActiveDocument.CustomDocumentProperties(sMyCDP) = "upc_setup_r" & MyValue & PropValue & "test.doc"
        Else
           ' Didn't find one, so Add it and populate it
            ActiveDocument.CustomDocumentProperties.Add _
                Name:=sMyCDP, LinkToContent:=False, Value:="upc_setup_r" & MyValue & PropValue & "test.doc", _
                Type:=msoPropertyTypeString
       End If
    Next xx
   

Of course, you can use a variable for the Value parameter, such as sFileName...but you know all that.

The code snippet I've attached is a routine I use to create/add a CDP and give it a value. You'll pass in the CDP name and the value, and it does the rest. Since it refers to yet another routine (CdpExists), I've included that, too. Finally, I'm throwing in the one I use to create a CDP with a numerical value instead of a string (I didn't even remember I had this, so I can't say why I chose Currency).


'--------------
Public Sub CreateAndPopulateOneCDP(ByVal psCdpName As String, ByVal psCdpValue As String)
        If CdpExists(psCdpName) Then
            ActiveDocument.CustomDocumentProperties(psCdpName) = psCdpValue
        Else ' CdpExists(psCdpName) = False
            ActiveDocument.CustomDocumentProperties.Add _
                Name:=psCdpName, LinkToContent:=False, Value:=psCdpValue, _
                Type:=msoPropertyTypeString
        End If ' CdpExists(psCdpName)
End Sub ' CreateAndPopulateOneCDP(ByVal psCdpName As String, ByVal psCdpValue As String)
'--------------
Public Function CdpExists(ByVal CdpName As String) As Boolean
Dim xx As DocumentProperty
    For Each xx In ActiveDocument.CustomDocumentProperties
            If LCase(xx.Name) = LCase(CdpName) Then
                CdpExists = True
                Exit Function
            End If ' xx.Name <> CdpName
    Next xx
    CdpExists = False
End Function
'--------------
Public Sub CreateAndPopulateOneCDP_Number(ByVal psCdpName As String, ByVal pcurCdpValue As Currency)
        If mxcCdpExists(psCdpName) Then
            ActiveDocument.CustomDocumentProperties(psCdpName) = psCdpValue
        Else ' CdpExists(psCdpName) = False
            ActiveDocument.CustomDocumentProperties.Add _
                Name:=psCdpName, LinkToContent:=False, Value:=pcurCdpValue, _
                Type:=msoPropertyTypeNumber
        End If ' CdpExists(psCdpName)
End Sub ' CreateAndPopulateOneCDP_Number(ByVal psCdpName As String, ByVal pcurCdpValue As Currency)
'--------------

Open in new window

0
 
PandaPantsCommented:
One last thing: Watch out for that last routine in the snippet above...I see I have it calling a function called "mxcCdpExists" instead of CdpExists -- I must have done that for disambiguation at some point and forgot to clean it out. If you use it, fix it. Have fun!
0
 
PandaPantsCommented:
Okay, THIS is the last thing (probably). You asked a question I never answered:

"Message and Title had already been Defined before but with no "as" statement.  However, I thought that it assumed a string as default.  No?"

Answer: No. The variables will be defined as Variant by default, which allocates twice as much memory as a String (if I remember correctly, a String requires 8 bytes, a Variant 16 bytes). A Variant can take all types of, well, Types, but if memory is an issue it's to be avoided. Also, explicitly typing your variables can help avoid puzzling Type Mismatch errors, like that one that nailed you when you tried to apply a string value (Title) to an integer parameter (Buttons).

And did I mention I'm a little anal about that sort of thing? ;~)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now