Mimic VBS code

Discussions related to Visual Prolog
Post Reply
User avatar
Tonton Luc
VIP Member
Posts: 800
Joined: 16 Oct 2001 23:01

Mimic VBS code

Post by Tonton Luc » 10 Jan 2018 14:32

Hi,

Is it possible to do the same in VP 7.3 ? :

Code: Select all

  Dim ApplicationExcel   Dim ClasseurExcel   Dim NumCol     Set ApplicationExcel = GetObject(,"Excel.Application")   Set ClasseurExcel = ApplicationExcel.ActiveWorkbook     NumCol = ClasseurExcel.Application.ActiveCell.Column

User avatar
Thomas Linder Puls
VIP Member
Posts: 2298
Joined: 28 Feb 2000 0:01

Re: Mimic VBS code

Post by Thomas Linder Puls » 12 Jan 2018 9:42

In Vip 8 it could look like this (I have simplified the VB code slightly):

Code: Select all

clauses     run() :-         %  Set ApplicationExcel = GetObject("Excel.Application")         CLSID = com_api::cLSIDFromProgID("Excel.Application"),         IApplicationExcell = uncheckedConvert(iDispatch, comCreation::createInstance(CLSID, iDispatch::iid)),         stdio::writef("%\n", IApplicationExcell),         ApplicationExcell = comDispInterface::new(IApplicationExcell),         %         %  Set ClasseurExcel = ApplicationExcel.ActiveWorkbook         comDomains::iDispatch(IClasseurExcel) == ApplicationExcell:getProperty("ActiveWorkbook"),         stdio::writef("%\n", IClasseurExcel),         ClasseurExcel = comDispInterface::new(IClasseurExcel),         %         %  ActiveCell = ApplicationExcell.ActiveCell         comDomains::iDispatch(IActiveCell) == ApplicationExcell:getProperty("ActiveCell"),         stdio::writef("%\n", IActiveCell),         ActiveCell = comDispInterface::new(IActiveCell),         %         %  NumCol = ActiveCell.Column         comDomains::integer(NumCol) == ActiveCell:getProperty("Column"),         stdio::writef("%\n", NumCol).
However, it does not work as intended, because I get null-interfaces for the ActiveWorkbook and ActiveCell. And then I (obviously) get an exception when I ask about the "Column".

(Since the code does not work I do not know whether "Column" is an integer, or something else).
Regards Thomas Linder Puls
Prolog Development Center

User avatar
Tonton Luc
VIP Member
Posts: 800
Joined: 16 Oct 2001 23:01

Re: Mimic VBS code

Post by Tonton Luc » 12 Jan 2018 11:49

Hi,

Many thanks for your help.
I forget to indicate I use VP 7.3.
So, I try to convert your code in VP 7.3 and I've an error.
Here is my code :

Code: Select all

        _R = com_native::cLSIDFromProgID("Excel.Application",CLSID),         IApplicationExcell = uncheckedConvert(iDispatch, comCreation::createInstance(CLSID, iDispatch::iid)),
Exception: systemException (com/visual-prolog/exception/runtime_exception)

System exception

error code = 1
ExtraInfo = Exception C0000005: Access violation

raised() 2018/01/12 11:47:00
ThreadId=5664
ClassInfo: com/visual-prolog/exception/runtime_exception $JustDate: 2010-01-05 $$Revision: 21 $


C:\Windows\syswow64\ole32.dll (0x76929EF8)
d:\1travail\Prolog_7_3\Test_excel\Exe\VIP7Kernel.dll (0x14009950)
C:\Windows\SysWOW64\ntdll.dll (0x771534C1)
C:\Windows\SysWOW64\ntdll.dll (0x77153493)
C:\Windows\SysWOW64\ntdll.dll (0x77100163)
C:\Windows\syswow64\ole32.dll (0x76929C2F)
c:\program files (x86)\visual prolog 7.3\pfc\com\comsupport\comcreation.pro(27)

