Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Delphi XE5, Excel (2010 e 2013): classe Range



Permlink Replies: 11 - Last Post: Feb 14, 2015 4:38 AM Last Post By: Claude Bourgine
Klaus Edelmann

Posts: 7
Registered: 10/25/06
Delphi XE5, Excel (2010 e 2013): classe Range
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 30, 2014 1:29 AM
Hello!

In my Delphi App I have to control Excel using

AExcel := CreateOleObject('Excel.Application');

Later on I do:

AExcel.Range[ AExcel.Cells[1,6], AExcel.Cells[1,6]].Select;
AExcel.selection.VerticalAlignment := $FFFFEFC0; //xlTop;

Executing this code compiled with Delphi 2010 all works fine.
Having it compiled with Delphi XE5 (Win 32 bit) I get the follwiong error:
"Impossibile impostare la proprietà HorizontalAlignment per la classe
Range."

Same results with Excel 2010 (32 bit) on Windows 8.1 64 bit and on Windows
Server 2008 R2 64 bit and with Excel 2013 (32 bit) on Windows Server 2012R2
64 bit.

Any ideas what's going on?

Thanks in advance!

Klaus
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Delphi XE5, Excel (2010 e 2013): classe Range
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 30, 2014 2:36 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
In the older Delphi's you could mix Office 2000 and Office XP packages in one unit. (uses Excel2000, ExcelXP)
Now you have to choose one of them. It can be that you have to switch from one to the other package.
I modified the XE code to only use Office 2000 package for using mailitem from Outlook2000.

Maybe you have to handle ranges different now..
I handle ranges like:

Uses Excel2000;
 
function ColToText(aCol: integer): string;
var d,m:integer;
begin
  result:='';
  if aCol<=0 then exit;
 
  aCol:=aCol-1;
  d:=aCol div 26;
  m:=aCol mod 26;
  result:=Char(ord('A')+m);
  if d>0 then
  begin
    result:=Char(ord('A')+d-1)+result;
  end;
end;
 
function CellToRange(aRow, aCol: integer): string;
begin
  result:='';
  if aCol<=0 then exit;
  if aRow<=0 then exit;
  result:=ColToText(aCol)+IntToStr(aRow);
end;
 
Procedure MyProcedure(aRow, aCol: integer);
var
  oRng:OLEVariant;
begin
  oSheet.Cells[aRow,aCol]:=Value;
  oRng:=oSheet.Range[CellToRange(aRow,aCol)];
  oRng.Font.Bold:=True;
 
  oRng.Interior.ColorIndex := 15;
  oRng.Interior.Pattern := xlSolid;
end;
Klaus Edelmann

Posts: 7
Registered: 10/25/06
Re: Delphi XE5, Excel (2010 e 2013): classe Range
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 30, 2014 7:00 AM   in response to: Robert Triest in response to: Robert Triest
Thanks Robert, for your answer!

But the situations are different: you seem to use what is called "early
binding". Therefore you have to put an Excel unit in your uses clause. I
don't have that, since I use "late binding" ...
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Delphi XE5, Excel (2010 e 2013): classe Range
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jun 30, 2014 7:46 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
Argh, yes I see now the CreateOleObject declaration..

AExcel.selection.VerticalAlignment := $FFFFEFC0; //xlTop;

I get the follwiong error: "Impossibile impostare la proprietà HorizontalAlignment per la classe

HorizontalAlignment??

Edited by: Robert Triest on Jun 30, 2014 4:47 PM
Klaus Edelmann

Posts: 7
Registered: 10/25/06
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 1, 2014 2:34 AM   in response to: Robert Triest in response to: Robert Triest
OK, Robert you got me, i posted the wrong statement. I shold have put
AExcel.Selection.HorizontalAlignment := $FFFFEFC8; //xlRight
instead in the post.
Sorry!
But the cause of the error remains unclear to me ...

"Robert Triest" ha scritto nel messaggio
news:683396 at forums dot embarcadero dot com...

Argh, yes I see now the CreateOleObject declaration..

AExcel.selection.VerticalAlignment := $FFFFEFC0; //xlTop;

I get the follwiong error: "Impossibile impostare la proprietà
HorizontalAlignment per la classe

HorizontalAlignment??

Edited by: Robert Triest on Jun 30, 2014 4:47 PM
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 1, 2014 4:59 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
Try this..

procedure TForm1.BitBtn1Click(Sender: TObject);
var AExcel,Sheet: OLEVariant;
const xlBottom = -4107;
      xlLeft = -4131;
      xlRight = -4152;
      xlTop = -4160;
      xlHAlignCenter = -4108;
      xlVAlignCenter = -4108;
begin
  AExcel := CreateOleObject('Excel.Application');
  Try
    AExcel.Visible := True;
    //AExcel.DisplayAlerts := False;
    AExcel.Workbooks.Open('C:\MySheet.xlsx');
    Sheet := AExcel.Worksheets.Item['Blad1'];
    //One Cell
    Sheet.Cells[2, 1].HorizontalAlignment := xlRight;
    //Range Cells
    Sheet.Range['A1:D1'].HorizontalAlignment := xlRight;
  Finally
    AExcel.Quit;
    AExcel := Unassigned;
  End;
end;
Klaus Edelmann

Posts: 7
Registered: 10/25/06
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 1, 2014 10:51 AM   in response to: Robert Triest in response to: Robert Triest
Thanks again, Robert. But unfortunately even this code does not work. Same
error message for both of the lines...
Does it work for you? Which Delphi version do you work with? On which
Windows? Which version of Excel is installed?

