macro to print filtered pivotchart to a PDF file

[GaryCoulter]

I have setup a macro using the OenForm and Printout to capture my pivotcharts in a PDF file.

I am trying to find a way to automate this task.

Here are two lines that show what I am trying to do.

DoCmd.OpenForm "Final_Top_PEC_Form",
acFormPivotChart, "", "[id]=1", , acNormal
DoCmd.PrintOut acPrintAll, , , acHigh, 1, True

I would like to know hoiw to create a loop for the [id] going from 1 to 40 and have the printout statement create a unique file for each form.

 

[PHV]

Something like this ?
For i = 1 To 40
DoCmd.OpenForm "Final_Top_PEC_Form", acFormPivotChart, "", "[id]=" & i, , acNormal
DoCmd.PrintOut acPrintAll, , , acHigh, 1, True
Name "\path\to\default.pdf" As "\path\to\output" & i & ".pdf"
Next i

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GaryCoulter]

I am trying to find away to have the macro fill in the filename.ps.

Note: I am trying to have a pivotchart exported in this file.

 

[petman]

I've done this and use it to create over 200 pdf files per day. It's somewhat tempermental at times, but it works.

There are a few steps involved. First, understand a couple of housekeeping rules that make it work:
1: Before I run the program, I need to set the Adobe Distiller printer settings to not send fonts and not view results.
2: I have defined the mapping and file name for the output within the Excel file (as opposed to the VB script) because the script was not translating it properly. I have no idea why.
Here's the script.
After initial setup and formatting, I begin loop processing as follows:
For Each cell In Range(Range("a3"), Range("a" & CompLastRow2))

Range("a3").Value = "=+'agentdashboardcompliance-" & TheDate & ".xls'!$A$" & LoopRow
Range("b3").Value = "=+'agentdashboardcompliance-" & TheDate & ".xls'!$b$" & LoopRow
Range("c3").Value = "=+'agentdashboardcompliance-" & TheDate & ".xls'!$d$" & LoopRow
If Range("a1").Value = True Then

Run "'20040000-AgentDashboardReport.xls'!Sheet2.DashPublisher"
LineCount = LineCount + 1
Else
If Range("c3").Value <> "Total" Then
Range("A3:C3").Copy
Workbooks("_AgentResearchtemplate.xls").Activate
Range("A" & ExceptionRow).PasteSpecial xlPasteValues
ExceptionRow = ExceptionRow + 1
Workbooks("20040000-AgentDashboardReport.xls").Activate

Else
End If
End If


LoopRow = LoopRow + 1
Next

The program that actually produces the file works like this:

Sub DashPublisher()

Dim PSFileName As String, PDFFileName, DistillerCall
Dim ReturnValue As Variant

'Define File Names
Sheets("datasheet").Select
PSFileName = Range("a8").Value
PDFFileName = Range("a9").Value
Sheets("AgentReport").Select

'If the files already exist - end program
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)

'Buffered SEND keys
SendKeys PSFileName & "{ENTER}", False

ActiveSheet.PrintOut , printtofile:=True

'add Double Quotes to file names
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)

DistillerCall = "C:\Program Files\Adobe\Acrobat 5.0\Distillr\acrodist.exe" _
& " /n /q /o" & PDFFileName & " " & PSFileName
'MsgBox DistillerCall
ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName & " Failed"
Sheets("datasheet").Select

End Sub

Good luck. Hope it works for you.

 

[petman]

In case you're wondering, this is where I learned how to create this particular script:

http://www.planetpdf.com/mainpage.asp?webpageid=762

 

[GaryCoulter]

Petman:

Do you know if this solution will work in MS Access?

 

[petman]

Sorry, but I don't know. There's no harm in trying. If it doesn't work, there's a few other options you can try such as exporting data to and from Excel. Have you been to

http://www.mrexcel.com?

It's a great resource.