Recent

Author Topic: Insert variant array to excel range  (Read 846 times)

Davidous

  • Full Member
  • ***
  • Posts: 106
Insert variant array to excel range
« on: January 27, 2023, 01:09:00 pm »
Hello,

I'm using Lazarus 2.2.4. x32 bit currently.
Before Lazarus 2.2.0. I used variant arrays to export big ranges of data from excel, and after the neccessary calculations I imported this variant array back to excel with a formula like this:

Excel.Activesheet.range[Excel.Activesheet.Cells[row,column],Excel.Activesheet.Cells[row,column]]:=Variant array;

Before Lazarus 2.2.0. this formula worked, but now I get an error even with 2.2.4.

Any ideas why this can be?

Thanks in advance!

Thaddy

  • Hero Member
  • *****
  • Posts: 12933
Re: Insert variant array to excel range
« Reply #1 on: January 27, 2023, 02:35:05 pm »
What error? That would be very helpful!
In memory of Gordon Moore  (January 3, 1929 – March 24, 2023) Just double the heaven every two years from now.

Davidous

  • Full Member
  • ***
  • Posts: 106
Re: Insert variant array to excel range
« Reply #2 on: January 27, 2023, 02:45:14 pm »
I have the error message in my language, the translation would be "Member not found". Hope it helps. I can also make a small demo program later this afternoon.
« Last Edit: January 27, 2023, 02:55:08 pm by Davidous »

rvk

  • Hero Member
  • *****
  • Posts: 5136
Re: Insert variant array to excel range
« Reply #3 on: January 27, 2023, 03:05:25 pm »
I have the error message in my language, the translation would be "Member not found". Hope it helps.
No, not very much.
You can switch your Lazarus to English (much easier to communicate).
Otherwise copy the EXACT error in your language (EXACT error is very important).
You can right click the error and choose Copy > Copy selected messages to clipboard.

Anyway... the error suggests either .ActiveSheet isn't a member of Excel or .Range or .Cells are not member of .ActiveSheet.

Also... show more code. How did you obtain Excel and .ActiveSheet?
Did you check if they got a valid value?

TRon

  • Hero Member
  • *****
  • Posts: 834
Re: Insert variant array to excel range
« Reply #4 on: January 27, 2023, 03:05:56 pm »
I can also make a small demo program later this afternoon.
You should. And when doing so make sure that every call made to comm has the result returned and checked against.

Member not found simply means that comm is unable to find the property/method in the comm object. Where exactly depends on how far you are able to get. In worse case scenario it might even be that the Excel object is empty (we can't tell from your one-liner).

edit: Ah, crossed post with rvk, see his remarks.

Davidous

  • Full Member
  • ***
  • Posts: 106
Re: Insert variant array to excel range
« Reply #5 on: January 27, 2023, 05:56:34 pm »
Dear TRon, rvk and Thaddy,

thank you for your suggestions. I made my demo program and compiled it with laz 1.8.4. and 2.2.4. In the program I create a variant array of 2*2 and fill it with "1" numbers. After that I (would like to) add this variant array to an excel range.
The program compiled with 1.8.4. works as it should, but the one compiled with 2.2.4. gives an error message.

You can download the exe-s from here:

https://drive.google.com/file/d/1ypN8w70jXuLaPPbu15kZ_iKgWeFj2baj/view?usp=share_link

The code is the following:

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, ComObj,
  Variants;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private

  public

  end;

var
  Form1: TForm1;
  E,arr: Variant;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  E:=CreateOleObject('Excel.Application');
  E.WorkBooks.Add;
  E.Visible:=True;
  arr:=VarArrayCreate([0,1,0,1], VarVariant);
  arr[0,0]:=1;
  arr[1,0]:=1;
  arr[0,1]:=1;
  arr[1,1]:=1;
  E.ActiveSheet.Range(E.ActiveSheet.Cells[1,1],E.ActiveSheet.Cells[2,2]):=arr;
  E:=Unassigned;
end;

end.

Nicole

  • Hero Member
  • *****
  • Posts: 742
Re: Insert variant array to excel range
« Reply #6 on: January 27, 2023, 06:24:32 pm »
If any possible avoid variant. I work a lot with figures. They internally are tricky. The bits differ, how they store figures. So I shred the bytes into bits in some cases. What do I say? I? I got a lot of help for this trick by a genius.

Yes, I use some variant types as well. Sometimes it must be.
Under the line, I prefer to use most common types, if ever possible: integer, string, double,...

And I had an Excel-interface as well. Did it with VBA and OLE and..........  :'( >:D :'( >:D 8-) >:D

The day I gave it up, was a good day.
Why? It was not stable because of the most dangerous element for any software: the user (who may this have been,  :-X).

rvk

  • Hero Member
  • *****
  • Posts: 5136
Re: Insert variant array to excel range
« Reply #7 on: January 27, 2023, 07:25:18 pm »
I made my demo program and compiled it with laz 1.8.4. and 2.2.4.
First of all, you made a fine test program. It crashes here too (although I can't see why on first glance).

Although you normally need to put code between code-tags in your posts (you can use the #-button in the editor for that.

*) I would make E an OleVariant. Not sure if it really matters but I think better.

*) The following line doesn't return an error
Code: Pascal  [Select][+][-]
  1. var Range: OleVariant;
  2. //...
  3.   Range := E.ActiveSheet.Range(E.ActiveSheet.Cells[1, 1], E.ActiveSheet.Cells[2, 2]); // This is Ok
  4.   Range := 'aaaaaaa'; // this fills the array with aaaa's
  5.   // Range.Value := arr; // we don't do this yet
So E, ActiveSheet and Range all work correctly.

*) So the real error is in this line.
Code: Pascal  [Select][+][-]
  1.   Range.Value := arr; // copy data from allocated variant array

