Export Data to Excel Format using codes

duffmoh · 2004-03-19

Hi All,

Greetings. I found these codings in the Application Designer's Guide, Chapter 16.2. page 402.

CREATE(xlApp);
xlBook := xlApp.Workbooks.Add(-4167);
xlSheet := xlApp.ActiveSheet;
xlSheet.Name := 'Personnel Expenses';

xlSheet.Range('A2').Value := 'Administration';
xlSheet.Range('A3').Value := "G/L Entry".Amount;

I've tried on these codes and it'll only export one record.
Just wondering, is there any other way to export all the records in the table into excel format without using dataport? and using the similar way as mentioned above?

By the way, I'm using 3.01a.

Thanks.
Philip

janpieter · 2004-03-19

Hello,

Loop through the records as usual and make a variable 'counter' (type = integer) and add one to the counter and dynamicaly set the row in excel:

xlSheet.Range('A1').Value := 'Administration';
IF "G/L Entry".FIND('-') THEN BEGIN
REPEAT
Counter := Counter + 1;
xlSheet.Range('B' + FORMAT(Counter)).Value := "G/L Entry".Amount;
UNTIL "G/L Entry".Next := 0;
END;

I did not test it but with this code youre close it think

eromein · 2004-03-19

right Jan Peter,

I don't want to bitch about your code, but I thinks you could improve it a bit by using a "INCSTR" instead of an integer variable.

Cell[1] := 'A1';
Cell[2] := 'B1';

xlSheet.Range(Cell[1]).Value := 'Entry No.'; // Just a sample
xlSheet.Range(Cell[2]).Value := 'Amount';

IF "G/L Entry".FIND('-') THEN
  REPEAT
    FOR i := 1 to ARRAYLEN(Cell) DO
      Cell[i] := INCSTR(Cell);
    
    xlSheet.Range(Cell[1]).Value := "G/L Entry"."Entry No.";
    xlSheet.Range(Cell[2]).Value := "G/L Entry".Amount;
  UNTIL "G/L Entry".Next := 0;

* Code haven't been tested.

janpieter · 2004-03-19

Sorry Emil, i think you should review your code, there are alot of mistakes in it wich are confusing.

- you are doing INCSTR(Cell) and Cell is an array?
- for every record you fill the array with the same data? Can be done before the repeat.
- you are always filling the same cells (A1 and B1)?
- INCSTR is a more lengthy routine then format because it has to search for te start of the number, convert it to integer, increase it and then convert it back to text. (ok who will notice on todays machines :? )

I don't think it is an improvent.

Don't make easy things difficult.

duffmoh · 2004-03-22

Hi JanPieter,

Thanks for your help!! Your codings works

janpieter · 2004-03-22

Youre welcome.

... if your goal is to create a chart in excel you might want to have a look at Embedded Controls ...

Export Data to Excel Format using codes

Comments

Categories