?
Solved

Output filename control for Adobe PDFWriter

Posted on 2003-03-19
4
Medium Priority
?
1,316 Views
Last Modified: 2007-12-19
Here's the situation:
(Platform: Win2K, Apps: Word 97, Excel 97, Acrobat 5.05)

I've got a group of users updating data in an excel spreadsheet.
I also have a group (3 to be exact) of word docs that access the spreadsheet as a data source for a mail merge.
Embeded in each page is a series of keywords.
What I want to achieve is a VBA macro that prints each individual page through the Adobe PDFWriter to a pre-determined filename (determined by one of the keywords) and a path (determined by the rest of the keywords and variables defined in the VBA code) without requiring user intevention.

An example path might be: c:\fab_sys\FABS\AFabs\cat1\subcat1 with a file name: unita.pdf
Another would be : c:\fab_sys\FABS\AFabs\cat2\subcat3 with a file name of: resourceF.pdf

I've gotten 99% of the way, but so far each solution I reach for presents me with a brick wall at the last.

I have previously tried a few things, including:
* moving data from a single page to a new doc and then printing that through pdfwriter. This failed because the word doc has images on it which get cut off (I can't work out how to control the section break, and it is automatically placed after the final text on a page, however the graphics appears at the very bottom of the page so they get lost)

* generating a .reg file containing pdfwriter reg entry required to pass pdf output file path and name then using shell to merge the .reg before firing off the print function. This failed for reasons I can only guess at (I have a few languages under my belt, but VB/VBA is not one of them), maybe the .reg merge doesn't have time to close the file before the next page attempts to print? I get an error saying "Cannot import c:\temp.reg: error opening file. There may be a disk or file system error". The flow is this: (1)generate file path and output file name, storing concatenated result in a variable. (2)Create .reg file with this information in it. (3)initiate print job of current page. (4)delete .reg file. (5) return to <1> until all pages printed.

*I also stumbled on something called ExtEscape. The Adobe API documentation mentions this as being a function able to set the output file name prior to the initialisation of each print job. But I sadly can't work out how to use it from within VBA. I've declared the function so it's available, but beyond that I can honestly say I don't know what I'm doing.

(I have developed a system of printing each page to a .ps file, storing in appropriate paths and calling a recursive batch file that then enters each directory in the tree and converts any .ps file it finds - however I'd like to use PDFWriter as it produces smaller cleaner .pdf's and it would involve less external mechanics to achieve. I also need to keep it simple so that the system may be used and maintained by those less technically minded)

Any suggestions would be GREATLY appreciated as this has been causing me grief for a couple of weeks now.
If I had more points to give I would - I'm thoroughly desperate!

[I've edited this question to add the code snippet here illustrating what I've most recently tried]
[For illustrative purposes assume regout$ == "C:\\fab_sys\\Fabs\\AFabs\\unita.pdf"]
First the path (regout$) is created, then iPDFPrint() is called. This is repeated until each page is processed.
 
Sub iPDFPrint()
'Macro to spawn individual pages to PDFWriter
'delete any residual/rogue file.
shell ("cmd /c del c:\winnt\temp\pdfreg.reg")
'create the pdfreg.reg file.
setregpdfw
'store current printer
oldprinter$ = Dialogs(wdDialogFilePrintSetup).Printer
'set active printer to pdfwriter
ActivePrinter = "Acrobat PDFWriter"
'print the page
Application.PrintOut FileName:="", _
                     Range:=wdPrintCurrentPage, _
                     Item:=wdPrintDocumentContent, _
                     copies:=1, _
                     Pages:="", _
                     pagetype:=wdPrintAllPages, _
                     collate:=False, _
                     Background:=False, _
                     printtofile:=False
ActiveDocument.Saved = True
'restore old default printer
ActivePrinter = oldprinter$
'clear pdfreg.reg file
shell ("cmd /c del c:\winnt\temp\pdfreg.reg")
'produce pdfreg.reg file that removes the registry value
clearregpdfw
End Sub

Sub setregpdfw()
'Macro to create pdfreg.reg that sets the registry value
    Open "C:\winnt\temp\pdfreg.reg" For Output As #1
        Print #1, "REGEDIT4"
        Print #1, ""
        Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]"
        Print #1, Chr(34) + "PDFFileName" + Chr(34) + "=" + Chr(34) + regout$ + Chr(34)
    Close #1
    'Set the registry value
    shell ("cmd /c regedit -s c:\winnt\temp\pdfreg.reg")
End Sub

Sub clearregpdfw()
'Macro to create pdfreg.reg that clears registry value
    Open "c:\winnt\temp\pdfreg.reg" For Output As #1
        Print #1, "REGEDIT4"
        Print #1, ""
        Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]"
        Print #1, Chr(34) + "PDFFileName" + Chr(34) + "=-"
    Close #1
    'Clear the registry value specified
    shell ("cmd /c regedit -s c:\winnt\temp\pdfreg.reg")
End Sub
0
Comment
Question by:silvercloak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:silvercloak
ID: 8171267
I'll add my own comment here - I've just run the macro on a small test spreadsheet and I can now note that it is outputting the files correctly, however it produces the error message box for each one, and launches Acrobat opening each file - one big fat mess.

So I guess the question is not so much how to output to individual files, as how to do it 'well' so that it doesn't produce erroneous error messages
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8171312
Well, i don't know if it causes the error but W2K uses Regedit 5
Also, use & instead of + when you concatenate string variables.
0
 

