ComatePlus excel

[captain_skank]

Hi All,

One for all you comateplus & excel experts

How do I get the value from another worksheet ( '0 - Summary' ) into my current worksheet ?

Neither of these work and i'm not sure where I would look for an error.

Code: Select all

ExcelObject\SetProperty("Cells(4,3) = '=SUM('0 - Summary'!E621:E795)'")
ExcelObject\SetProperty("Cells(4,4) = '='0 - Summary'!E" + LVAR_assembly_total + ")'")

Any and all help appreciated.

Cheers

[Marc56us]

Remove the apostrophes around the name of the sheet.
SUM( ' 0 - Summary ' !E621:E795)

Code: Select all

ExcelObject\SetProperty("Cells(4,3) = '=SUM(0 - Summary!E621:E795)'")

Works

[captain_skank]

Thanks for the reply.

Very strange, this puts the correct formula in the cell, but then opens a 'Save Sheet' window, and the formula doesn't calculate.

I have no other excel code after this statement either.

Oh well, I found another way to acheive the same result ( by storing the value of the cell in a variable ).

Cheers

[Marc56us]

You're right, there's something strange about the character management.
If you make a macro with the recorder, it adds characters around the sheet name, but if you put them back into ComatePlus it does not work.

(For the recording request message, you can delete it with \SetProperty("Application\DisplayAlerts = #False") )

Full example

Code: Select all

XIncludeFile "COMatePLUS.pbi"

Define.COMateObject ExcelObject, WorkBook
ExcelObject = COMate_CreateObject("Excel.Application")

If ExcelObject
    If ExcelObject\SetProperty("Visible = #True") = #S_OK
        WorkBook = ExcelObject\GetObjectProperty("Workbooks\Add")
        
        If WorkBook
            With ExcelObject
                \SetProperty("Application\DisplayAlerts = #False")     
                \invoke("Worksheets\Add")
                \SetProperty("ActiveSheet\Name('0 - Summary')")
                \SetProperty("Cells(1,1) = '1'")
                \SetProperty("Cells(2,1) = '2'")
                
                \invoke("Worksheets\Add")
                \SetProperty("ActiveSheet\Name('Total')")    
                \SetProperty("Cells(3,1) = '=SUM(0 - Summary!A1:A2)'")
                
                ;Delay(2000)
                ;\Invoke("Quit()") 
            EndWith
        EndIf
    EndIf
    ExcelObject\Release()
Else
    MessageRequester("COMate -Excel demo", "Couldn't create the application object!")
EndIf 

Here we have #Value in A3
If we edit A3 and add the ' ' in sheet name then it works.
But if you put them in Comate then it doesn't work.
I don't get it anymore.

[captain_skank]

Thanks for having a look - and confirming i'm not going nuts

I guess it's the way Comate is parsing content within the existing ' '.

Anyhoo, as I said i have a solution so no big problem.

[Marc56us]

Perfect if it works with your solution.

As I'm also interested in the question and I noticed that it only bugs if the name of the sheet contains one or more spaces. With a name without spaces, it works.

I found this by doing a test with Excel's macro recorder. It puts ' ' only if the name contains one or more spaces.

So while waiting for someone to modify the .PBI and if you are free to name the sheets, you have the alternative of putting sheet names without spaces and so use your first method.

[srod]

If you wish to use the single apostrophe character within a COMate quoted string parameter then you must use a COMate escape code, in this case $0027.

Code: Select all

ExcelObject\SetProperty("Cells(4,3) = '=SUM($00270 - Summary$0027!E621:E795)'")

[Kwai chang caine]

Works here

Hello great MASTER SROD
Always happy to see, that even if you are not with us often now and that miss me , you throw always a little eye sometime for protect your "PB childs"

[srod]

Hi Kwai,

always good to see you about these forums.

As it turns out I am here quite a bit at the moment as hospital treatment has forced me to pause my day job whilst I repair and recover. Truth is, that it is probably 50:50 whether I will be able to return to work anyhow given the invasive treatment that lies before me and so, well, more time for coding. Not the way I would have liked to have made more time for programming, but, needs must sometimes.

Can't complain. Programming was always what I was 'best' at (a matter of opinion really I guess! ) and so I will not be overly distressed if I have to spend all of my time coding!

[captain_skank]

Hey SROD,

Sorry to hear about your health and hope things soon pick up for you.

Thanks for the escape info, that had completely gone out of my head - been a while since i'd used ComatePlus.

As your going to be laid up for a while, are you going to get back into PB coding ? I'd really love to pay for an update to your excellent Arctic Reports ( Export to PDF, and Excel in particular ).

As always it's great to see you on the forums.

Cheers

[srod]

Yep, nothing else to do really and so I am hoping for plenty of coding. Only the ongoing treatments and side effects will get in the way, but am hoping that won't hold me up too much. The work involved in my little self-employed business was quite physical which is why I think that it will probably have to bite the dust. Shame as it was paying the bills and keeping me fit.

As for Arctic Reports; I fully hope to upgrade/replace that with something more robust and flexible. Free upgrade of course. Have to finish the cross-platform grid control first mind which was looking great and nearing completion until I decided that we ideally needed to be able to embed child grids within a given grid control which necessitated a restart! Should be back on track soon with that as I can carry across much of the existing code base albeit in a slightly different form.

[skywalk]

Very sorry to hear your health status.
Here's hoping the power of positivity and programming are fruitful.

[Kwai chang caine]

Thanks a lot great master for disclose a little part of your life
It's fundamental for a fan, to have this style of informations about his heroes, against never have news and waiting weeks after weeks, month after month, with questions in the head (and you good know, that already with nothing, you fill the head of kcc ), and that also show, how much the forum is important for you too
I know it will not help , but know that this announcement saddens me and i am wholeheartedly with you, as well as many family members surely
That's why, reading such news, sadly reminds us of life
Then.... again thanks a lot, and courage

When i thinking about what you say, use your muscles when you have an also powerfull brain....it's not a bit of a waste ?

You are a one of the main member of this forum, and mainly one of the most old i know (for old ...i talk about the forum ). I always remember your "crash car" avatar
One of the great masters, who put me in the eyes an already very long time ago, these stars that have never left since
And that...Kcc never remember how programming....but he always remember what you have do for him and obviously all the others members

It's funny... because not long ago, the great master NETMAESTRO also came back to say a little hello too
Again SPARKIE, TsSoft and some members, and it's a big part of the brain power and my heroes who come back, like in the old time