ComatePlus + Excel + Autofit

Applications, Games, Tools, User libs and useful stuff coded in PureBasic
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

ComatePlus + Excel + Autofit

Post 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
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: ComatePlus + Excel + Autofit

Post by Kiffi »

just a guess: perhaps without the trailing parentheses?

Code: Select all

WorkSheet\Invoke("Columns('A:F')\AutoFit")
Greetings ... Kiffi
Hygge
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: ComatePlus + Excel + Autofit

Post 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
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: ComatePlus + Excel + Autofit

Post 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
Hygge
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: ComatePlus + Excel + Autofit

Post 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
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: ComatePlus + Excel + Autofit

Post 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
Hygge
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: ComatePlus + Excel + Autofit

Post by captain_skank »

Kiffi to the rescue - thanks for that.

I can't get my head around when to use \Invoke and \SetProperty.
Post Reply