User avatar
Thomas Linder Puls
VIP Member
Posts: 2298
Joined: 28 Feb 2000 0:01

Re: Mimic VBS code

Post by Thomas Linder Puls » 12 Jan 2018 12:12

You did indicate that you use Vip 7.3 and that was why I wrote "In Vip 8 ..."; it will be your own job to downgrade the code.

The problem with your code is that com_native::cLSIDFromProgID must be called with the address of a piece of memory into which the CLSID is copied.

In Vip 8 the code looks like this (but the memory allocation it a bit more difficult in Vip 7.3):

Code: Select all

clauses     cLSIDFromProgID(ProgID) = CLSID :-         CLSID = memory::alloc_atomic(),         check2(com_native::cLSIDFromProgID(ProgID, CLSID)).
In Vip 7.3 you will have to use memory::allocAtomicHeap and some unchecked conversion.
Regards Thomas Linder Puls
Prolog Development Center

User avatar
Tonton Luc
VIP Member
Posts: 800
Joined: 16 Oct 2001 23:01

Re: Mimic VBS code

Post by Tonton Luc » 12 Jan 2018 13:24

Hi

Sorry, I did not see that you had indicated "Vip 8" in your first reply.

I've try this code without success :

Code: Select all

        XXX = uncheckedConvert(comDomains::nativeClassId,memory::allocAtomicHeap(sizeOfDomain(comDomains::nativeClassId))),         CLSID = uncheckedConvert(pointer,XXX),         R_clsid = com_native::cLSIDFromProgID("Excel.Application",CLSID), % not ok here         IApplicationExcell = uncheckedConvert(iDispatch, comCreation::createInstance(CLSID, iDispatch::iid)),
What's wrong ?

User avatar
Thomas Linder Puls
VIP Member
Posts: 2298
Joined: 28 Feb 2000 0:01

Re: Mimic VBS code

Post by Thomas Linder Puls » 14 Jan 2018 18:57

memory::allocAtomicHeap returns a pointer, which you convert to a comDomains::nativeClassId, so far so good.

Why convert it back to a pointer again?

(I have not checked vip 7.3 but it may be the case that com_native::cLSIDFromProgID is declared to have the guid as out parameter. It that is the case then it is wrong and you will have to create another declaration where it is in).
Regards Thomas Linder Puls
Prolog Development Center

User avatar
Tonton Luc
VIP Member
Posts: 800
Joined: 16 Oct 2001 23:01

Re: Mimic VBS code

Post by Tonton Luc » 15 Jan 2018 8:44

Hi,
All works fine now :D
Many thanks for your help.
Here is my code :

Code: Select all

class predicates     getActiveObject : (comDomains::nativeClassId CLSID, pointer MustBeNull, iUnknown IUnknown [out]) -> hResult HResult language apicall.   clauses     onFileNew(_Source, _MenuTag):-         CLSID = uncheckedConvert(comDomains::nativeClassId,memory::allocAtomicHeap(sizeOfDomain(comDomains::nativeClassId))),         _R_clsid = com_native::cLSIDFromProgID("Excel.Application",CLSID),         _RR = getActiveObject(CLSID, null, IUnk),         if memory::isNull(IUnk) then             stdio::write("No active Excel application\n")         else             Unk = comInterface::new(IUnk, comMemory::release),             IDisp = uncheckedConvert(iDispatch, Unk:queryInterface(iDispatch::iid)),             ApplicationExcell = comDispInterface::new(IDisp, comMemory::release),             comDomains::string(Name) == ApplicationExcell:getProperty("Name"),             stdio::writef("%\n", Name),             comDomains::iDispatch(IActiveCell) == ApplicationExcell:getProperty("ActiveCell"),             stdio::writef("%\n", IActiveCell),             ActiveCell = comDispInterface::new(IActiveCell),             comDomains::integer(NumCol) == ActiveCell:getProperty("Column"),             stdio::writef("%\n", NumCol),             comDomains::integer(NumRow) == ActiveCell:getProperty("Row"),             stdio::writef("%\n", NumRow),             Value == ActiveCell:getProperty("Value"),             stdio::writef("%\n", Value),             comDomains::iDispatch(ISelection) == ApplicationExcell:getProperty("Selection"),             stdio::writef("ISelection = %\n", ISelection),             Selection = comDispInterface::new(ISelection),             Address == Selection:getProperty("Address"),             stdio::writef("%\n", Address),             Outputs2 = Selection:invokeMethodWithOutput("Next",[]), % Next or Previous             stdio::writef("%\n", Outputs2),             comDomains::iDispatch(IFind) == list::nth(0,Outputs2),             CellSel = comDispInterface::new(IFind),             CellSel:invokeMethod("Select",[])         end if,         !.
Now, I would like to select a cell, but don't find the solution. Here is the VBS code :
ClasseurExcel.Application.Range("B6").Select
I've tried "Selection:setProperty("Address",comDomains::string("$B$6"))," without success.
Any :idea:
Last edited by Tonton Luc on 15 Jan 2018 10:17, edited 1 time in total.

