COMate - control COM objects via automation - OBSOLETE!

[srod]

Thanx srod!

It works - now i can make my own Plugins.

Mike

In which case you should be able to switch :

Code: Select all

Plot(j,i ,RGB(*startR\pvData\bVal[rIndex],0,0)) 

for

Code: Select all

Plot(j,i ,RGB(SA_BYTE(*startR, rIndex),0,0)) 

Looks a bit tidier!

[Kiffi]

Hello,

here is a simple example to implement a spellchecker (MS-Word must be installed):

Code: Select all

IncludePath #PB_Compiler_Home + "comate\"
XIncludeFile "COMate.pbi"

Procedure.s SpellChecker(Input.s)

; inspired by
; http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=843&lngWId=1

  Protected oWA.COMateObject
  Protected ReturnString.s
  oWA = COMate_CreateObject("Word.Application")
  With oWA
    \Invoke("WordBasic\AppMinimize")
    \Invoke("WordBasic\FileNewDefault")
    \Invoke("WordBasic\EditSelectAll")
    \Invoke("WordBasic\EditCut")
    \Invoke("WordBasic\Insert('" + ReplaceString(Input, "'", "$0027") + "')")
    \Invoke("WordBasic\StartOfDocument")
    \Invoke("WordBasic\ToolsSpelling")
    \Invoke("WordBasic\EditSelectAll")
    ReturnString = \GetStringProperty("WordBasic\Selection")
    If Right(ReturnString, 1) = #CR$
      ReturnString = Left(ReturnString, Len(ReturnString) - 1)
    EndIf
    \Invoke("Quit(0)")
    \Release()
  EndWith
  ProcedureReturn ReturnString
EndProcedure

StringToCheck.s = InputRequester("", "", "Hello Worlt")
If StringToCheck
  MessageRequester("", SpellChecker(StringToCheck))
EndIf

Greetings ... Kiffi

[leodh]

Hi srod,

I have had a play with Comate and it has made a difficult task very easy for me.
I use it to check a pile of Excel Worksheets for a Sample ID.

Here is my humble Code

Code: Select all

;<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 
;
; EXCEL Sample Find ( Finds Sample ID in Worksheet Files )
;
; Version 0.1
;
; Program By Leo 
;
; Programmed in PureBasic Version 4.20
;
; Last Updated 9/12/2008
;
;  Comate by Stephen Rodriguez 
;
;  nxSoftware
; 
;  http://www.nxSoftware.com
;
;<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

IncludePath "C:\PureBasic\Demo code\Comate\" 
XIncludeFile "COMate.pbi" 

Global Find$ = ""

