Another quick Excel question (OpenInsight 32-bit) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight 32-bit" author:"Martin Drenovac" author:"Andrew McAuley" author:"Carl Pates" author:"bob carten"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== Another quick Excel question (OpenInsight 32-bit) ====
=== At 06 OCT 2020 04:49:15AM Martin Drenovac wrote: ===
 
 
<QUOTE>
Thanks to the support in last couple of days, i'm "hammering" thru with Ole calls to Excel, etc



I have one new challenge, and i wonder if there's a more subtle solution that my chosen path



eg:



Range("B13:B14").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorDark2

        .TintAndShade = -9.99786370433668E-02

        .PatternTintAndShade = 0

    End



I can select Range

I can select Interior, and then I make OlePutProperty calls for each of the properties



Is there an abbridged means to set more than one property at a time, as per the format above



Cheers, and TIA




</QUOTE>
----

=== At 06 OCT 2020 05:14AM Andrew McAuley wrote:  ===

<QUOTE>You DON'T mean using @Rm delimited arrays in your putProperty call?



[url=https://www.sprezzatura.com]The Sprezzatura Group[/url]

[url=https://www.sprezzatura.com/blog]The Sprezzatura Blog[/url]

[i]World leaders in all things RevSoft[/i]

[img]https://www.sprezzatura.com/zz.gif[/img]

</QUOTE>

----

=== At 06 OCT 2020 05:21AM Martin Drenovac wrote:  ===

<QUOTE>Andrew, seriously that simple



Thank you again.



</QUOTE>

----

=== At 06 OCT 2020 05:26AM Andrew McAuley wrote:  ===

<QUOTE>Makes maintenance easier and improves speed :). I explained it to our Martyn by saying "If I ask for a 12 pack from the fridge, how many journeys are you going to make?". ;)



[url=https://www.sprezzatura.com]The Sprezzatura Group[/url]

[url=https://www.sprezzatura.com/blog]The Sprezzatura Blog[/url]

[i]World leaders in all things RevSoft[/i]

[img]https://www.sprezzatura.com/zz.gif[/img]

</QUOTE>

----

=== At 06 OCT 2020 05:39AM Martin Drenovac wrote:  ===

<QUOTE>Good man, cheers again Andrew



I would do 12 trips as you'd be aware...

</QUOTE>

----

=== At 06 OCT 2020 05:50AM Andrew McAuley wrote:  ===

<QUOTE>Encourages moderation I guess :)





[url=https://www.sprezzatura.com]The Sprezzatura Group[/url]

[url=https://www.sprezzatura.com/blog]The Sprezzatura Blog[/url]

[i]World leaders in all things RevSoft[/i]

[img]https://www.sprezzatura.com/zz.gif[/img]

</QUOTE>

----

=== At 06 OCT 2020 09:33AM Martin Drenovac wrote:  ===

<QUOTE>Andrew, any chance of some more education

i want to insert a .png and having run the macro and i have looked high and low to sort how to exec this

i've battled the Object browser, nothing's coming up that looks familiar





*    Range("I3").Select

*    ActiveSheet.Pictures.Insert("F:\temp\edmen\dcp\dcp-graphic.png").Select

* 	 End Sub



I don't "understand" the ActiveSheet.Pictures.Insert bit - 

i have the excel object of course, and the cell range, however am lost with the rest, esp the 2nd .Select



cheers

</QUOTE>

----

=== At 06 OCT 2020 09:56AM Carl Pates wrote:  ===

<QUOTE>Martin,



When you see a VB/JS statement like that what you usually need to do is break it down for Basic+ (these languages are designed for chained object notation - Basic+ is not)



So try something like this (not tested):


<code>
   objSheet = oleGetProperty( objExcel, "ActiveSheet" )

   objPix = oleGetProperty( objSheet, "Pictures" )

   objPic = oleCallMethod( objPix, "Insert", "F:\temp\edmen\dcp\dcp-graphic.png" )

   selVal = oleCallMethod( objPic, "Select" )


</code>




[email=cpates@revsoft.co.uk]Carl Pates[/email]

</QUOTE>

----

=== At 06 OCT 2020 10:28AM bob carten wrote:  ===

<QUOTE>FWIW, there  are times when translating to individual basic+ OleGetproperty / OlePutproperty / OleCallmethod  becomes too unwieldy.  I will then use the windows scripting control with the Addcode method to create my own OLE object whose methods I can call from Basic+.  This is similar to using <script> tags in a webpage to add functions to the page.



For example, the first snippet below show where the function RTI_HASHTABLE_COM initializes a scripting control and adds a GetKeys method, and the second shows where Basic+ calls the GetKeys method.  Note that all of the varables suffixed with % are in a common, so they persist between calls.  Embedding the scripting host is tricky to debug, but it makes it easier to implement complex vb or javascript code examples in Basic+. 



If you are working with Excel and you persist the handle to the excel object in a common, then you can have OI set/get excel values  real_time.  Finally, you can use VBA plus the OI com interface or a restful api to create spreadsheets which query and update OI realuptime. 




<code>
init:

install:

init% = true$

* Load scripts. Use a local copy if available, else pull from web.

oScript% = OLECreateInstance( 'MSScriptControl.ScriptControl')

oScript%->Language = 'VBScript'

oError% = oScript%->Error

script = ''

script<-1> = ''

script<-1> = 'Function GetKeys(obj,delim)'

script<-1> = 'set Stream = CreateObject("ADODB.Stream")'

script<-1> = 'Stream.type = 2 ' ;* adTypeText'

script<-1> = 'Stream.mode = 3 ' ; * adModeReadWrite

script<-1> = 'Stream.open'

script<-1> = '   i = 0'

script<-1> = '   for each key in obj.Keys'

script<-1> = '       i = i+1'

script<-1> = '       if i > 1 then Stream.writeText delim'

script<-1> = '       stream.WriteText Right(key, len(key)-1)'

script<-1> = '   next'

script<-1> = ''

script<-1> = 'Stream.position = 0'

script<-1> = 'GetKeys = Stream.readText()'

script<-1> = 'Stream.Close'

script<-1> = 'set Stream = nothing'

script<-1> = 'End Function'

script<-1> = ''

script<-1> = 'Function PutItem(obj,key,value)'

script<-1> = ' obj.Item(key) = value'

script<-1> = 'PutItem = ""'

script<-1> = 'End Function'







Swap @fm With crlf$ In script

x = oScript%->AddCode( Script)

oCodeObject% = oScript%->CodeObject

Return


</code>







<code>
Keys:

delim = If Assigned(key) Then key Else ''

If getByteSize(delim) Else delim = @FM

retval = ''

retval = OleCAllMethod(oCodeObject%, 'GetKeys', handle%, delim)

return
</code>


</QUOTE>

----

=== At 06 OCT 2020 10:35AM Martin Drenovac wrote:  ===

<QUOTE>Thank you very much Carl - appreciate it. 



It's just so convoluted, but getting easier once all the  nuances come together



thank you both

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=EC085ED85853B446BCDBCDA8A82B58A6|View this thread on the Works forum...]]