User avatar
Thomas Linder Puls
VIP Member
Posts: 2298
Joined: 28 Feb 2000 0:01

Re: Mimic VBS code

Post by Thomas Linder Puls » 15 Jan 2018 9:30

Unfortunately, our comDispInterface does not support indexed properties (neither in Vip 7.3 or in Vip 8).

The principle is described in Indexed Properties.

It is very much like a function call, but it must use one of the constants:
  • com_native::dispatch_propertyGet,
  • com_native::dispatch_propertyPut or
  • com_native::dispatch_propertyPutRef.
Regards Thomas Linder Puls
Prolog Development Center

User avatar
Thomas Linder Puls
VIP Member
Posts: 2298
Joined: 28 Feb 2000 0:01

Re: Mimic VBS code

Post by Thomas Linder Puls » 15 Jan 2018 9:47

That was slightly wrong. Vip 8 does have a predicate for getting an property which is indexed by a number:

Code: Select all

predicates     getIndexedProperty : (string Name, integer Index) -> variant Output.     % @short Get the value of the indexed property identified by #Name/#DispId and #Index.     % @end
But:
  • It is not available in Vip 7.3
  • There is no corresponding set-predicate
  • The index must be a number (yours is a string).
Regards Thomas Linder Puls
Prolog Development Center

User avatar
Tonton Luc
VIP Member
Posts: 800
Joined: 16 Oct 2001 23:01

Re: Mimic VBS code

Post by Tonton Luc » 15 Jan 2018 14:36

I've tried this code without succes :

Code: Select all

            Outputs2 = ApplicationExcell:invokeMethodWithOutput("Range",[comdomains::in(comDomains::string("B6"))]),
========================================
Dump: 2018/01/15 14:34:56
----------------------------------------
Exception: disp_e_membernotfound_exception (com/visual-prolog/com/exceptionHandling/exceptionHandling_exception)

Member not found

HRESULT code = 2147614723
HRESULT hex code = 80020003
hresultDescription = Member not found
Predicate name = invoke
Source of the exception =
ErrorDescription =
Native Error Code = 0
Native Scode = 0

raised() 2018/01/15 14:34:53
ThreadId=7908
ClassInfo: com/visual-prolog/com/componentUsage/comDispInterface $JustDate: 2010-03-09 $$Revision: 19 $


