It is currently Sat Dec 14, 2019 7:39 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: ComatePlus + Excel + Autofit
PostPosted: Wed Jun 04, 2014 3:34 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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:
      ; 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


Top
 Profile  
Reply with quote  
 Post subject: Re: ComatePlus + Excel + Autofit
PostPosted: Wed Jun 04, 2014 7:21 pm 
Offline
Addict
Addict
User avatar

Joined: Tue Mar 02, 2004 1:20 pm
Posts: 1034
Location: Amphibios 9
just a guess: perhaps without the trailing parentheses?

Code:
WorkSheet\Invoke("Columns('A:F')\AutoFit")


Greetings ... Kiffi

_________________
Can't decide if i need a hug, an XXL coffee, 6 shots of vodka or 2 weeks of sleep.


Top
 Profile  
Reply with quote  
 Post subject: Re: ComatePlus + Excel + Autofit
PostPosted: Thu Jun 05, 2014 8:50 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
Thanks Kifi - but no joy.

It always crashes with 'invalid memory access'

I've also tried :

Code:
ExcelObject\Invoke("ActiveSheet\Columns\AutoFit")


and

Code:
ExcelObject\Invoke("ActiveSheet\Columns('A:F')\AutoFit")


and whilst neither crash the program - they don't 'do' anything.

Any ideas ??

cheers


Top
 Profile  
Reply with quote  
 Post subject: Re: ComatePlus + Excel + Autofit
PostPosted: Thu Jun 05, 2014 5:11 pm 
Offline
Addict
Addict
User avatar

Joined: Tue Mar 02, 2004 1:20 pm
Posts: 1034
Location: Amphibios 9
this one works for me:

Code:
  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

_________________
Can't decide if i need a hug, an XXL coffee, 6 shots of vodka or 2 weeks of sleep.


Top
 Profile  
Reply with quote  
 Post subject: Re: ComatePlus + Excel + Autofit
PostPosted: Fri Jun 06, 2014 9:27 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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:
ExcelObject\Invoke("ActiveSheet\Columns('F')\ColumnWidth ='40'")
and variations thereof with no joy either.

As you can tell - excel is not my forte :)

Cheers


Top
 Profile  
Reply with quote  
 Post subject: Re: ComatePlus + Excel + Autofit
PostPosted: Fri Jun 06, 2014 9:48 am 
Offline
Addict
Addict
User avatar

Joined: Tue Mar 02, 2004 1:20 pm
Posts: 1034
Location: Amphibios 9
captain_skank wrote:
I tried
Code:
ExcelObject\Invoke("ActiveSheet\Columns('F')\ColumnWidth ='40'")
and variations thereof with no joy either.


Code:
ExcelObject\SetProperty("ActiveSheet\Columns('F')\ColumnWidth=40")


Greetings ... Kiffi

_________________
Can't decide if i need a hug, an XXL coffee, 6 shots of vodka or 2 weeks of sleep.


Top
 Profile  
Reply with quote  
 Post subject: Re: ComatePlus + Excel + Autofit
PostPosted: Fri Jun 06, 2014 5:25 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
Kiffi to the rescue - thanks for that.

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


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye