AR Invoices to PDF 1 page at a time 1

[WalterHeisenberg]

Hello,

Our issue is we're trying to see if it is possible to print an AR Invoice batch to PDF but rather than create 1 PDF with X number of invoices we'd like it to create 1 PDF for each invoice in the batch. Has anyone managed to do something along these lines?

I'm guessing we might be able to do this with the API as ideally not only would we like 1 file per invoice but also to rename the pdf to the Invoice number.

We're trying to do this as every month we have a recurring batch of 1k plus invoices. These invoices are currently being extracted and renamed one at time to our internal file share. Any feedback would be most welcome.

We're running Accpac version 5.6, PU2, AR/AP/GL on MS-SQL Server 2008. Thanks,

-A

 

[Jay Converse]

Here's a code snippet from a form that has the user select the batches to print, then the AR Invoice spec, and outputs PDFs to the selected folder:


If UCase(fso.GetExtensionName(Me.txtInputFileName)) <> "RPT" Then
MsgBox "That is not a valid Crystal report file", , APPHEADER
Exit Sub
End If

If Not fso.FileExists(Me.txtInputFileName) Then
MsgBox "Crystal report file does not exist", , APPHEADER
Exit Sub
End If

sReportName = fso.GetFileName(Me.txtInputFileName)

CSCompany.Read

ARBatch.Cancel
ARBatch.Browse "CNTBTCH >= " & Me.fldBatchFrom & " and CNTBTCH <= " & Me.fldBatchTo, True

Do While ARBatch.Fetch
ARBatchHeader.Cancel
ARBatchHeader.Browse "CNTBTCH = " & ARBatch.Fields("cntbtch"), True
Me.fldBatchFrom.DataSource.Read

iRecordCount = 0
Do While ARBatchHeader.Fetch

iRecordCount = iRecordCount + 1
iTotalCount = iTotalCount + 1
Me.lblStatus = "Processing batch " & ARBatchHeader.Fields("cntbtch") & ", entry " & iRecordCount & " of " & ARBatch.Fields("CNTINVCENT") & ", please wait..."
DoEvents

Set rpt = ReportSelect("ARDINVO[" & Me.txtInputFileName & "]", " ", " ")
Set rptPrintSetup = GetPrintSetup(" ", " ")
rpt.SetParam "SELECTBY", "1" ' Report parameter: 12
rpt.SetParam "FROMBATCH", Me.fldBatchFrom
rpt.SetParam "TOBATCH", Me.fldBatchFrom
rpt.SetParam "FROMENTRY", ARBatchHeader.Fields("CNTITEM")
rpt.SetParam "TOENTRY", ARBatchHeader.Fields("CNTITEM")
rpt.SetParam "FROMCUST", " " ' Report parameter: 0
rpt.SetParam "TOCUST", "ZZZZZZZZZZZZ" ' Report parameter: 0
rpt.SetParam "FROMDOCNBR", " " ' Report parameter: 0
rpt.SetParam "TODOCNBR", "ZZZZZZZZZZZZZZZZZZZZZZ" ' Report parameter: 0
rpt.SetParam "FCURNDEC", "2" ' Report parameter: 0
rpt.SetParam "ADDR01", CSCompany.Fields("ADDR01")
rpt.SetParam "ADDR02", CSCompany.Fields("ADDR02")
rpt.SetParam "ADDR03", CSCompany.Fields("ADDR03")
rpt.SetParam "ADDR04", CSCompany.Fields("ADDR04")
rpt.SetParam "CITY", CSCompany.Fields("CITY")
rpt.SetParam "STATE", CSCompany.Fields("STATE")
rpt.SetParam "POSTAL", CSCompany.Fields("POSTAL")
rpt.SetParam "@SELECTION_CRITERIA", "{ARIBH.CNTBTCH} in " & ARBatchHeader.Fields("cntbtch") & " to " & ARBatchHeader.Fields("cntbtch") & " and {ARIBH.CNTITEM} in " & ARBatchHeader.Fields("CNTITEM") & " to " & ARBatchHeader.Fields("CNTITEM")
rpt.NumOfCopies = 1
rpt.Destination = PD_FILE
rpt.Format = PF_PDF
rpt.PrintDir = Me.lblExportFolder & ARBatchHeader.Fields("IDINVC") & ".PDF"
rpt.PrintReport
Set rptPrintSetup = Nothing
Set rpt = Nothing

Loop ' Invoices in the batch
Loop ' Batches selected

 

[WalterHeisenberg]

Sorry I'm just getting around to work on this. First, thanks for your help! I wouldn't have gotten far without it.

So I created a Macro and using your post I was able to create a form that successfully accomplishes what I set out to do. However, I exported that form and tried to load it into a basic excel file as part of a bigger project. When I try to execute the code I get an error, User-defined type not defined when it gets to the following line:

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink

. I am assuming once I'm not running this from Accpac's created project I'm losing access to those object/methods. Is there a way I can regain that access?

Sorry I'm new to Accpac VBA so forgive me. Thanks in advance.

 

[Jay Converse]

You need to add the COMAPI to your Project.

 

[WalterHeisenberg]

Thanks for your help Tuba. I got it to work in Excel. Last piece I need is to get it to work in VB.

I'll start that on Monday. I'm assuming it should be the same as Excel more or less?

 

[Jay Converse]

Yes, VB and VBA are close siblings.

 

[WalterHeisenberg]

Thanks for all your help Tuba. I just have one last issue to solve.

I have the following line to try to grab the value of ARIBH.IDINVC:

 rpt.PrintDir = "\\ACCPAC\USER\ADMIN\" & ARBATCHFields.FieldByName("IDINVC").ToString() & ".pdf"

I also have the following:

        Dim ARBATCH As AccpacCOMAPI.AccpacView
        Dim ARBATCHFields As AccpacCOMAPI.AccpacViewFields

        mDBLinkCmpRW.OpenView("AR0032", ARBATCH)
        ARBATCHFields = ARBATCH.Fields

The PDFs are being called, "System._ComObject.pdf". I also tried to use FieldByIndex and got the same result. Any idea what I'm doing wrong? Thanks in advance,

-A

 

[Jay Converse]

What's the issue?

 

[WalterHeisenberg]

The invoices should be called the invoice number, I.E. IN01. This was working in VBA with the code above not sure why it is failing in VB.

 

[WalterHeisenberg]

Right now those invoices are being consolidated as part of a single pdf named, System._comObject.pdf.

 

[Jay Converse]

Use ARBATCHFields.Fields("IDINVC") instead of ARBATCHFields.FieldByName("IDINVC").ToString()

I've never used "ToString" before in VB, I thought it was a Java function.

 

[DjangMan]

ToString is also used in .net languages.

 

[WalterHeisenberg]

This is part of my problem. Sorry I wasn't specific before. I'm doing this is VB.net using 4.0 framework. ARBATCH fields does not have a fields property or function I can invoke although it works perfectly in Excel. The closest thing I see is what I mentioned earlier and both are returning the object type rather than the field value.

I've only worked with C# before but in reading the forums it seemed like VB was the better choice with Accpac.

 

[WalterHeisenberg]

I figured it out. Thanks Tuba and Djang for your help. In the event it helps anyone else, the problem was solved as follows:

 ARBATCHFields.FieldByName("IDINVC").Value.ToString()