c:\program files (x86)\visual prolog 7.3\pfc\exception\exception.pro(180)
d:\1travail\Prolog_7_3\Test_excel\Exe\VIP7Kernel.dll (0x14009864)
c:\program files (x86)\visual prolog 7.3\pfc\exception\exception.pro(180)
c:\program files (x86)\visual prolog 7.3\pfc\com\exceptionhandling\comexceptioncheck.pro(415)
c:\program files (x86)\visual prolog 7.3\pfc\com\exceptionhandling\comexceptioncheck.pro(355)
c:\program files (x86)\visual prolog 7.3\pfc\com\exceptionhandling\comexceptioncheck.pro(344)
c:\program files (x86)\visual prolog 7.3\pfc\com\exceptionhandling\comexceptioncheck.pro(335)
c:\program files (x86)\visual prolog 7.3\pfc\com\comsupport\comdispinterface.pro(108)
c:\program files (x86)\visual prolog 7.3\pfc\com\comsupport\comdispinterface.pro(144)
d:\1travail\prolog_7_3\test_excel\taskwindow\taskwindow.pro(229)
:?:

User avatar
Thomas Linder Puls
VIP Member
Posts: 2298
Joined: 28 Feb 2000 0:01

Re: Mimic VBS code

Post by Thomas Linder Puls » 15 Jan 2018 14:38

Range is an indexed property see Application.Range Property (Excel).

The code for getting such one this resembles the code for invokeMethodWithOutput, but instead of using com_native::dispatch_method you must use com_native::dispatch_propertyGet.
Regards Thomas Linder Puls
Prolog Development Center

User avatar
Tonton Luc
VIP Member
Posts: 800
Joined: 16 Oct 2001 23:01

Re: Mimic VBS code

Post by Tonton Luc » 15 Jan 2018 16:21

Bingo !!! Works fine. Many many thanks for your help Thomas.

Code: Select all

            Outputs2 = ApplicationExcell:invokeMethodWithOutput2("Range",[comdomains::in(comDomains::string("B6:E7"))]),             stdio::writef("%\n", Outputs2),             comDomains::iDispatch(IFind) == list::nth(0,Outputs2),             CellSel = comDispInterface::new(IFind),             CellSel:invokeMethod("Select",[])
in comDispInterface.pro :

Code: Select all

clauses     invokeMethodWithOutput2(MethodName, Params2) = OutList :-         Params = removeOptionalTail(Params2),         NParams = list::length(Params),         Size = NParams*sizeOfDomain(comDomains::comVariant),         ParamsArray = allocOrNull(Size),         AfterLast = memory::pointerAdd(ParamsArray, Size),         reverseFillArray_dispParameter(AfterLast, Params),         ComVariant = variant::allocateComVariant(),         DispParams = comDomains::comDispParams(ParamsArray, null, convert(unsigned, NParams), 0),         invoke(MethodName, com_native::dispatch_propertyGet, DispParams, ComVariant),         Variant = variant::toVariantAndClear(ComVariant, comMemory::release()),         if comDomains::empty() = Variant then Tail = [] else Tail = [Variant] end if,         OutList = getReverseOutputParams(AfterLast, Params, Tail),         dispose(uncheckedConvert(comDomains::comVariant, ParamsArray), NParams).
in comDispInterfaceExport.pro :

Code: Select all

clauses     invokeMethodWithOutput2(MethodName, ParamList) = OutList :-         mkArray_dispParameter(ParamList, ParamArray, Count),         DispParams = comDomains::comDispParams(uncheckedConvert(pointer, ParamArray), ::null, Count, 0),         ComVariant = variant::allocateComVariant(),         try             invoke(MethodName, com_native::dispatch_method, DispParams, ComVariant),             Variant = variant::toVariantAndClear(ComVariant, comMemory::keep()), % better to release it in the finally section             OutList = getOutputParams(ParamArray, ParamList, [Variant])         finally             comVariant_clear(ParamArray, Count),             comMemory::taskMemFree(ComVariant)         end try.
in comDispInterface.i :

Code: Select all

predicates     invokeMethodWithOutput2 : (string MethodName, comDomains::dispParameter* Params) -> comDomains::variant* OutValues.
...and this code works fine too :

Code: Select all

            Outputs2 = ApplicationExcell:invokeMethodWithOutput2("Cells",[comdomains::in(comDomains::integer(2)),comdomains::in(comDomains::integer(2))]),
:-)

Post Reply