You are not logged in.

Palo - Visual Basics

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

Vautid

Trainee

  • "Vautid" started this thread

Posts: 1

Date of registration: Oct 17th 2011

  • Send private message

1

Tuesday, October 18th 2011, 8:29am

Palo - Visual Basics

Hello,

our Problem is:

We want to start a macro in excel but it won´t works because the run-time is to short for palo to calculate the new data in Excel.

You must start the marco over again, then it works.

Have any body an answer for this problem with a nother approach?

Thanking you anticipation.

laloune

Sage

Posts: 216

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

2

Tuesday, October 18th 2011, 3:03pm

Hi,

what about an Application.Wait (intTime in milliseconds) statement to wait until Palo has finished the calculation ?
laloune

"To understand recursion, one must understand recursion"

realquo

Sage

  • "realquo" is male

Posts: 255

Date of registration: Mar 11th 2009

Location: Italy

Occupation: BI Consultant

  • Send private message

3

Tuesday, October 18th 2011, 9:24pm

Hi,
what about

Source code

 
1

application.calculation = xlCalculationManual

at the start of your macro (so excel won't calculate your data), then


Source code

 
1
2

application.calculate
application.calculation = xlCalculationAutomatic

at the end, so you can perform all your operation in the middle without having to wait for palo to calculate? Obviously this will work if you don't need the product of the palo calculation inside your macro.

Regards,

RQ

gchicoye

Intermediate

Posts: 10

Date of registration: Feb 28th 2011

  • Send private message

4

Tuesday, March 6th 2012, 11:37am

Same problem, no answer, any idea?

realquo

Sage

  • "realquo" is male

Posts: 255

Date of registration: Mar 11th 2009

Location: Italy

Occupation: BI Consultant

  • Send private message

5

Tuesday, March 6th 2012, 12:08pm

Hi,
why not the solution I suggested above?

Quoted from "realquo"

Hi,
what about

Source code

 
1

application.calculation = xlCalculationManual

at the start of your macro (so excel won't calculate your data), then


Source code

 
1
2

application.calculate
application.calculation = xlCalculationAutomatic

at the end, so you can perform all your operation in the middle without having to wait for palo to calculate? Obviously this will work if you don't need the product of the palo calculation inside your macro.

Regards,

RQ

gchicoye

Intermediate

Posts: 10

Date of registration: Feb 28th 2011

  • Send private message

6

Tuesday, March 6th 2012, 12:15pm

I've tried it, but it does not work!

I've tried as well the Application.Run("PALO.CALCSHEET"), but I've got the same problems!

dmarkovic

Master

  • "dmarkovic" is male

Posts: 90

Date of registration: Aug 11th 2011

Location: Vienna / Austria

Occupation: Vector SW DV GmbH

  • Send private message

7

Tuesday, March 6th 2012, 12:35pm

Hi,

why you don't try to force your macro to calculate the palo-data, then a force to wait (~1-3sec) and then again a calculation (refresh view).

Regards
Djordja Markovic
Vector SW DV GmbH

Interessant things:
Internal derby:
http://www.jedox.com/community/palo-foru…14338#post14338
Calculate your cube size:
http://www.jedox.com/community/palo-foru…14406#post14406

gchicoye

Intermediate

Posts: 10

Date of registration: Feb 28th 2011

  • Send private message

8

Tuesday, March 6th 2012, 3:00pm

Quoted from "dmarkovic"

Hi,

why you don't try to force your macro to calculate the palo-data, then a force to wait (~1-3sec) and then again a calculation (refresh view).

Regards
I've already tried, but the calculation is blocked until I'm out the macro...

dmarkovic

Master

  • "dmarkovic" is male

Posts: 90

Date of registration: Aug 11th 2011

Location: Vienna / Austria

Occupation: Vector SW DV GmbH

  • Send private message

9

Tuesday, March 6th 2012, 3:22pm

Hello,

Quoted from "gchicoye"


I've already tried, but the calculation is blocked until I'm out the macro...


maybe it is a problem with the cell. Try this out:

Source code

 
1

 Application.EditDirectlyInCell = True


set this statement before you called your method.
I hope it will help you!

Regards.
Djordja Markovic
Vector SW DV GmbH

Interessant things:
Internal derby:
http://www.jedox.com/community/palo-foru…14338#post14338
Calculate your cube size:
http://www.jedox.com/community/palo-foru…14406#post14406

gchicoye

Intermediate

Posts: 10

Date of registration: Feb 28th 2011

  • Send private message

10

Thursday, March 8th 2012, 9:02am

Does not work as well... Here is my code

Source code

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Application.EditDirectlyInCell = True
 Application.Calculation = xlCalculationManual
Sheets("Chiffre").Activate
            Range("C5").Select
            ActiveCell.Value = IDHotel

            Application.Calculate
            Application.Calculation = xlCalculationAutomatic
            
            Application.Run ("PALO.CALCSHEET")
           

   
   'Impression PDF
           Sheets("Chiffre").Select
            Range("C5").Select
            IDHotel = ActiveCell.Value
            Nomhotel = Sheets("Chiffre").Range("E5").Value
                            
                Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
                NomExcel = ThisWorkbook.Name
              NomPdf = IDHotel & " - " & Nomhotel & " - Stat 2011" & ".pdf"

'To be continued


I get an error at this stage...

realquo

Sage

  • "realquo" is male

Posts: 255

Date of registration: Mar 11th 2009

Location: Italy

Occupation: BI Consultant

  • Send private message

11

Thursday, March 8th 2012, 8:08pm

Hi,
I guess you have to put the calculation method set to Manual during the whole operation and set it to auto at the end:

Source code

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Application.EditDirectlyInCell = True
 Application.Calculation = xlCalculationManual
Sheets("Chiffre").Activate
        	Range("C5").Select
        	ActiveCell.Value = IDHotel

        	Application.Calculate
        	' Application.Calculation = xlCalculationAutomatic	'' <<<--       	
        	Application.Run ("PALO.CALCSHEET")                   	'' application.calculate should already have recalc palo formulas, performing it again here doubles the time
       	   
   'Impression PDF
       	Sheets("Chiffre").Select
        	Range("C5").Select
        	IDHotel = ActiveCell.Value
        	Nomhotel = Sheets("Chiffre").Range("E5").Value
                        	
            	Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
            	NomExcel = ThisWorkbook.Name
          	NomPdf = IDHotel & " - " & Nomhotel & " - Stat 2011" & ".pdf"

        	Application.Calculation = xlCalculationAutomatic	'' <<<--        	now is the time to set to auto mode

'To be continued


Hope it helps,
regards,
RQ

gchicoye

Intermediate

Posts: 10

Date of registration: Feb 28th 2011

  • Send private message

12

Friday, March 9th 2012, 12:21pm

It fact, I had palo.datac formulas in my spreadsheet, I just had to replace it by palo.data, it works perfectly!

Thanks anyway!

dmarkovic

Master

  • "dmarkovic" is male

Posts: 90

Date of registration: Aug 11th 2011

Location: Vienna / Austria

Occupation: Vector SW DV GmbH

  • Send private message

13

Saturday, March 10th 2012, 3:08pm

Quoted from "gchicoye"

It fact, I had palo.datac formulas in my spreadsheet, I just had to replace it by palo.data, it works perfectly!

Thanks anyway!


Same like the other problem you post :)

Regards.
Djordja Markovic
Vector SW DV GmbH

Interessant things:
Internal derby:
http://www.jedox.com/community/palo-foru…14338#post14338
Calculate your cube size:
http://www.jedox.com/community/palo-foru…14406#post14406

byoshimi99999

Sage

Posts: 97

Date of registration: Feb 20th 2013

  • Send private message

14

Monday, February 25th 2013, 11:42pm

Follow up to this post:

I noticed with Jedox 4.0.0 that with a web spread sheet if I have a region, lets say B100:N150 populated with the following:
=PALO.DATAC("database","cube",$A100,B$99,@sel_product,@target_datatype,@target_scenario)


If I update my cube, I need to issue a "Data -> Refresh data" command to get refreshed data into my cube.


BUT, if I just change B100 to

=PALO.DATA("database","cube",$A100,B$99,@sel_product,@target_datatype,@target_scenario)
AND leave the rest of the cells alone...


Then magically, the entire B100:N150 region updates correctly when the cube updates.


No. I lied.


If I update the part of the cube corresponding to B101, the view doesn't auto update :(


It appears that if I do a mass cube update and at least B100 is updated, then the rest of the table refreshes correctly....


Looks like I'll also need to change all of my PALO.DATAC() into PALO.DATA().


Still, it's interesting to see how these two commands partially work together when an underlying cube is updated.

Similar threads

Rate this thread