Author Comment

by:silvercloak
ID: 8171571
Well, I'll get this question removed, as I've produced a solution for it myself.
However in my research into ways to get this done, I have seen hundreds of 'pointers' to how to do it, but nothing that I could see which just said 'do this, it works'. So it would be good to get this as a PAQ as I can say 'do this, it works'.

Here is the code I am using to print a file from VBA without requiring user intervention to supply a filename.
(ie: PDFWriter with no Save-As dialogue box).
Variables (set anywhere you like, so long as these functions will have access to them. I have routines set that generate FABPath and RegPath based on keywords embeded in the document, so each page is different):

TempPDF$ = "c:\winnt\temp\temppdf.pdf"
FABPath$ = "c:\fab_sys\fabs\test.pdf"
'' the double '\' is needed for registry imports.
RegPath$ = "c:\\fab_sys\\fabs\\test.pdf"

Sub iPDFPrint()
    '
    ' iPDFPrint Macro
    ' Macro created 19/03/03 by Paul G Jungwirth
    '
    '' Delete residual temporary file if exists.
    If Dir(TempPDF$) <> "" Then Kill TempPDF$
    '' This is a check to loop the operation until the
    '' PDFWriter has completed it's job.
    On Error GoTo printNotFinished
    '' Set the registry entry calling PDFFileNameSet
    PDFFileNameSet
    '' Save the current printer device to restore later.
    oldprinter$ = Dialogs(wdDialogFilePrintSetup).Printer
    '' Set the current printer to PDFWriter
    ActivePrinter = "Acrobat PDFWriter"
    '' Print the current page. You could change this to print
    '' all pages, a range of pages, as you require.
    Application.PrintOut FileName:="", _
                     Range:=wdPrintCurrentPage, _
                     Item:=wdPrintDocumentContent, _
                     copies:=1, _
                     Pages:="", _
                     pagetype:=wdPrintAllPages, _
                     collate:=False, _
                     Background:=False, _
                     printtofile:=False
    ActiveDocument.Saved = True
    '' If PDFWriter has finished printing the file then
    '' FABPath$ will be able to be renamed. If not it branches
    '' because of the error. This simply tests that the file
    '' has been created, and only moves on if it has.
    Name FABPath$ As TempPDF$
    Name TempPDF$ As FABPath$
    '' Clear the registry entry calling PDFFileNameClear
    PDFFileNameClear
    '' Reset the active printer to the previous state.
    ActivePrinter = oldprinter$
Exit Sub
'' Here's the error loop
printNotFinished:
    Select Case Err.Number
        Case 13, 75, 53
            Err.Clear
            DoEvents
            Resume
    End Select
End Sub

Sub PDFFileNameSet()
    ' This function creates a reg file to import the PDFFileName
    ' registry value into our registry.
    ' This value, if it exists, suppresses the 'save-as' dialogue box
    ' and forces PDFWriter to out put to the file specified in the value.
    '' Open the file to create our temporary .reg file.
    Open "C:\winnt\temp\pdfreg.reg" For Output As #1
        Print #1, "REGEDIT4"
        Print #1, ""
        Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]"
        Print #1, Chr(34) & "PDFFileName" & Chr(34) & "=" & Chr(34) & RegPath$ & Chr(34)
    Close #1
    '' Import the registry file, the '-s' forces regedit to do it quietly.
    '' The /c argument to cmd forces it to close when execution complete.
    shell ("cmd /c regedit -s c:\winnt\temp\pdfreg.reg")
End Sub

Sub PDFFileNameClear()
    ' This function creates a reg file that will REMOVE the PDFFileName
    ' registry value, hence returning PDFWriter to it's default state
    ' of displaying a 'save-as' dialogue box.
    '' Open the file to create our temporary .reg file.
    Open "c:\winnt\temp\pdfreg.reg" For Output As #1
        Print #1, "REGEDIT4"
        Print #1, ""
        Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]"
        '' The "=-" when executed by Regedit will actually remove the PDFFileName value
        Print #1, Chr(34) & "PDFFileName" & Chr(34) & "=-"
    Close #1
    '' Import the registry file, the '-s' forces regedit to do it quietly.
    '' The /c argument to cmd forces it to close when execution complete.
    shell ("cmd /c regedit -s c:\winnt\temp\pdfreg.reg")
End Sub


The problem I had that caused this not to work was two fold. Firstly, somehow the registry entry for bExecViewer in PDFWriter had become set true - this was causing acrobat to launch on each print, I reset it to 0 and all ok.
Secondly though it was a timing problem - the function was catching up to itself so quickly that it was trying to delete the reg file while it was still using it (as best I can guess). Anyway, with my knowledge of windows and vb/vba programming so slim I was trying to clear out the files so they didn't keep writing into themselves - but I didn't need to do it. As each open as just starts the file again.
So I deleted the shell("cmd /c del.....") statements I was using. Instead I inserted a 'name' statement in that attempts to rename (or move) the file that PDFWriter is creating. Name can't do that until PDFWriter has finished with it, so it just loops until PDFWriter is finished, then once the test is passed moves on from the function.

If anyone else out there is looking for ways to automate filename output from PDFWriter I hope this is of some assistance to them.
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8173796
PAQ'd and all 75 points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

777 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