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.
Pop-up box for element-selection via VBA
Hello, I started working with palo some days ago. Now I'm searching for a possibility to open the pop-up box for element selection via VBA (the box, that appears if I make a double click on an element field). Something like
Range("A2").Select
Application.DoubleClick
didn't work. Could anybody help me?
Thanx a lot
RE: Pop-up box for element-selection via VBA
Hi,
first set a VBA reference on the "Jedox.Palo.XlAddin.tlb". You find it in e.g. "c:\program files\Jedox\Palo\". Here is a function you can use for showing the element selector. It returns the selected element name.
Public Function getElementBySelection(serverdb As String, Dimension As String)
'e.g. try a call with "localhost/Demo" as serverdb and "Products" as dimension
Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
Set obj = New Jedox_Palo_XlAddin.ComInterface
getElementBySelection = obj.ChooseElements(serverdb, Dimension)
End Function
Holger
This post has been edited 1 times, last edit by "h_decker" (Jun 24th 2008, 10:49am)
RE: Pop-up box for element-selection via VBA
Hi Holger,
many thanks for your help. It seems to work fine. Now I have the following question about the VBA reference to the tlb-file:
Normally, users don't have this reference in Excel/VBA. How can I implement this that users get this reference automatically when they start the excel file (or the function). And how can I get dynamically the program path of Jedox Palo, where I find this tlb-file (e.g. from a registry-key. Not all users have the same Palo-programpath. So I can't use a fixed string).
Thank you for your help
RE: Pop-up box for element-selection via VBA
Usually Excel tries to restore the reference if it can find it. But of course you can set it by your own using application with e.g.
ThisWorkbook.VBProject.References.AddFromFile ...
or
ThisWorkbook.VBProject.References.AddFromGuid ...
You find information about palo path e.g. in HKEY_USERS\.DEFAULT\Software\Jedox\Palo\XlAddin
Holger
RE: Pop-up box for element-selection via VBA
Thank you for this information. I think, now I can work forward.
One little detail question: it's possible, to select a default element in the pop-up box for element-selection? I like to select the element in the box, which was given to the report by the last selection.
Thank you very much
RE: Pop-up box for element-selection via VBA
Sorry, no idea. May you could at least check the result for an empty string so that user is not able to choose nothing and take the last value instead.
This post has been edited 2 times, last edit by "h_decker" (Jun 24th 2008, 1:24pm)
RE: Pop-up box for element-selection via VBA
...or is there a possibility to set a doubleclick on a cell per vba (Application.DoubleClick doesn't work)? When I do a doubleclick manually, the 'old' element in the pop-up box is marked. I'd like to mark the actual element that's selected in the report per vba (so that if the user presses the 'OK'-Button without a selection before, the correct element will be choosen and the user will have a better orientation when the active element is marked).
Or if you have a workaround, please let me know.
Thank you.
RE: Pop-up box for element-selection via VBA
Goodday,
My question might sound a little bit strange to you but could you please explain to me how to set the VBA reference as written below:
"first set a VBA reference on the "Jedox.Palo.XlAddin.tlb". You find it in e.g. "c:\program files\Jedox\Palo\". Here is a function you can use for showing the element selector. It returns the selected element name.
(written by h_decker)".
An step by step example would be great!
Sorry I'm a newbie!
Thanks a lot!
Liv
RE: Pop-up box for element-selection via VBA
Hi,
open Excel VBA Editor. e g. via ALT+F11. Then choose Tools in Dropdownmenu. There should be a menue ntry entry called References. In german Excel VBAS its Menu "Extras" and "Verweise". Then browse for the explained file.
Hope that helps.
Holger
