Mimic VBS code

Tonton Luc · Post by **Tonton Luc** » 10 Jan 2018 14:32

Hi,

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

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

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).

Tonton Luc · 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)

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.

Tonton Luc · 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 ?

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).

Tonton Luc · Post by **Tonton Luc** » 15 Jan 2018 8:44

Hi,
All works fine now

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

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.

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).

Tonton Luc · 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)

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.

Tonton Luc · 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))]),

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code

Re: Mimic VBS code