RemoveDuplicates Method

Question

Has anyone used Excel's RemoveDuplicates method in Progress 4GL? Could you tell me what the syntax is?

Example in VBA:

 ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Here is what I have tried in Progress 4gl:

PROCEDURE pi-elimina-duplicata:

  DEFINE VARIABLE v-chr-excel-application AS COM-HANDLE NO-UNDO. 
  DEFINE VARIABLE v-chr-work-book AS COM-HANDLE NO-UNDO.
  DEFINE VARIABLE v-chr-work-sheet AS COM-HANDLE NO-UNDO.
  DEFINE VARIABLE v-chr-range AS CHARACTER NO-UNDO.
  DEFINE VARIABLE v-int-line AS INTEGER NO-UNDO INITIAL 1.
  DEFINE VARIABLE v-int-ultimalinha AS INTEGER NO-UNDO.
  DEFINE VARIABLE v-coluna-a AS CHARACTER FORMAT "X(20)".
  DEFINE VARIABLE v-coluna-b AS CHARACTER FORMAT "X(20)".
  DEFINE VARIABLE v-coluna-c AS CHARACTER FORMAT "X(20)".

  CREATE 'excel.application':U v-chr-excel-application. 
  v-chr-excel-application:VISIBLE = TRUE.
  ASSIGN
    v-chr-work-book = v-chr-excel-application:Workbooks:OPEN(c-nome-arquivo) v-chr-work-sheet = v-chr-excel-application:Sheets:ITEM(1)
    v-int-ultimalinha = v-chr-excel-application:Activesheet:Usedrange:Rows:COUNT
  .

  /* v-chr-work-sheet:Range("$A$1:$AC$568"):RemoveDuplicates:COLUMNS:Array(1, 2, 3, 4, 5, 6, , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29):HEADER:xlYes. */

  v-chr-work-sheet:UsedRange:RemoveDuplicates("A:AC",1) /* ("1, 2, 3, 4, 5, 6, , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29", YES) */. 

  /* Range(“A1:C8”).RemoveDuplicates Columns:=1, Header:=xlYes */

  v-chr-excel-application:Workbooks:APPLICATION:QUIT.

  RELEASE OBJECT v-chr-excel-application NO-ERROR.
  RELEASE OBJECT v-chr-work-sheet NO-ERROR.
  RELEASE OBJECT v-chr-work-book NO-ERROR.

END PROCEDURE.

Are you using the COM Interface (Active X) or .NET Interop to use Excel from the Progress 4GL? — Mike Fechner, Apr 05 '23 at 04:56
No, I'm manipulating an Excel spreadsheet, with some VBA commands, through a script written in Progress 4GL, but I can't implement this method of removing duplicates, because I don't know the correct syntax for it. — Reginaldo Leandro, Apr 05 '23 at 13:38
Sorry - it's important to know, if your Progress 4GL "scripts" (we usully call it procedures) use the COM intrfrace or .NET. I suggest you share some existing source code to clarify. — Mike Fechner, Apr 05 '23 at 14:37
PROCEDURE pi-elimina-duplicata: DEFINE VARIABLE v-chr-excel-application AS COM-HANDLE NO-UNDO. DEFINE VARIABLE v-chr-work-book AS COM-HANDLE NO-UNDO. DEFINE VARIABLE v-chr-work-sheet AS COM-HANDLE NO-UNDO. DEFINE VARIABLE v-chr-range AS CHARACTER NO-UNDO. DEFINE VARIABLE v-int-line AS INTEGER NO-UNDO INITIAL 1. DEFINE VARIABLE v-int-ultimalinha AS INTEGER NO-UNDO. DEFINE VARIABLE v-coluna-a AS CHARACTER FORMAT "X(20)". DEFINE VARIABLE v-coluna-b AS CHARACTER FORMAT "X(20)". — Reginaldo Leandro, Apr 05 '23 at 15:00
DEFINE VARIABLE v-coluna-c AS CHARACTER FORMAT "X(20)". CREATE 'excel.application':U v-chr-excel-application. v-chr-excel-application:VISIBLE = TRUE. ASSIGN v-chr-work-book = v-chr-excel-application:Workbooks:OPEN(c-nome-arquivo) v-chr-work-sheet = v-chr-excel-application:Sheets:ITEM(1) v-int-ultimalinha = v-chr-excel-application:Activesheet:Usedrange:Rows:COUNT. — Reginaldo Leandro, Apr 05 '23 at 15:01
/*v-chr-work-sheet:Range("$A$1:$AC$568"):RemoveDuplicates:COLUMNS:Array(1, 2, 3, 4, 5, 6, , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29):HEADER:xlYes.*/ v-chr-work-sheet:UsedRange:RemoveDuplicates("A:AC",1) /*("1, 2, 3, 4, 5, 6, , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29", YES)*/. /*Range(“A1:C8”).RemoveDuplicates Columns:=1, Header:=xlYes*/ v-chr-excel-application:Workbooks:APPLICATION:QUIT. RELEASE OBJECT v-chr-excel-application NO-ERROR. — Reginaldo Leandro, Apr 05 '23 at 15:02
RELEASE OBJECT v-chr-work-sheet NO-ERROR. RELEASE OBJECT v-chr-work-book NO-ERROR. END PROCEDURE. — Reginaldo Leandro, Apr 05 '23 at 15:02

score 0 · Answer 1 · answered Apr 05 '23 at 20:35

The correct syntax should be:

DEFINE VARIABLE columnsArray AS INTEGER EXTENT 2 NO-UNDO.  
      
columnsArray[1] = 1 .
columnsArray[2] = 2 .
      
v-chr-work-sheet:Range("A1:A5"):RemoveDuplicates(columnsArray, 1 /* XlYesNoGuess:xlYes */ ) .

For the array, you pass in an ABL array and the header constant needs to be replaced by a single integer value: https://learn.microsoft.com/en-us/office/vba/api/excel.xlyesnoguess

However the code errors, but maybe this helps you get further.

Error occurred while accessing component property/method: RemoveDuplicates. Ausnahmefehler aufgetreten.

Error code: 0x80020009 test.p (5890)

RemoveDuplicates Method

1 Answers1