PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

COMatePLUS: Copy sheet and paste it in my workbook

https://www.purebasic.fr/english/viewtopic.php?t=39535

Page 1 of 1

COMatePLUS: Copy sheet and paste it in my workbook

Posted: Thu Oct 15, 2009 7:59 pm
by CNESM
Hi,

i want to copy my actual sheet into my workbook to get a 100% copy of a my sheet, but it looks like that this function "Sheets\copy" of the OWC11 doesn't run correct. So in the past i helped me with the following workarround:

Code: Select all

IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"

 
ExcelObject.COMateObject 
ExcelObject1.COMateObject 
obSheet.COMateObject

If OpenWindow(0,#PB_Ignore,#PB_Ignore,800,400,"COMate: OWC11.Spreadsheet-Demo",#PB_Window_Invisible|#PB_Window_SystemMenu) 
    
  ExcelObject=COMate_CreateActiveXControl(0,50,800,350,"OWC11.Spreadsheet") 
  
  If ExcelObject 
  
    ExcelObject\SetProperty("Cells(1,1) = 'Özgür'") 
    ExcelObject\SetProperty("Cells(1,2) = 'Levent'") 
    ExcelObject\SetProperty("Cells(2,1) = 10") 
    ExcelObject\SetProperty("Cells(2,2) = 20") 
    
    ExcelObject\SetProperty("Cells(1, 1)\Interior\Color = 18915966") 
    ExcelObject\SetProperty("Cells(1, 2)\Interior\Color = 18915966") 
    ExcelObject\SetProperty("Cells(2, 1)\Interior\Color = 18915966") 
    ExcelObject\SetProperty("Cells(2, 2)\Interior\Color = 18915966") 
    
    ExcelObject\SetProperty("Cells(1, 1)\Font\Color = 255255255") 
    ExcelObject\SetProperty("Cells(1, 2)\Font\Color = 255255255") 
    ExcelObject\SetProperty("Cells(2, 1)\Font\Color = 255255255") 
    ExcelObject\SetProperty("Cells(2, 2)\Font\Color = 255255255") 
    
    ExcelObject\GetObjectProperty("Sheets('3')\Delete") 
    ExcelObject\GetObjectProperty("Sheets('2')\Delete") 
  
    ExcelObject\SetProperty("Cells(1, 1)\HorizontalAlignment = -4152") ;-4152 = xlRight 
    ExcelObject\SetProperty("Cells(1, 2)\HorizontalAlignment = -4152") ;-4152 = xlRight 
    ExcelObject\SetProperty("Cells(2, 1)\HorizontalAlignment = -4152") ;-4152 = xlRight 
    ExcelObject\SetProperty("Cells(2, 2)\HorizontalAlignment = -4152") ;-4152 = xlRight 
    
    
    ExcelObject\SetProperty("Cells(1, 1)\Borders(7)\LineStyle = 1") ;7 = xlEdgeLeft ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(1, 1)\Borders(8)\LineStyle = 1") ;8 = xlEdgeTop ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(1, 1)\Borders(9)\LineStyle = 1") ;9 = xlEdgeBottom ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(1, 1)\Borders(10)\LineStyle = 1") ;10 = xlEdgeRight ; 1 = xlContinuous 
    
    ExcelObject\SetProperty("Cells(1, 2)\Borders(7)\LineStyle = 1") ;7 = xlEdgeLeft ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(1, 2)\Borders(8)\LineStyle = 1") ;8 = xlEdgeTop ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(1, 2)\Borders(9)\LineStyle = 1") ;9 = xlEdgeBottom ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(1, 2)\Borders(10)\LineStyle = 1") ;10 = xlEdgeRight ; 1 = xlContinuous 
    
    ExcelObject\SetProperty("Cells(2, 1)\Borders(7)\LineStyle = 1") ;7 = xlEdgeLeft ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(2, 1)\Borders(8)\LineStyle = 1") ;8 = xlEdgeTop ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(2, 1)\Borders(9)\LineStyle = 1") ;9 = xlEdgeBottom ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(2, 1)\Borders(10)\LineStyle = 1") ;10 = xlEdgeRight ; 1 = xlContinuous 
    
    ExcelObject\SetProperty("Cells(2, 2)\Borders(7)\LineStyle = 1") ;7 = xlEdgeLeft ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(2, 2)\Borders(8)\LineStyle = 1") ;8 = xlEdgeTop ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(2, 2)\Borders(9)\LineStyle = 1") ;9 = xlEdgeBottom ; 1 = xlContinuous 
    ExcelObject\SetProperty("Cells(2, 2)\Borders(10)\LineStyle = 1") ;10 = xlEdgeRight ; 1 = xlContinuous 
    
    ExcelObject\SetProperty("Cells(1, 1)\Borders(7)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(1, 1)\Borders(8)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(1, 1)\Borders(9)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(1, 1)\Borders(10)\Weight = 2") ;2 = xlThin 
    
    ExcelObject\SetProperty("Cells(1, 2)\Borders(7)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(1, 2)\Borders(8)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(1, 2)\Borders(9)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(1, 2)\Borders(10)\Weight = 2") ;2 = xlThin 
    
    ExcelObject\SetProperty("Cells(2, 1)\Borders(7)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(2, 1)\Borders(8)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(2, 1)\Borders(9)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(2, 1)\Borders(10)\Weight = 2") ;2 = xlThin 
    
    ExcelObject\SetProperty("Cells(2, 2)\Borders(7)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(2, 2)\Borders(8)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(2, 2)\Borders(9)\Weight = 2") ;2 = xlThin 
    ExcelObject\SetProperty("Cells(2, 2)\Borders(10)\Weight = 2") ;2 = xlThin 
    
    ExcelObject\SetProperty("DisplayToolbar = #False") 
    ExcelObject\SetProperty("ScreenUpdating = #True") 
    ExcelObject\SetProperty("ViewOnlyMode = #True") ;Schreibschutz 
    ExcelObject\SetProperty("DisplayOfficeLogo = #False") 
    ExcelObject\SetProperty("DisplayTitleBar = #False") 
    ExcelObject\SetProperty("ActiveWindow\ViewableRange = 'A1:B2'") 
  Else 
    
    Debug "!mySpreadsheet" 
      
  EndIf 
  
  ButtonGadget(0, 10, 10, 200, 20, "Copy")
  HideWindow(0,0) 
  
  If ExcelObject 
    Repeat 
    
    EventID = WaitWindowEvent()
    If EventID = #PB_Event_Gadget
      If EventGadget()=0
        
      ExcelObject\Invoke("Sheets(1)\Cells\Copy")
      obSheet=ExcelObject\Invoke("Sheets\add")
      strActiveName.s=ExcelObject\GetStringProperty("ActiveSheet\Name")
      ExcelObject\Invoke("Sheets('"+strActiveName.s+"')\Cells\Paste")
      
      ExcelObject\SetProperty("ViewableRange='A1:B2'")
      ExcelObject\SetProperty("DisplayToolbar=#False")
      ExcelObject\SetProperty("DisplayOfficeLogo=#False")
      ExcelObject\SetProperty("DisplayTitleBar=#False")
      ExcelObject\SetProperty("DisplayHorizontalScrollBar=#False")
      ExcelObject\SetProperty("DisplayAlert=#False")
      ExcelObject\SetProperty("AllowPropertyToolbox=#False")

      EndIf
    EndIf

    Until EventID=#PB_Event_CloseWindow 
    ExcelObject\Release() 
  EndIf 
  CloseWindow(0) 
