Page 1 of 1
ComatePlus + Excel + Autofit
Posted: Wed Jun 04, 2014 3:34 pm
by captain_skank
Hi All,
What I want to do is take the contents of the clipboard an paste it into excel - then autisize the columns/rows if possible.
The following does the first part but I cant get the autosize to work
Code: Select all
; open excel, create a new sheet and paste the contents of the clipboard
Define.COMateObject ExcelObject, WorkBook, WorkSheet
ExcelObject = COMate_CreateObject("Excel.Application")
If ExcelObject
If ExcelObject\SetProperty("Visible = #True") = #S_OK
WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")
If WorkBook
WorkSheet = ExcelObject\GetObjectProperty("Sheet1")
ExcelObject\Invoke("ActiveSheet\Paste")
; should autofit the columns - i think
;WorkSheet\Invoke("Columns('A:F')\AutoFit()")
WorkBook\Release()
EndIf
EndIf
ExcelObject\Release()
Else
MessageRequester("COMate -Excel demo", "Couldn't create the application object!")
EndIf
I think the syntax for the autofit is correct, but it keeps crashing .
Anyone got any ideas ??
Cheers
Re: ComatePlus + Excel + Autofit
Posted: Wed Jun 04, 2014 7:21 pm
by Kiffi
just a guess: perhaps without the trailing parentheses?
Code: Select all
WorkSheet\Invoke("Columns('A:F')\AutoFit")
Greetings ... Kiffi
Re: ComatePlus + Excel + Autofit
Posted: Thu Jun 05, 2014 8:50 am
by captain_skank
Thanks Kifi - but no joy.
It always crashes with 'invalid memory access'
I've also tried :
Code: Select all
ExcelObject\Invoke("ActiveSheet\Columns\AutoFit")
and
Code: Select all
ExcelObject\Invoke("ActiveSheet\Columns('A:F')\AutoFit")
and whilst neither crash the program - they don't 'do' anything.
Any ideas ??
cheers
Re: ComatePlus + Excel + Autofit
Posted: Thu Jun 05, 2014 5:11 pm
by Kiffi
this one works for me:
Code: Select all
If ExcelObject\SetProperty("Visible = #True") = #S_OK
WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")
If WorkBook
WorkSheet = ExcelObject\GetObjectProperty("ActiveSheet")
If WorkSheet
WorkSheet\Invoke("Paste")
WorkSheet\Invoke("Columns('A:F')\AutoFit")
WorkSheet\Release()
Else
Debug "!WorkSheet"
EndIf
WorkBook\Release()
EndIf
EndIf
Greetings ... Kiffi
Re: ComatePlus + Excel + Autofit
Posted: Fri Jun 06, 2014 9:27 am
by captain_skank
Once again Kiffi - thanks for the effort.
You are correct - it works.
However - i'm pasting data from the webgadget and for some reason autofit does not work on this data ??
Is there a way to indiviualy set column widths ??
I tried
Code: Select all
ExcelObject\Invoke("ActiveSheet\Columns('F')\ColumnWidth ='40'")
and variations thereof with no joy either.
As you can tell - excel is not my forte
Cheers
Re: ComatePlus + Excel + Autofit
Posted: Fri Jun 06, 2014 9:48 am
by Kiffi
captain_skank wrote:I tried
Code: Select all
ExcelObject\Invoke("ActiveSheet\Columns('F')\ColumnWidth ='40'")
and variations thereof with no joy either.
Code: Select all
ExcelObject\SetProperty("ActiveSheet\Columns('F')\ColumnWidth=40")
Greetings ... Kiffi
Re: ComatePlus + Excel + Autofit
Posted: Fri Jun 06, 2014 5:25 pm
by captain_skank
Kiffi to the rescue - thanks for that.
I can't get my head around when to use \Invoke and \SetProperty.