I have one requirement i.e. my dashboard has sheets like Home,America-A,America-B,America-C etc......
In Home Page I have 2 ListBoxes Region and BU If user select Region and BU then Corresponding sheets will enable.
Ex: If user select America,Europe as regions and A as BU then two sheets will enable 1) America-A 2)Europe-A.
Each sheet has two tables and two buttons to export data table to Excel and PPT.
My requirement is If i select America and A as BU then America-A sheet will enable and if i click on export buttons then first table will export to Excel or PPT.
If i select two regions America,Europe and BU as 'A' then America-A,Europe-A sheets will enable.
If you click on Export to Excel (or PPT) button from these two sheets then first table from both sheets should export to single Excel(or PPT) with different sheets and sheet name should be heading of the table.
Even if user selects multiple sheets then also first table from all sheets should export to single Excel file with different sheets and sheet name should be heading of the table.
I googled more than one day and find export to excel(or PPT) macros but i didn't get exact output. If I click on Export to excel then it's exporting all first table from all sheets but i need only selected Region's tables.
I am not getting object title in PPT slides but i have to show the title as object title.
Could you please help me here I am very new to VB script.
Is there any way to pass variable into arrays? If yes please give me idea i'll try to pass variable with Object names dynamically instead of hard coding them in array like below
It's a quite challenging task and I'm not sure if your approach isn't a bit too complicated for an application which only reason seems to be to export tables.
One alternative approach might be to export all of those tables beforehand maybe in each combination and another could be to use a xls/ppt-masterfile approach whereby the xls/ppt is linked to simpler qv export-files (especially by more complicated layout-requirements is this easy than to build everything per macro). This avoids macros within the gui: Macros are Bad This meant it should be worth to consider if there are not other methods easier to implement.
Even if something like the above mentioned alternatives isn't suitableI would try to simplify the task and use always the same object(s) to export especially if the action will be triggered per button. Very often I use a hidden sheet for prints and exports - even if you showed the user various sheets and objects you could use for printing and exporting other (maybe further specialized) objects. If I write this I think it might even easier if there are only one or two sheets and objects and their visibility will be controlled by variables.
If you really want to create this like described the I suggest that you have a look on the APIGuide.qvw (is in your install-folder) which provides many examples like this:
for i = 0 to ActiveDocument.NoOfSheets - 1
set ss= ActiveDocument.GetSheet(i)
msgbox(ss.GetProperties.Name)
next
function Rand (Lo, Hi)
Rand = Lo + Rnd() * (Hi - Lo + 1) - 0.5
end function
sub randpiv
rem ** set random colors in all expression **
rem ** cells in all pivot tables on active sheet. **
as examples to loop through sheets and sheet-objects (and access their properties) as alternatives to your mentioned hard-coding arrays.
For the export-routines itself (multiple objects into one application) you will find various examples here within the community - they need to be only* to adjust and combined and checked.
* meant: it's not extrem complicated but it will take some time - good luck.
1)One alternative approach might be to export all of those tables beforehand maybe in each combination and
2)another could be to use a xls/ppt-masterfile approach whereby the xls/ppt is linked to simpler qv export-files (especially by more complicated layout-requirements is this easy than to build everything per macro). This avoids macros within the gui:
Here was meant to do the export-job at beforehand maybe directly after a nightly update-task to these application. It's quite common for rather regular respectively static reports (prints and exports) to create them automatically and distribute them per mail or per network-storage which could be accessed directly or maybe per url-link.
Such approach isn't perfect because probably a lot of "wasted" reports will be created (nobody used them) and it requirred ressources to produce them. On the other hand those efforts happens mostly within the nightly update-window and not daily while the server has assumingly the highest workload. Further it reduced the development-efforts for such printing/reporting, avoids macros within the gui (which don't work within in AJAX client) and security measures/rules could be (rather) better applied.
2)
Here was meant to use always the same finished xls/ppt-files which are once manually created and whose objects are linked to certain data-ressources. And then those data-ressources will be updated or exchanged with the qv-data exports. Especially if there are complex report-requirements with a lot of objects and complicated layouts which are very expensive to build with macros has this approach advantages.
If there are a lot of report-requirements it's worth to consider specialized tools for it, for example QlikView NPrinting.
