Assigning Dynamic file name to report .pdf output? 1

[AlexCuse]

Hi Guys,

I have a 'report' from Access that is generated daily. This 'report' is in fact only a collection of mailing address info from a database, used to print applications that are sent to customers. I have grown tired of being prompted the filename, because it is always the same (sort of).

The best way I can think of to accomplish this is to create a string variable containing (dateadd today- 1) and the rest of the desired filename. Then have my sub open the report in design mode, change the caption to string variable, close design mode, open, and print to my output directory.

I am not so familiar with many of the finer points in Visual Basic, and that makes me think there might be a better way. If there is I would love to know it.

If anyone has other ideas for how I could accomplish this please let me know.

Thanks,
Alex

 

[CautionMP]

AlexCuse,
So you have a report that is set to print to a specific printer (Adobe PDF) and you want to eliminate the enter filename box?

Do you by chance have Acrobat Distiller installed?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[AlexCuse]

Thanks for getting back to me Caution,

1.) yes I want to print to Adobe PDF and avoid seeing the save as dialog box.

2.) Second, I do in fact have Acrobat Distiller v 7.0 installed.

If this helps, my intended filenames will be MMDDYYapp.pdf where MMDDYY = yesterday's date.

It sounds like you've got something cooking here, I'm excited.

Thanks again,

Alex

 

[CautionMP]

AlexCuse,
I wouldn't get excited, we're just going to trick Adobe into doing what we want.
Here is the plan.[ol]
[li]Create a PostScript file from Access (temporary).[/li]
[li]Rename and Move the file to a temporary directory.[/li]
[li]Distill the file.[/li]
[li]Clean up the temporary files.[/li][/ol]

The first step is to create a new local printer on your machine that is setup to print to file.[ul]
[li]Fire up the Add Printer Wizard.[/li]
[li]Select Local Printer attached to this Computer.[/li]
[li]Create a new port: type of port: Local Port.[/li]
[li]Name the port [tt]PSFile[/tt][/li]
[li]Select a PS printer driver, I usually use Canon PS-NX5000.[/li]
[li]Name the printer [tt]PSFile[/tt][/li][/ul]

Now set your report to print to the specific printer you just created. (Now if you print the report you will find a file has been created in [tt]C:\WINDOWS\system32[/tt] called [tt]PSFile[/tt]).

Now here is the code piece to put it all together:

Dim varShell As Variant
Dim strOutputFileName As String
DoCmd.OpenReport "Report1", acViewNormal
'This will cause the macro to wait until the file is created
strOutputFileName = Dir("C:\WINDOWS\system32\PSFile", vbNormal)
'It takes a second or two for the file to be created
While strOutputFileName = ""
  strOutputFileName = Dir
Wend

'Create the new filename, it will be a *.ps since distiller will
'change it to *.PDF when it's created
strOutputFileName = "C:\" & Format$(Date - 1, "MMDDYY") & "app.ps"

'Move and Rename the output file
VBA.FileCopy "C:\WINDOWS\system32\PSFile", strOutputFileName
'Delete the output file
VBA.Kill "C:\WINDOWS\system32\PSFile"
'Create the pdf with distiller, setting varShell to the return pauses
'the macro until Distiller is done
varShell = Shell("c:\Program Files\Adobe\Acrobat 6.0\Distillr\Acrodist.exe /n /q" & strOutputFileName, vbNormalFocus)
'Delete the *.ps file
VBA.Kill strOutputFileName

Or you could just use [tt]SendKeys()[/tt] to send the filename to the popup box

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[AlexCuse]

Gracias

I wanted to use SendKeys(filedate&"app.txt" to the combo box, but for some reason my computer has been choking on SendKeys a lot lately (I do a lot of command line FTP scripting, and more often than not I need to type the command to run my created script manually). I also would really like to make the operation as transparent to the user as possible (so they don't have to think).

I am going to try your way as soon as I get done fixing some problems with reports coming from one of our SQL Server db's. Your method may be a little more code than my design mode thing, but it is one more toy I can learn to use. I'll post when I've got it working.

Thanks,

Alex

 

[AlexCuse]

Well Caution, as usual things get crazy just when I find the time to do some development. Because there are no restrictions on the user for this app (me, my boss and co-worker) I am doing the following to assign the name, and making it print straight to the desired directory through print options on report:

DoCmd.SetWarnings WarningsOff
DoCmd.OpenReport "rpt_applications1a",acDesign
Reports("rpt_applications1a").Caption = fnamedate & "apps1a"
DoCmd.CloseacReport, "rpt_applications1a"
DoCmd.SetWarnings WarningsOn

This works for the particular application, but in the future when I have more time I will certainly want to try your solution for applications where users do not have design rights. Thanks a lot!

Alex

 

[AlexCuse]

Caution-

I got your script to work beautifully, with only a few minor mods (mostly message boxes just to give a pause). However I am having trouble getting the Output file name to the pop up box. I tried using sendkeys for it, and it seems to put me into a never ending loop (which report to print is an if statement based on a message box). Any insight you could offer would be greatly appreciated.

Thanks a lot,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[CautionMP]

AlexCuse,
Distiller dosen't use a pop up box for the filename, it creates a *.pdf file with the same name and in the same folder as the *.ps file.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[AlexCuse]

That is correct, the pop up box I'm talking about is the one for the initial .ps output file. When it is sent to the printer it asks me for output file name. I feel like this piece of your code is supposed to handle this box:

strOutputFileName = Dir("C:\WINDOWS\system32\PSFile", vbNormal)
'It takes a second or two for the file to be created
While strOutputFileName = ""
  strOutputFileName = Dir
Wend

Thanks for all your help so far, and if you have any advice for getting around this box I would love to hear it.

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[CautionMP]

AlexCuse,
Sorry, I misunderstood your last post. What does the popup box say? Is it prompting for a 'Print to File: Filename' or something else?

If the new port/printer was setup correctly to support this process there should be no prompt.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[AlexCuse]

Yeah, it is a Print to File : Output File Name.

The problem may be that I cannot create a local port (it always tells me that a port with that name already exists). I think there is some printer port restriction in place that I can't change... I had set up PSFile through our existing print to file port. IT guy is on vacation for another two weeks, I guess I will have to take this up with him when he's back?

Thanks a lot,

ALex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

Hey Caution,

I'm back, with an actual problem this time rather than just a convenience issue. I am trying to create .pdf's sized for 'Tabloid' paper in Landscape view. I'm not sure if this is a problem with Distiller or with creating the PostScript file. The final .pdf always comes out in letter and portrait. As a result, information is being cut off. I have tried changing settings on the printer itself as well as in Distiller, but it keeps coming out this way. If I set pages in distiller to any kind of "landscape" view, it actually leaves me with no information in the final .pdf file. If I manually create the .pdf it works fine. What do I need to do to get the printer and distiller to recognize the file format I wish to have?

Thanks a lot,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[CautionMP]

AlexCuse,
I would guess it's a problem with the PostScript file. The paper size should be embedded in the PS file but I looked at mine and it's not. Don't know if it's the printer driver or the Windows PS driver.

A couple of thoughts:[ol]
[li]You can change the default paper size in Distiller.[/li]
[li]You could look at using a [tt]joboptions[/tt] file that has the correct setting (you specify the file when you make the Distiller call).[/li][/ol]

When I was doing work with this stuff I was actually making a database talk to a fax server, the PDF part was just a plus, so I worked exclusively with 8.5" x 11" output.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)