How do I do this code?

sp4tc · 2008-11-06

I am trying to export from the GL Budget Entry table using the Excel buffer so I wrote this

GL1 := "G/L Budget Entry"."G/L Account No.";
IF GL1 = '101000' THEN
RS1 := "G/L Budget Entry".Amount
ELSE
CurrReport.SKIP;

This works for one and exports fine my problem here is I have many GL accounts to do this for (see below) and I want one in each column going across the page

GL codes;

101000
102000
103000
104000
105000
106000
107000
108000
109000
110000
111000
112000
113000
114000
115000
116000
117000
118000
119000
120000
121000
122000
123000
124000
125000
126000
127000

Can anyone help?

Thanks in advance :shock:

Belias · 2008-11-06

GL1 := "G/L Budget Entry"."G/L Account No.";
if intaccounttoskip = 0 then  
  INTAccountToSkip := 101000;
IF GL1 = format(intaccounttoskip) THEN begin
  intaccounttoskip += 1000;
  if intaccounttoskip = 127000 then begin
    //CURRReport.break?
  end;
  RS1 := "G/L Budget Entry".Amount
end ELSE
  CurrReport.SKIP;

sp4tc · 2008-11-06

What if there are other GL codes I want e.g. 200001, 200005, 200008?

Regards

Belias · 2008-11-06

You must enter them manually in the filter...
with my previous post, i solved ONLY your problems, with ONLY the accounts you posted :whistle:

garak · 2008-11-06

why not using a setup table where you store the GL Accounts to ignore / to use.

If GLIgnoreTable.get(GLAccNo) then //<- to ignore, to use -> if  not GLIgnoreTable.get(GLAccNo) then CurrReport.skip
  CurrReport.skip;

Regards

sp4tc · 2008-11-06

sorry, I am lost.

I have setup a table and put the GL codes in but I am unsure of how to use your code.

I want a column per GL account in the excel output

Can you help please?

Savatage · 2008-11-06

how about store that account #'s using an array

keep the Row number the same & keep incrementing the column no.

Savatage · 2008-11-06

Ok Try THis .. it's an example on how to put the account codes going across ---> then you can add what yo need to add to it. It exports it to excel.

OBJECT Report 50092 GL Account No Export
{
  OBJECT-PROPERTIES
  {
    Date=11/06/02;
    Time=12:57:27 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    TopMargin=1000;
    BottomMargin=1000;
    LeftMargin=1000;
    ProcessingOnly=Yes;
    Orientation=Portrait;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table15;
        OnPreDataItem=BEGIN
                        Window.OPEN(
                          text001 +
                          '@1@@@@@@@@@@@@@@@@@@@@@\');
                        Window.UPDATE(1,0);
                        TotalRecNo := "G/L Account".COUNTAPPROX;
                        RecNo :=0;

                        TempExcelBuffer.DELETEALL;

                        CLEAR(TempExcelBuffer);
                      END;

        OnAfterGetRecord=BEGIN
                           Row := 1;
                           Column := Column +1;
                           EnterCell(Row, Column, "G/L Account"."No.", FALSE, FALSE, FALSE);

                           RecNo := RecNo + 1;
                           Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
                         END;

        OnPostDataItem=BEGIN
                         Window.CLOSE;

                         TempExcelBuffer.CreateBook;
                         TempExcelBuffer.CreateSheet(text000,text000,COMPANYNAME,USERID);
                         TempExcelBuffer.GiveUserControl;
                       END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      TempExcelBuffer@1100500000 : TEMPORARY Record 370;
      Window@1100500005 : Dialog;
      Row@1100500001 : Integer;
      TotalRecNo@1100500003 : Integer;
      RecNo@1100500004 : Integer;
      PORow@1102606008 : Integer;
      xlSheet@1102606009 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020820-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Worksheet";
      Item@1000000000 : Record 27;
      Column@1000000003 : Integer;
      text001@1102606000 : TextConst 'ENU=Account No';
      text000@1102606001 : TextConst 'ENU=GL Account';

    LOCAL PROCEDURE EnterCell@2(RowNo@1000 : Integer;ColumnNo@1001 : Integer;CellValue@1002 : Text[250];Bold@1003 : Boolean;Italic@1004 : Boolean;UnderLine@1005 : Boolean) : Text[250];
    BEGIN
      TempExcelBuffer.INIT;
      TempExcelBuffer.VALIDATE("Row No.",RowNo);
      TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
      TempExcelBuffer."Cell Value as Text" := CellValue;
      TempExcelBuffer.Formula := '';
      TempExcelBuffer.Bold := Bold;
      TempExcelBuffer.Italic := Italic;
      TempExcelBuffer.Underline := UnderLine;
      TempExcelBuffer.INSERT;
    END;

    EVENT xlSheet@1102606009::SelectionChange@1543(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range");
    BEGIN
    END;

    EVENT xlSheet@1102606009::BeforeDoubleClick@1537(Target@1102606001 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range";VAR Cancel@1102606000 : Boolean);
    BEGIN
    END;

    EVENT xlSheet@1102606009::BeforeRightClick@1534(Target@1102606001 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range";VAR Cancel@1102606000 : Boolean);
    BEGIN
    END;

    EVENT xlSheet@1102606009::Activate@304();
    BEGIN
    END;

    EVENT xlSheet@1102606009::Deactivate@1530();
    BEGIN
    END;

    EVENT xlSheet@1102606009::Calculate@279();
    BEGIN
    END;

    EVENT xlSheet@1102606009::Change@1545(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Range");
    BEGIN
    END;

    EVENT xlSheet@1102606009::FollowHyperlink@1470(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00024431-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.Hyperlink");
    BEGIN
    END;

    EVENT xlSheet@1102606009::PivotTableUpdate@2156(Target@1102606000 : Automation "{00020813-0000-0000-C000-000000000046} 1.5:{00020872-0000-0000-C000-000000000046}:'Microsoft Excel 11.0 Object Library'.PivotTable");
    BEGIN
    END;

    BEGIN
    END.
  }
}

Hopefully this is what you were talking about..let me know if you need fob instead.
We don't use the table you speak of so I couldn't make a working example.

costas · 2008-11-07

If you do not want to use a table the something like this will do the job

If Not GL1 In ['101000','102000','103000','104000','105000',
                    '106000','107000','108000','109000','110000',
                    '111000','112000','113000','114000','115000',
                    '116000','117000','118000','119000','120000',
                    '121000','122000','123000','124000','125000','126000','127000'] Then
  CurrReport.SKIP;

Belias · 2008-11-07

costas wrote:

If you do not want to use a table the something like this will do the job

If Not GL1 In ['101000','102000','103000','104000','105000',
                    '106000','107000','108000','109000','110000',
                    '111000','112000','113000','114000','115000',
                    '116000','117000','118000','119000','120000',
                    '121000','122000','123000','124000','125000','126000','127000'] Then
  CurrReport.SKIP;

which is the same thing i wrote before...

but a table is definitively better if the process is not a "one shot" thing

How do I do this code?

Comments

Categories