Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Converting Bulk Number of DBF Reports To Bulk Numbers of PDF Documents 2

Status
Not open for further replies.

Ahmad kaj

Programmer
Apr 29, 2022
11
NG
Hi, Am using this technique to convert on daily basis a bulk number of reports ( clients statements ) extracted from a single DBF table then distributed into different type of PDF documents to be emailed later on to customers. the entire process of accounts distribution is automated with a single click. if you look at my code below you will notice that i am converting the DBF to excel first before saving it as PDF!. my question is there is a more simple way to do the task which is converting directly to PDF without a middle agent ( Excel in this case ) In Between.

< Code >
WAIT WINDOW "Please Wait ... Generating PDF-Files." NOWA

&& Initiate Excel
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
return .F.
ENDIF
oExcel.DisplayAlerts = .F.
oExcel.Application.UserControl=.F.
oExcel.visible = .T.
oWorkbook = oExcel.Application.Workbooks.Add() && Add New Workbook <Book1>
#define xlTypePDF 0 && PDF Extension
&&

SELECT 1 && Pre-Prepared Accounts Table For Customers. Strip Statement Suitable For Mobile Screen View
GO TOP
DO WHILE NOT EOF()

&& Read Data
ACCCNUM = ALLTRIM(ACC_CNUM)
ACCNAME = ALLTRIM(ACC_NAME)
WAIT WINDOW "Generating {" + ACCCNUM + "}-{" + ACCNAME + "}" NOWA
&&

&& Send To Temporal Table
SELECT 2
SET FILTER TO ALLTRIM(AGENT_ID) = ACCCNUM
COPY TO TMPDIR + "EXPORT2.DBF"
&&

&& Transfer To Excel -> PDF
SELECT 3
USE TMPDIR + "EXPORT2.DBF"
IF RECCOUNT() > 0 && Automate
oSheet = oExcel.ActiveSheet
oSheet.Columns("A:A").ColumnWidth=41
oSheet.Columns("B:B").ColumnWidth=26
oSheet.Columns("A:A").Font.Name = "Calibri"
oSheet.Columns("A:A").Font.Size = 24
oSheet.Columns("B:B").Font.Name = "Calibri"
oSheet.Columns("B:B").Font.Size = 24
XCOUNT = 0
GO TOP
DO WHILE NOT EOF()
XCOUNT = XCOUNT + 1
VAR001 = D_ETAILS
VAR002 = A_MOUNT
XCELL1 = "A" + ALLTRIM(STR(XCOUNT))
XCELL2 = "B" + ALLTRIM(STR(XCOUNT))
oExcel.Range(XCELL1).Value = VAR001
oExcel.Range(XCELL2).Value = VAR002
SKIP + 1
ENDDO
FileName1 = "C:\ABC\" + ACCCNUM
oWorkbook.ExportAsFixedFormat(xlTypePDF,FileName1)
ENDIF
USE
&&

&& Clear The Excel Sheet
oExcel.Range("A1:B1500").Clear && Clear The Excel Sheet
&&

SELECT 1
SKIP + 1

ENDDO

oExcel.quit()
oExcel = .Null.
RELEASE oExcel

WAIT WINDOW "Ready." NOWA
CLOSE DATABASES ALL
MESSAGEBOX("PDF Files Successfully Generated.",64,"Message")
RETURN

< Code>







 
Yes, there are definitely easier ways to do this.

The simplest would be to create the reports in VFP, and then output them to PDFs by "printing" them to a PDF printer driver. That would be quick and simple. The main disadvantage is that you (or your users) would need to install a suitable driver, but that is hardly a problem these days. Also, the driver would need to allow for programmatically setting the output file name and path, but most PDF drivers can do this.

But a better approach, in my opinion, would be to do it via FoxyPreviewer. That provides an easy way of outputting a VFP report to PDF, with full programmer control over the destination path and file. XFRX can do the same, but FoxyPreviewer has the advantage of being completely free.

All the above methods can be "automated with a single click". Despite its name, FoxyPreviewer isn't only used for previewing the report. It can generate PDFs without any user involvement, as can XFRX.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
If you want to stick to Excel for any reason, the way to make this much faster is to not automate Exce and set every single cell. you can create a legacy version xls with COPY TO, then open this in the current excel version to use the export-to-PDF feature of Excel. And even in automation you can get data faster over into a sheet by setting an Excel Range of multiple columns and rows to an array that has the same dimensions. And you know it's easy to get from a DBF to an array with an SQL-Select INTO ARRAY.

Bt I agree with Mike the easiest to get PDFs is use a PDF printer.

Chriss
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top