Comate Plus and excel

[loulou2522]

Can someone help me to translate thist hree vb instruction in comate plus

Code: Select all

 ActiveSheet\ListObjects\Add(xlSrcRange, Range("$A$1:$C$2"), , xlYes).Name =   "Tableau1"
    Range("Tableau1[#All]").Select
    ActiveSheet.ListObjects("Tableau1").TableStyle = "TableStyleLight11"

I know that the value of xlSrcRange is 1 and xlYes = 1
Thanks in advance

[srod]

Need to know how you are accessing 'ActiveSheet' before a translation makes sense? Is 'ActiveSheet' a COMatePLUS object or are you working through a 'Worksheet' COMatePLUS object?

This would affect how we would try to translate this, i.e. ActiveSheet\SetProperty("ListObjects... etc)
or Worksheet\SetProperty("ActiveSheet\ListObjects... etc).

If I pick the wrong one then I'd only confuddle the issue!

Post a bit more of the VB code or/and your attempted PB translation.

[loulou2522]

Here is the code

Code: Select all

XIncludeFile #PB_Compiler_Home + "comateplus.pbi"

Global COMateObject ExcelObject, WorkBook

ExcelObject = COMate_CreateObject("Excel.Application")
ExcelObject\SetProperty("Visible = #True") 
ExcelObject\SetProperty("DisplayAlerts=0")

WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")

wORKBOOK\INVOKe("ActiveSheet\ListObjects\Add(1, Range('$A$1:$C$2'), , 1)\name='Tableau1'")
Workbook\invoke("Range('Tableau1[#All]')\Select")
Workbook\invoke("ActiveSheet.ListObjects('Tableau1').TableStyle = 'TableStyleLight11'")
fichier2$ = GetUserDirectory(#PB_Directory_Desktop)+"recap_suivi.xlsx"
Workbook\Invoke("SaveAs('"+fichier2$+"'"+",51)") 
;Fermeture du fichier Excel 
ExcelObject\Invoke("ActiveWorkbook\Close")
ExcelObject\SetProperty("Visible = #True")
ExcelObject\SetProperty("Visible = #True")
ExcelObject\SetProperty("ScreenUpdating = #True")
ExcelObject\SetProperty("DisplayAlerts=1")      
ExcelObject\Invoke("Quit")
WorkBook\Release()
ExcelObject\Release()   

[srod]

I think you have to pass a 'Range' object which we can wrap up in a COMate object easily enough. Note also the use of #Opt to define an optional parameter.

Code: Select all

XIncludeFile "COMatePLUS.pbi"
DisableExplicit

Define.COMateObject ExcelObject, WorkBook, Range
ExcelObject = COMate_CreateObject("Excel.Application")

If ExcelObject
  If ExcelObject\SetProperty("Visible = #True") = #S_OK
    WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")
    If WorkBook
      Range = WorkBook\GetObjectProperty("ActiveSheet\Range('A1:C2')")
      If Range
        Workbook\SetProperty("ActiveSheet\ListObjects\Add(1, " + Str(Range) + " As ComateObject, #Opt, 1)\name='Tableau1'")
        Range\Release()
      EndIf
      ExcelObject\Invoke("Quit()") 
      WorkBook\Release()
    EndIf
  EndIf
  ExcelObject\Release()
Else
  MessageRequester("COMate -Excel demo", "Couldn't create the application object!")
EndIf

Note that if you want to create the range '$A$1:$C$2' then you have to remember that the '$' character is COMate's escape character and so you need to use $0024 (which is hex 24 or ascii 36) in order to embed a '$' character in a COMate command string as with :

Code: Select all

'$0024A$00241:$0024C$00242'

So you would need to use :

Code: Select all

Range = WorkBook\GetObjectProperty("ActiveSheet\Range('$0024A$00241:$0024C$00242')")

[loulou2522]

Thanks Srod that's work perfectly
Have a nice day

[srod]

You're welcome.

[loulou2522]

I have another problem in the same program

Code: Select all

  Sheet = workbook\GetObjectProperty("ActiveSheet")
        
         Debug Comate_Getlasterrordescription()+ "Ligne 69"
      
         Sheet\Invoke("Columns('D:D')\Select")
         
         sheet\invoke("Selection\ColumnWidth = 25")

I can't arrive to redim column

[srod]

Code: Select all

Workbook\SetProperty("ActiveSheet\Columns('D:D')\ColumnWidth=25")

Works here.

You do need to use the correct COMate method call. That is you are using \Invoke() in places where you need to use \SetProperty() etc.