"Robert Triest" ha scritto nel messaggio
news:683553 at forums dot embarcadero dot com...

Try this..

procedure TForm1.BitBtn1Click(Sender: TObject);
var AExcel,Sheet: OLEVariant;
const xlBottom = -4107;
      xlLeft = -4131;
      xlRight = -4152;
      xlTop = -4160;
      xlHAlignCenter = -4108;
      xlVAlignCenter = -4108;
begin
  AExcel := CreateOleObject('Excel.Application');
  Try
    AExcel.Visible := True;
    //AExcel.DisplayAlerts := False;
    AExcel.Workbooks.Open('C:\MySheet.xlsx');
    Sheet := AExcel.Worksheets.Item['Blad1'];
    //One Cell
    Sheet.Cells[2, 1].HorizontalAlignment := xlRight;
    //Range Cells
    Sheet.Range['A1:D1'].HorizontalAlignment := xlRight;
  Finally
    AExcel.Quit;
    AExcel := Unassigned;
  End;
end;
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 2, 2014 12:48 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
Hi Klaus,

Strange, I use Excel 2010 and XE3 and
also could reproduce your error using your code.

Did you change this line ?
Sheet := AExcel.Worksheets.Item['Blad1'];
"Blad1" is the name of the worksheet tab you can see
at the bottom of the sheet. You need to change this to
you Excel version language.

AExcel.Range[ AExcel.Cells[1,6], AExcel.Cells[1,6]].Select;
AExcel.selection.VerticalAlignment := $FFFFEFC0; //xlTop;
I also don't understand why your error message speaks about
a VerticalAlignment problem on "range" and you use this
property on selection..

Edited by: Robert Triest on Jul 2, 2014 9:50 AM
Klaus Edelmann

Posts: 7
Registered: 10/25/06
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 2, 2014 1:04 AM   in response to: Robert Triest in response to: Robert Triest
Ciao Robert!

Sorry, did I understand you correctly? You can reproduce the error with my code?
How does it work with your code?

My actual testing code is this:
procedure TForm1.Button1Click(Sender: TObject);
var
 AExcel: variant;
 ASheet : OleVariant;
begin
  try
    try
      Aexcel := GetActiveOleObject('Excel.Application');
    except
      Aexcel := CreateOleObject('Excel.Application');
    end;
  except
    ShowMessage('Excel non riesce a partire! Installare Excel o chiudere Excel se aperto.');
    Exit;
  end;
 
  AExcel.visible := true;
 
  AExcel.Workbooks.Add;
 
  ASheet := AExcel.Worksheets.Item['Foglio1']; // Italian localized windows and office ...
  ASheet.Range['A1:D1'].Select;
  AExcel.Selection.HorizontalAlignment := $FFFFEFC8; //xlRight
 
  AExcel := Unassigned;
end;

With this code I still get the error ...

Regards,

Klaus

P.S.: Forgot to mention: just downloaded trial of XE6. Same error :-(

Edited by: Klaus Edelmann on Jul 2, 2014 1:05 AM

Edited by: Klaus Edelmann on Jul 2, 2014 1:10 AM
Klaus Edelmann

Posts: 7
Registered: 10/25/06
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 2, 2014 2:25 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
I've got it!

We have to convert the constant explicitly to integer:
AExcel.Selection.HorizontalAlignment := integer($FFFFEFC8); //xlRight

Now it works!

Thanks for your help and investigation!!

JFTF:
$FFFFEFC8 converts in Delphi 2010 as in XE5 to 4294963144 which is a value of type Cardinal
Assigning this in delphi 2010 to the propoerty AExcel.Selection.HorizontalAlignmen seems to convert the value to the desired value of -4152.
(I found the inspiration for this question here:http://www.pcreview.co.uk/forums/cannot-set-horizontalalignment-property-range-class-t998609.html - the right question was "Does it show -4108").
Interesting is that the following code also in Delphi 2010 always shows "no":
    AExcel.Selection.HorizontalAlignment := $FFFFEFC8; //xlRight
    if AExcel.Selection.HorizontalAlignment = $FFFFEFC8 then
      showmessage('yes')
    else
      ShowMessage('no');
This means that the involved converting routines differ from D2010 to XE5.
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Delphi XE5, Excel (2010 e 2013): classe Range [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 2, 2014 2:34 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
Cool, I just saw the same.

//Error
ASheet.Range['A1:D1'].HorizontalAlignment := $FFFFEFC8;

//OK
const xlRight = -4152;
ASheet.Range['A1:D1'].HorizontalAlignment := xlRight;
Claude Bourgine

Posts: 1
Registered: 5/25/06
Re: Delphi XE5, Excel (2010 e 2013): classe Range
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 14, 2015 4:38 AM   in response to: Klaus Edelmann in response to: Klaus Edelmann
Klaus Edelmann wrote:
Hello!

In my Delphi App I have to control Excel using

AExcel := CreateOleObject('Excel.Application');

Later on I do:

AExcel.Range[ AExcel.Cells[1,6], AExcel.Cells[1,6]].Select;
AExcel.selection.VerticalAlignment := $FFFFEFC0; //xlTop;

Executing this code compiled with Delphi 2010 all works fine.
Having it compiled with Delphi XE5 (Win 32 bit) I get the follwiong error:
"Impossibile impostare la proprietà HorizontalAlignment per la classe
Range."

Same results with Excel 2010 (32 bit) on Windows 8.1 64 bit and on Windows
Server 2008 R2 64 bit and with Excel 2013 (32 bit) on Windows Server 2012R2
64 bit.

Any ideas what's going on?

Thanks in advance!

Klaus
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02