EndIf
But why does the function:

Code: Select all

objSpreadsheet\Invoke("Sheets(1)\copy")
not work? You can see more information about this function in the OWC11 help file under "Copy Method".

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Thu Oct 15, 2009 9:04 pm
by srod
The Copy() method on a sheet object requires that you pass one of two possible parameters in order to determine which existing sheet to copy before or after.

Try something like the following :

Code: Select all

objSheet1.COMateObject = ExcelObject\GetObjectProperty("ActiveSheet")
If objSheet1
  ExcelObject\Invoke("Sheets(1)\Copy(#Empty, " + Str(objSheet1) + " as COMateObject)")
  objSheet1\Release()
EndIf

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Fri Oct 16, 2009 8:39 am
by CNESM
Hi scrod,

but one thing i didnt understand. The two parameters are optional parameters. So in normal Excel i dont have to set the paramters and i can only use "Sheets(1)\Copy" but via COM it doesnt seems to work and i had to set the parameters like you did.

But thanks scrod for your great support! It everybody would have such a great support like you all the big companys would have a better image :-D

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Fri Oct 16, 2009 10:00 am
by srod
I must admit that I do not understand that myself and indeed, I cannot get it to work when specifying the 'before' parameter. I have checked the COMatePLUS code carefully and can only conclude that there is a bug with the component itself.

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Fri Oct 16, 2009 10:27 am
by CNESM
"The world make its own rules"

So, scrod, thanks a lot!. Now i know for the future, that i have to set all parameter, optional and not optional and take this information in my head :-)

Best wishes from Germany, scrod!

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Sat Oct 17, 2009 11:39 pm
by HeX0R
*pssst*: the name is srod!

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Sun Oct 18, 2009 11:30 am
by srod
HeX0R wrote:*pssst*: the name is srod!
:lol:

CNESM: best post these sort of questions in future in either the coding questions forum or in the main COMatePLUS thread. Best not start new threads in this forum just for a single question.

Re: COMatePLUS: Copy sheet and paste it in my workbook

Posted: Tue Oct 20, 2009 2:55 pm
by CNESM
srod wrote:
HeX0R wrote:*pssst*: the name is srod!
:lol:

CNESM: best post these sort of questions in future in either the coding questions forum or in the main COMatePLUS thread. Best not start new threads in this forum just for a single question.
Urgh, sorry srod for using wrong name! I dont know how i came to scrod .....

Ok, i will post all further questions in the main topic!
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited