need to code a macro to run an Access db update and store output?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to write code so that I can have a scheduled task run a macro to
perform the following steps:

1. Run DB update
2. Print a file to Adobe PDF Writer
3. Save the Adobe PDF file in a directory sequentially numbered (using the
date in the mmddyyyy format as the file name)
 
BCS,

It is not really clear what you want, I'm afraid. What does "Run DB
update" mean? And when you say "print a file", presumably you are
referring to an Access report?
 
Sorry for the lack of information.

Macro should:

1. Run 3 access reports sequentially.
2. Save each report.
3. Print each report using the Adobe PDF Writer (therefore storing the file
on a shared drive space with a file name similar to
g:\\XXXXfolder\YYYYfolder\reportnamemmddyyyy.pdf)

Does that clear up my question any?
 
BCS,

Because of the variable naming requirement for the PDF files, this will
not be easy with a macro, and you will be better to use a VBA procedure
to do the "work", typing it as a Function rather than a Sub, and then
use a RunCode action in your macro to run the function.

In the design view of each report, go to the File|PageSetup menu, and
select Specific Printer and nominate your PDF driver. Most PDF writers
allow the option to specify a predetermined file name for the PDF output
file. I would assume the Adobe one provides for this. I think the best
approach is to output the PDF for each report, one at a time, and then
rename the file. Something like this...

Public Function PrintPDF()
Dim OutputFileName As String
Dim SaveFileName As String
Dim ReportName As String
OutputFileName = "G:\XXXXfolder\PreDetermined.pdf"
ReportName = "FirstReport"
DoCmd.OpenReport ReportName
SaveFileName = "G:\XXXXfolder\" & ReportName &
Format(Date,"mmddyyyy") & ".pdf"
Name OutputFileName As SaveFileName
ReportName = "SecondReport"
DoCmd.OpenReport ReportName
SaveFileName = "G:\XXXXfolder\" & ReportName &
Format(Date,"mmddyyyy") & ".pdf"
Name OutputFileName As SaveFileName
ReportName = "ThirdReport"
DoCmd.OpenReport ReportName
SaveFileName = "G:\XXXXfolder\" & ReportName &
Format(Date,"mmddyyyy") & ".pdf"
Name OutputFileName As SaveFileName
End Function
 
Back
Top