Help automating Excel printout to PDF from within FoxPro

[N8RL8R]

I am using an OLE container control to automate an Excel spreadsheet. We create and manipulate data in Excel in our quoting process.

I am trying to pull up the finished cover letter, create a pdf file and email it back to the creator so it can be forwarded to the customer. Everything works great if the filename is allowed to be entered manually but when it is automated (either through FoxPro or as a macro from within Excel) the resulting pdf file created cannot be opened in Acrobat Reader. I get an error that reads "Adobe Reader could not open "filename.pdf" because it is either not a supported file type or because the file has been corrupted (for example, it was sent as an email attachment and wasn't correctly decoded)."

Here's the jist of the code to get the file into excel and prepare to print. I then use CutePDF as the print driver for output which is saved to the local c: drive.

loExcel = CreateObject("Excel.Application")
loExcel.DisplayAlerts = .F.
loExcel.Workbooks.Open(lcPathName2)
loExcel.Range("B1:I77").Select

WITH loExcel.ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.TopMargin = 0
.HeaderMargin = 0
.PrintArea="B1:I77"
.PrintHeadings = .F.
ENDWITH

*loExcel.Run("nateprint1") && this is what I tried to use to run the printing through a macro located in the excel file

loExcel.ActiveSheet.PrintOut(1,1,1,.F.,"CutePDF Printer",.T.,.T.,"C:\"+ALLTRIM(mquotenum)+".pdf") && this is how I would like to be able to enter the filename automatically without user intervention.

*loExcel.ActiveSheet.PrintOut(1,1,1,.F.,"CutePDF Printer",.F.,.T.) && this is the line I've used that works but requires the user to enter the file name in the dialog box that displays after printing.

I've tried different combinations to no avail. I would appreciate any guidance on this.

Thanks in advance,
Nate

 

[Mike Gagnon]

My understanding of the problem, it seems to be related to the printer driver rather than VFP. Or perhaps an incompatibility between VFP and the printer driver. Have you contacted the supplier of the printer driver?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[TamarGranor]

Are you sure that:

"C:\"+ALLTRIM(mquotenum)+".pdf"

gives you a valid file name in a location where you have rights? I suggest building the filename in a separate command and storing it to a variable. (I also suggest using the FORCEPATH() and FORCEEXT() functions, so you don't have to worry about getting all the punctuation right.) Then, check what result you get.

Tamar

 

[N8RL8R]

Here's what I altered:

mFileName = FORCEEXT(FORCEPATH(lcPathName,"C:\"),".pdf")

loExcel.ActiveSheet.PrintOut(1,1,1,.F.,"CutePDF Printer",.T.,.T.,mFileName)

This returned the same error when trying to open in Adobe. I believe the error is related to the printer driver as Mike suggested above. CutePDF must create a binary file regardless of what extension is given(?) when ran through the printout command in VFP. I've tried both .T. and .F. in the sixth parameter slot and both return the same. What still puzzles me is that when this is allowed to be entered manually the file created is useable. There is one other difference to make note of, the manually created file is around 106KB in size whereas the automated one is 115KB in size. Something is happening to the creation when the name is force entered.

Alas I'm still looking for a plausible solution.
Thanks greatly for the suggestions!
Nate

 

[TamarGranor]

Did you confirm that cFileName is what you think it is and that you have the appropriate permissions to create that file?

Tamar

 

[JRB-Bldr]

Assuming that the CutePDF "printer" works for you in other (non-VFP) Windows applications, there is another approach that you could consider. It is somewhat more complex, but I use it to "print" VFP Reports to JPG files.

Following the excellent examples provided by Mike Gagnon in
faq184-4140 and faq184-2977 ....
You can have your application utilize Windows Scripting to set the workstation Default printer to CutePDF, then execute your Excel automation including the print (with no unique printer specification), and then set the workstation Default printer back to its original setting.

Something like....

* --- Determine Existing Default Windows Printer ---
lcDefaultPrinter =  SET("PRINTER",2)

* --- Use APRINTERS() To Determine What Printers Available To Workstation ---
=APRINTERS(gaPrinters)

IF ASCAN(gaPrinters,"CUTEPDF PRINTER") > 0
  * --- Use Windows Scripting To Set Default Printer To New PDF Printer ---
  lcNewPrinter = "CutePDF Printer"
  oNET = CREATEOBJECT("WScript.Network")
  oNET.SetDefaultPrinter(lcNewPrinter)

  SET PRINTER TO NAME (lcNewPrinter)

  < do whatever through Excel Automation >
  < no need to specify different, non-default, printer >

  * --- Return to original Default Printer ---
  oNET.SetDefaultPrinter(lcDefaultPrinter)
  RELEASE oNET

  SET PRINTER TO NAME (lcDefaultPrinter)
ENDIF  && IF ASCAN(gaPrinters,"CUTEPDF PRINTER") > 0

Good Luck,
JRB-Bldr

 

[danceman]

try this

loExcel.ActiveSheet.PrintOut(1,1,1,.F.,"CutePDF Printer",.T.,.T.,"C:\"+ALLTRIM(mquotenum))

thinking that your print driver will automatically add the pdf. mine does

Attitude is Everything