Output GL Transaction Listing to Excel via Macro

[blalor]

We are trying to eliminate the printing of G/L Transaction Listings for over 50 departments. Recording a macro seems the easiest method for this, however when printing the Transaction Listings to a file it requires the end-user to manually specify the path and filename for each report. The macro recording does not seem to pickup the address input during the recording and the code doesn't show it either.

Does anyone have a sample macro that outputs the transaction listing to a specific path and filename?

Thanks

 

[Jay Converse]

Yes, but not to Excel. PDF only.

 

[warrenk1966]

I have never done macros in ACCPAC but for other applications something like this works (you can specify the path to your liking rather than the default of where ACCPAC resides)

SaveAs Filename:=Path & "\" & ".xls"

You may have to play with it a bit; hope it works out for you.

 

[DjangMan]

Go the other way: Pull the transactions from GLPOST into Excel via ODBC.

 

[BillLalor]

Hi Tuba.... do you have an example of the script to output to pdf?

 

[Jay Converse]

Sorry, forget my first answer, I was thinking of regular Crystal reports, not Financial reports.

I just recorded an FR macro, and I see this line:

GLFRRPT.CmdFRPrintEx2 mDBLinkCmpRW, "file", 1, "C:\PROGRAM FILES\ACCPAC\SHARED\USER\ADMIN", "Balsum01_FR.001.xls", 0

That sure looks like a full path and filename to me.

 

[BillLalor]

Yeah, that's where my problem is. From the FR it's pretty simple. This is a GLreport that I'm trying to automate.

 

[DjangMan]

Seriously - create an Excel workbook. Page one is where you enter a FY and FP. The other sheets can then contain queries to each company. Pull in records from GL post using the criteria from page one. It'll be faster and have better formatting.

 

[Jay Converse]

Oh, then you do something like this:

===========================================
Set rpt = ReportSelect("ARDINVO[" & sReportName & "]", " ", " ")
Set rptPrintSetup = GetPrintSetup(" ", " ")
rpt.SetParam "SELECTBY", "1" ' Report parameter: 12
rpt.SetParam "FROMBATCH", Me.fldVendor
rpt.SetParam "TOBATCH", Me.fldVendor
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 " & Me.fldVendor & " to " & Me.fldVendor & " 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

===========================================

rpt.PrintDir is the folder and file name.