Page 1 of 1
Comate Plus and excel
Posted: Sat Feb 08, 2020 8:48 pm
by 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
Re: Comate Plus and excel
Posted: Sun Feb 09, 2020 11:58 am
by 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.
Re: Comate Plus and excel
Posted: Sun Feb 09, 2020 3:47 pm
by 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()
Re: Comate Plus and excel
Posted: Sun Feb 09, 2020 4:59 pm
by 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 :
So you would need to use :
Code: Select all
Range = WorkBook\GetObjectProperty("ActiveSheet\Range('$0024A$00241:$0024C$00242')")
Re: Comate Plus and excel
Posted: Sun Feb 09, 2020 5:19 pm
by loulou2522
Thanks Srod that's work perfectly
Have a nice day
Re: Comate Plus and excel
Posted: Sun Feb 09, 2020 5:23 pm
by srod
You're welcome.
Re: Comate Plus and excel
Posted: Sun Feb 09, 2020 7:54 pm
by 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
Re: Comate Plus and excel
Posted: Mon Feb 10, 2020 10:02 am
by 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.