Exporting Report to PDF with VBA, second page is blank.

kfraysur

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I used this thread:

MS Access auto/scheduled report export. | MrExcel Message Board

to learn how to schedule a report to be exported to a pdf file once per day. I have the report working, the macro works, and the batch file runs at the appropriate time. I actually set it up as the 'AutoExec' macro for this .accdb file and the vba code closes the database file. This report is the only thing done with this database file.

VBA Code:
Public Function Report_Export()

'Creating two filename variables so they are easier to reference and modify in the future if necessary.
Dim FileNamePDF As String

FileNamePDF = "Filenamegoeshere.pdf" 'This is a network drive link

DoCmd.OutputTo acOutputReport, "CADTech Finishing Detail Report", acFormatPDF, FileNamePDF, False, "", , acExportQualityPrint

Application.Quit

End Function

This works when I run the code directly from the VBA editor. It also works when I run the macro. However, when I simply open the database file (or it's automatically opened by the task scheduler), the second page of the pdf that gets exported looks like this:
PDF Export Error.JPG


If you notice, the page count says '2 of 3'. However, the pdf only has 2 pages.

Any idea why this might happen/what a solution might be? I can run this manually and it works just fine, but the goal was to have this report run from a remote machine automatically.
 

Attachments

  • PDF Export Error.JPG
    PDF Export Error.JPG
    36.1 KB · Views: 2

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
What does the 1st page look like, and are you saying that the report should only be one page? You're saying it runs fine if you call the code somehow, yet also say it doesn't work when you open the db because the Autoexec calls the code?

If you shift bypass the macro when you open the db then execute the macro I gather it doesn't work. That would rule out a profile issue I guess. So whose profile is being used when TS opens the db and could it be a permissions issue?
 

kfraysur

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
What does the 1st page look like, and are you saying that the report should only be one page? You're saying it runs fine if you call the code somehow, yet also say it doesn't work when you open the db because the Autoexec calls the code?

If you shift bypass the macro when you open the db then execute the macro I gather it doesn't work. That would rule out a profile issue I guess. So whose profile is being used when TS opens the db and could it be a permissions issue?
The first page looks fine, it is page 2 where there's an error. In this instance, there should be 3 pages of the report, but only a 2 page pdf was generated. The first page appears as normal, the second appears like the image. There is no 3rd page at all in the pdf.

If I simply call the VBA caode (whether in the VBA editor or manually running the Autoexec macro) the report gets generated exactly as it should be. However, if I simply open the db file and let the Autoexec macro run itself, or if the db is opened due to my task scheduler batch file running, then I get the error I'm describing.

If I shift bypass and run manually, it does work. I'm not sure if you mean Windows profile or Office profile, but they are the same either way. The profile remains the same across all instances where the report generates correctly or incorrectly.

I have this setup in the task scheduler to run every morning at 6:40AM. Strangely, it worked without issue this morning, and generated a 3 page report as it's supposed to. I'm even more confused now, as I changed nothing in the code.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
By profile I meant yours, in case you were not logged in when TS runs this.
I suspect each page is never identical because you've somehow broken up records - likely with page breaks or groups. Since it always prints page 1 all I can think of is that it's data realted but since I have no idea what pages 1 and 3 are supposed to look like, that's just a guess.
 

kfraysur

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This report contains no groupings or page breaks, only some sorting by dates and customer names.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
414
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What if you put a pause or wait at the start of your routine?

Also, where are the data table(s) stored?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

A pause is a decent idea. In that code the only place that makes sense to be is between the Docmd and the Quit.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
414
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
A pause is a decent idea. In that code the only place that makes sense to be is between the Docmd and the Quit.
I was thinking maybe there's a delay in loading the data source and so the export doesn't have all the data available when it runs.

Wouldn't the time between the export and Application.Quit be the same when auto-running and running by hand? The time between the DB loading and report export would change, though, whether running automatically vs having to click around to get it to run...

Right?

I'm genuinely just guessing around.
 
Solution

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
Hmm. Maybe. I was thinking along the lines of a network delay between the two commands, and the db was closing too soon, but I like your thinking better.
 

kfraysur

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I really appreciate the help.

I'm going to go ahead and mark JonXL's post as the solution. It looks like some kind of error was occurring with the report not able to fully load. Once I added a 2 second pause at the beginning of the VBA sequence, it appears to be working as intended under all circumstances, including running from the task scheduler.

I have another set of reports that I was working on automating, and the first report of 5 was showing a similar issue, but again, it was intermittent. I also added the pause to that code and it appears to be working as well.

For reference, this report is pulling data from a linked table from a SQL Server 2019 database.

Again, thanks for the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top