I'm not sure why.

I thought Excel array's should be 1-based so you should do:
Code: Pascal  [Select][+][-]
  1.   Arr := VarArrayCreate([1, 2, 1, 2], VarVariant);
  2.   Arr[1, 1] := 1;
  3.   Arr[2, 1] := 1;
  4.   Arr[1, 2] := 1;
  5.   Arr[2, 2] := 1;
But it doesn't fix your crash (at least not here).

I've got this now:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   E, Range: olevariant;
  4.   Arr: variant;
  5. begin
  6.   E := CreateOleObject('Excel.Application');
  7.   E.WorkBooks.Add;
  8.   E.Visible := True;
  9.   Arr := VarArrayCreate([1, 2, 1, 2], VarVariant);
  10.   Arr[1, 1] := 1;
  11.   Arr[2, 1] := 1;
  12.   Arr[1, 2] := 1;
  13.   Arr[2, 2] := 1;
  14.  
  15.   Range := E.ActiveSheet.Range(E.ActiveSheet.Cells[1, 1], E.ActiveSheet.Cells[2, 2]); // this is Ok
  16.   Range.Value := 'aaaaaaa'; // Works ok
  17.   ShowMessage('1');
  18.   Range.Value := Arr; // Crashes
  19.  
  20.   E := Unassigned;
  21. end;

Davidous

  • Full Member
  • ***
  • Posts: 106
Re: Insert variant array to excel range
« Reply #8 on: January 27, 2023, 10:13:32 pm »
Thank you for trying! :) I couldn't figure it out either so far.
About the excel arrays... If you read a certain range from excel into an array then the arrays rows and columns will start with 1, but if you just create an array, then you can start with 0 and read it into excel. Or you could, because it's not working now :(

Davidous

  • Full Member
  • ***
  • Posts: 106
Re: Insert variant array to excel range
« Reply #9 on: January 28, 2023, 04:58:15 pm »
I made one more exploration.
I tried to add a certain item of my variant arrai to an excel cell like this:

E.ActiveSheet.Cells[1, 1]:=arr[1,1];

The result was the same error when I tried to add the whole variant array to an excel range. BUT!

E.ActiveSheet.Cells[1, 1]:=WideString(arr[1,1]); -> this works!!!

I wonder if there is a way to convert the whole variant array to an array of widestrings? Maybe then it could be added to excel too, like it was in the above example.

 

TinyPortal © 2005-2018