Board index » delphi » OLE automation don't work on localized version of Excel

OLE automation don't work on localized version of Excel

I'm trying using OLE automation to send data to Excel97, The VB-code I send
to the
OLE server seems to work, but when I'm try to modify a property or just try
to acces the Cells value the Excel OLe server returns an error "can't find
the member".
Is this just a problem af localized versions of Excel97? And if so, how does
I solve this problem

e.g. I tried this code.

procedure TForm1.Button1Click(Sender: TObject);
var
  vExcel:Variant;
begin
  try
     vExcel:=GetActiveOleObject('Excel.Application');
  except
     On EOLEerror do
     vExcel:=CreateOLEObject('Excel.Application');
  end;
  try
     vExcel.Visible:=True;
     vXLbook:=vExcel.Workbooks.Add;
// the following statement will not work
     vExcel.ActiveSheet.Cells('1,1').Value:='OLEtest';
  except
     ShowMessage('OLE error');
  end;

jos.zonnev...@wxs.nl

 

Re:OLE automation don't work on localized version of Excel


Quote
: "Jos" <jos.zonnev...@wxs.nl> wrote:
>I'm trying using OLE automation to send data to Excel97, The VB-code I send
>to the
>OLE server seems to work, but when I'm try to modify a property or just try
>to acces the Cells value the Excel OLe server returns an error "can't find
>the member".
>Is this just a problem af localized versions of Excel97? And if so, how does
>I solve this problem

You need to change the current locale of your program/thread to one
Excel does understand. SetLocale(<english>) does it.

--
[Please NO private email - all follow-ups ARE read]
Stefan.Hoffmeis...@Uni-Passau.de
http://kakadu.rz.uni-passau.de/~w4hoff01/

Re:OLE automation don't work on localized version of Excel


Quote
Stefan Hoffmeister writes:

You need to change the current locale of your program/thread to one
Excel does understand. SetLocale(<english>) does it.

Stefan, you've lost me.  How can this work?  Surely, the GetIDsOfNames
interface functions of localized versions of Excel expect localized object
and member names.  A program calling a French Excel GetIDsOfNames with
English names will be rejected.

Jo, in the meantime, get Delphi 3 to derive a Delphi unit from the type
library.  The menu command is Project | Import Type Library.  Press the Add
button and load C:\Program Files\Microsoft Office\Office\Excel8.olb.  The
interfaces bypass the name translation part and make it language
independant.  Or at least I hope it is.

Matthew Greet

Re:OLE automation don't work on localized version of Excel


: "Matthew Greet" <matth...@chemware.co.uk> wrote:

Quote
>Stefan Hoffmeister writes:
>You need to change the current locale of your program/thread to one
>Excel does understand. SetLocale(<english>) does it.

>Stefan, you've lost me.  How can this work?  Surely, the GetIDsOfNames
>interface functions of localized versions of Excel expect localized object
>and member names.  A program calling a French Excel GetIDsOfNames with
>English names will be rejected.

Oh, it does work - but only for D3 - D2.01 is broken in this respect.

There is some magic down the road which I investigated a while back,
so that you will get the locale you want. If I recall right, Delphi
registers itself (main thread) with the system's current (default?)
locale settings (mine are German). Now let's assume that we need to
use late binding and that we really want to be able to talk to all
versions of Excel world-wide (they all speak English VBA-wise). So we
write our (OLE / VBA) source code in English.

When the OLE connection is initiated, Excel learns about the program's
locale settings (in my case by default German), and consequently
expects German VBA commands. To work around this, we simply
(temporarily) change our main thread's locale settings (or do OLE
automation from a separate thread) to English - and Excel suddenly
understands English.

Quote
>Jo, in the meantime, get Delphi 3 to derive a Delphi unit from the type
>library.  The menu command is Project | Import Type Library.  Press the Add
>button and load C:\Program Files\Microsoft Office\Office\Excel8.olb.  The
>interfaces bypass the name translation part and make it language
>independant.  Or at least I hope it is.

Rest assured that EARLY binding is completely (natural) language
independent.
--
[Please NO private email - all follow-ups ARE read]
Stefan.Hoffmeis...@Uni-Passau.de
http://kakadu.rz.uni-passau.de/~w4hoff01/

Re:OLE automation don't work on localized version of Excel


Quote
Stefan Hoffmeister writes:

When the OLE connection is initiated, Excel learns about the program's
locale settings (in my case by default German), and consequently
expects German VBA commands. To work around this, we simply
(temporarily) change our main thread's locale settings (or do OLE
automation from a separate thread) to English - and Excel suddenly
understands English.

Wow!  Nice of Microsoft to not document this.

Nonetheless Jos, use the type library, it's faster.

Matthew Greet

Other Threads