OpenWindow(0, 0, 0,820,600," EXCEL Sample Find ",#PB_Window_SystemMenu|#PB_Window_ScreenCentered)
   
   CreateGadgetList(WindowID(0))
   
   ListIconGadget(1, 10, 10,295,580,"File Name",200,#PB_ListIcon_GridLines)
   ButtonGadget  (2,315, 10,100, 20," Find String ")
   StringGadget  (3,420, 10,170, 20,"")
   ListIconGadget(5,315, 70,495,520,"File Name",200,#PB_ListIcon_GridLines)
   StringGadget  (6,315, 40,495, 20,"Remember - this search routine is case sensitive and will only find an 'exact' match to the Find String.")
   TextGadget    (7,625, 10,150, 20," Default Excel Search Column",#PB_Text_Border)
   StringGadget  (8,780, 10, 30, 20,"B")
   
   AddGadgetColumn(1, 2,"Date"     , 74)
   AddGadgetColumn(5, 2,"Case No." ,100)
   AddGadgetColumn(5, 3,"Sample ID",135)
   AddGadgetColumn(5, 4,"Row"      , 38)
      
If ExamineDirectory(0,"C:\ELISA\Upload\","Worksheet*.xls")
   While NextDirectoryEntry(0)
      FileName$ = DirectoryEntryName(0)
      If DirectoryEntryType(0) = 1 ; Directory type
         FileDate = DirectoryEntryDate(0,#PB_Date_Modified)
         AddGadgetItem(1, -1, FileName$ + Chr(10) + FormatDate("%dd/%mm/%yyyy", FileDate))
      EndIf
   Wend
Else
   MessageRequester("Error","Can't examine this directory: C:\ELISA\Upload\",0)
EndIf

Repeat
   Event = WaitWindowEvent()
     
      Select Event
     
         Case #PB_Event_Gadget
         
            Select EventGadget()
               
               Case 2
               ClearGadgetItemList(5) 

               Gosub CheckFile
               
            EndSelect 
          
      EndSelect
   
Until Event = #PB_Event_CloseWindow
End

CheckFile:
Find$ = GetGadgetText(3)
For Files = 0 To CountGadgetItems(1)
   File$ = GetGadgetItemText(1,Files)
   CDate$ = GetGadgetItemText(1,Files,1)
   SetGadgetItemColor(1,Files,#PB_Gadget_BackColor,RGB(100,220,240),0)
   Lookin$ = "Workbooks\Open('C:\Elisa\Upload\"+File$+"')"
   Define.COMateObject ExcelObject, WorkBook 
   ExcelObject = COMate_CreateObject("Excel.Application") 
   ;ExcelObject\SetProperty("Visible = #True") 
   WorkBook = ExcelObject\GetObjectProperty(Lookin$) 
   If ExcelObject 
   t = 1  
   Repeat
      Cell$ = ExcelObject\GetStringProperty("Cells(" + Str(t) + ",2)")           ;    Cells(Row,Column)
      position = FindString(Cell$,Find$,1)
      If position > 0 
         Cell1$ = ExcelObject\GetStringProperty("Cells(" + Str(t) + ",1)") 
         AddGadgetItem(5,-1,File$ + Chr(10)+Cell1$ + Chr(10) + Cell$ + Chr(10) + Str(t) + Chr(10) + "B")
      EndIf
      t = t + 1
   Until Cell$ = ""
   ExcelObject\Invoke("Quit()") 
   ExcelObject\Release() 
   EndIf
Next Files
Return  

Its not that flash but it works, but I have a few questions ?

How do I deal with a requester that pops up if a different version of Excel was used to create the file.
" Do you want to save the changes to ------- ? Yes No Cancel "

I want to be able to see the progress of the search, highlight each file as it has been checked. I can not get that to work am I doing something silly or is it due to Comate?

Are there any ways of speeding up the search ( I gather most of the time is used up opening and closing Excel )

Regardless, Comate is great and I will be doing a lot more with it in the future.

Thanks

[srod]

Well, without time to run this program properly I cannot answer your questions regarding the hightlighting, though it is very unlikely to be anything to do with COMate, more likely your loop needs some message-processing thrown in via a delay and a WindowEvent() etc.

The reason it is so slow is that you keep closing and re-opening the Excel application instance!!! Why not open the one instance before the loop starts and then open/and close the relevant workbook objects within the loop as required? Release the application object after the loop is done.

How do I deal with a requester that pops up if a different version of Excel was used to create the file.
" Do you want to save the changes to ------- ? Yes No Cancel "

I don't think you can. Except...

Considering what you are doing, why not take a completely different approach and connect to the Excel worksheets using either ODBC or OLE-DB (you can use ADOmate for this latter option). These options would not require Excel to be present on your machine (just the relevant Jet provider - which most versions of Windows will have) and would simply allow you to treat your workbooks as tables in a database. There's loads of code scattered around to show you how to do this.
With this of course you will get no requesters to worry about. If speed is an issue then use ODBC as this will generally be quicker.
I am pretty sure that the Jet 4.0 provider can connect to the older versions of the Excel file format.

[SFSxOI]

for comate for 4.30 I keep getting a syntax error on this line in Comate.pbi when using the released version of PB 4.30:

Code: Select all

Declare.i COMateClass_COMInvoke(*this._membersCOMateClass, invokeType, returnType, *ret.VARIANT, iDisp.iDispatch, subObjectIndex, parse._COMateParse(1))

I cant really pin down a syntax error. Am I just not seeing it?

parse???

Is there a parse in 4.30 or something?

[srod]

Downloading final version...

[srod]

Version compatible with PB 4.3 (release version) has been uploaded.
This version will not run on the beta versions of PB 4.3.

See the nxSoftware site for the download.

[DoubleDutch]

Just curious - What was the reason for the incompatibility?

[Little John]

If I'm allowed to guess: I'd say the new "Array" keyword was missing.

Regards, Little John

[srod]

Yes it was the array keyword; that sneaked in for PB 4.3 final that did!

[SFSxOI]

Yep, new version works with 4.30. Thanks srod

[SFSxOI]

I'm trying to use the windows shell with some netsh commands for some networking stuff, in vbs i just do a 'WshShell.Run' and then my command in quotes. Using comate how do you access the windows shell commands? Like for example, how would this be done in CoMate:

Code: Select all

WshShell.Run "netsh int tcp set global ecncapability=enabled"

??

[Kiffi]

Code: Select all

WshShell.Run "netsh int tcp set global ecncapability=enabled"

Code: Select all

Define WshShell.COMateObject

WshShell = COMate_CreateObject("WScript.Shell")

If WshShell
  
  WshShell\Invoke("Run 'netsh int tcp set global ecncapability=enabled'")
  
  WshShell\Release()
  
Else
  
  Debug "!WshShell"
  
EndIf

(untested)

Greetings ... Kiffi

[SFSxOI]

Code: Select all

WshShell.Run "netsh int tcp set global ecncapability=enabled"

Code: Select all

Define WshShell.COMateObject

WshShell = COMate_CreateObject("WScript.Shell")

If WshShell
  
  WshShell\Invoke("Run 'netsh int tcp set global ecncapability=enabled'")
  
  WshShell\Release()
  
Else
  
  Debug "!WshShell"
  
EndIf

(untested)

Greetings ... Kiffi

Thanks Kiffi;

It works but doesn't work, meaning, the above executes without error, but it doesn't make the desired changes. If I do it from a .vbs script it makes the desired changes, of course i ran the .vbs from an admin command prompt so i'm wondering if thats the reason which if it is in this case Comate isn't going to help me out.

[srod]

Try :

Code: Select all

Define WshShell.COMateObject 

WshShell = COMate_CreateObject("WScript.Shell") 

If WshShell 
  
  WshShell\Invoke("Run('netsh int tcp set global ecncapability=enabled')") 
  
  WshShell\Release() 
  
Else 
  
  Debug "!WshShell" 
  
EndIf