1) Put:
“ActiveDocument.GetApplication.Save”
“ActiveDocument.GetApplication.Quit”
at the end of the macro.

2) Associate the “export&save Excel macro” to the “OnPostReload” event.

3) Use this call in the command line:
“Qlikview.exe /l /NoSecurity doc.qvw”

The “/l” trigger will open doc.qvw and reload it without closing and saving.

The export macro will be triggered after the reload.

“ActiveDocument.GetApplication.Save” will force a QlikView save.
“ActiveDocument.GetApplication.Quit” will force a QlikView to close.

This will NOT work with the /r trigger. The /l trigger works in a different way.

This also applies to stuff where the /r trigger does not wait for the macro to finish and closes the QlikView document.

cool, easy and nice to use.

bye
Konrad

Thanks for your quick reply.

The resulting expression would not work when I use it in my project. The problem seemst to be related to the part between the account numbers and the else-if statements: 14600000)','$(='S')','$(=If
The tool adds e.g. a closing bracked after the account number, a $-expansion around the search string, a $-expansion around the next if statement and some extra single quotes. When I remove these, the expression works fine.

Regards,
Thomas

http://tools.qlikblog.at/SetAnalysisWizard/?sa=O8PF

Where do you struggle?

Regards
Stefan

Here is the result:
http://tools.qlikblog.at/SetAnalysisWizard/?sa=HJ3M

Regards
Stefan

Sum({1<fact_PhysicalMovement.order_type={"$(=If(dim_AccountAssignmentRules.sap_account_number=14600000,'S',If(dim_AccountAssignmentRules.sap_account_number=14680000,'T',Null())))"}>}fact_PhysicalMovement.total_amt_std)

This is an incredible tool! Thanks very much.

I tried to create the following expression with your tool but did not suceed…

Sum ({1} fact_PhysicalMovement.total_amt_std)

Regards,
Thomas

Thanks & Regards

Anil

Nice job! I am sure this will get used by thousands of people….

Ed

yes, the idea of generating the comments automatically is IMHO one of the key features which is really important to be able to re-edit the expressions later on …

Regards
Stefan

yes, absolutely. I will probably do that in the future but before creating such an application (or publishing my existing test-application ) I first want to expand the examples section by adding a lot of new examples, dealing with more complex set analysis expressions.

Regards
Stefan

first of all I will concentrate to improve the Set Analysis Wizard a little bit (especially by adding the QV 11 functionality of Alternate States), so there are no further wizards in the pipeline

… but some other ideas …

Regards
Stefan

Thanks for the Awesome work done for developers new to QV world to know Set Analysis.

Regards
Ashwin Pagare

Very good! Nice tool for people who don’t do a lot of set analysis.

Regards,

Stephen

Thank you very much for sharing this awesome tool with us.
Set Analysis is very powerfull but hard to understand the goal of an existing expression even if you did create it yourself !!!
Generation documentation while creating the expressing is a briljant concept.
This is the wizard we need in QlikView ASAP.

Regards Daan

Greetings, Ebony

Thank you very much for creating this awesome tool.
I especially like the feature of saving the expression on the server, being able to come back and change it after some time!!!

Do you plan any further wizards like this one or the nested-ifs-wizard? ;);)

Cheers, V.

Davide Garavaglia

Brgds / Lasse J

But, for anyone interested, it turns out the code does work, it was an issue with macro security in Excel that was causing my problem.

I found this on Microsoft Support, refer to step 7 for the correct settings in Excel.
http://support.microsoft.com/kb/219905

Thank you Stefan for sharing your knowledge to us.
I have the similar requirement with a small change where as i cannot hardcode the range where the object has to get pasted.
How to dynamically choose the range for pasting the second object. And how to add header and footer dynamically based on the number of pages in the excel sheet. Can you please provide some samples.

No filter for Region then export to GLOBAL tab
Region = AMER export to AMER tab
etc…

I really don’t know VBScript, but I tried this without any success:

objExcelWorkbook.Application.VBE.ActiveVBProject.VBComponents.Import “C:\Users\user\Desktop\macro.bas”
objExcelWorkbook.Application.Run “macro”

Regards
Stefan

Longitude:
=avg( round (256*pow(2,($(var_zoom)-1)))+( Longitude *((256*pow(2,$(var_zoom)))/360)) )
Latitude:
=avg(((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((Latitude)*pi()/180)))/(1-(sin((Latitude)*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))))
Sum of tickets:
=Dual(Country, sum({$} [Case Count]))

String on the Dynamic Image=
=’http://maps.google.com/staticmap?center=’
& num(var_mid_lat, ‘##############’, ‘.’, ‘,’ ) & ‘,’
& num(var_mid_long, ‘##############’, ‘.’, ‘,’ )
& ‘&zoom=$(var_zoom)’
& ‘&maptype=’&var_maptype
& ‘&size=’&map_size_x&’x'&map_size_y
& ‘&key=’&gmap_key & ‘.jpg’
& ‘sensor=true_or_false’

Script:
// Google Maps in QlikView
// V0.9 – October 1st 2008 © Copyright QlikTech International AB 2008 / AES

// Google Maps Key
// get a key here http://code.google.com/apis/maps/signup.html
gmap_key = ‘ABQIAAAA2bV_3zvH8IN-gNX92ByjgRSdapX2gNjAd7fDB7lOLcIRZhEleBSz9ZaixvLiWeauCbq9wsjh-DzpVA’;
max_zoom_level = 5; //maximum value 17

// Variables required for calculating map
// No need to change these
var_pi180= ‘=pi()/180′;
var_lat_offset= ’0′;
var_mc2= ‘=256*pow(2,$(var_zoom))’;
var_mc1= ‘=256*pow(2,($(var_zoom)-1))’;
var_mid_lat= ‘=min(Latitude)+(1+var_lat_offset)*((max(Latitude)-min(Latitude))/2)’;
var_mid_long= ‘=min(Longitude)+(max(Longitude)-min(Longitude))/2′;
var_zoom= ‘=max(aggr(if(max( round(256*pow(2,(_zoom_level -1)))+( Longitude *((256*pow(2,_zoom_level ))/360)) )-min( round(256*pow(2,(_zoom_level -1)))+( Longitude *((256*pow(2,_zoom_level ))/360)) ) <map_size_x AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((Latitude)*pi()/180)))/(1-(sin((Latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((Latitude)*pi()/180)))/(1-(sin((Latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<map_size_y,_zoom_level,1),_zoom_level))';
var_maptype= '=if(isnull(only(maptype)),fieldvalue( '&chr(39)&'maptype'&chr(39)&', 1 ),maptype)';
map_size_x= '800';
map_size_y= '600';

SET HidePrefix='_' ;
// Field required for calcualting best zoom level
_zoom_level:
Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);

maptype:
LOAD * INLINE [
maptype
roadmap
mobile
satellite
terrain
hybrid
];

Thanks for your support!

Please send me an example (you’ll find my eMail-address in the “About Me” section of this blog).

Regards
Stefan

the answer for your questions is quite simple:

Just use the macro recorder in Excel, have a look at the generated code and use it in VBScript …
That’s the way I have implemented similar taks…

Hope this helps!

Regards
Stefan

Truly great read thank you for all this and the code. With your answers to comments I have started implementing exactly what the bosses were asking for. Until they decided the wanted additional formatting into the excel document created here. Do you know or have resources to do that? Some things include one row text be rotated 90 degrees or shaded while another row would be black with white text. They also want ascetic changes similar to those. To be honest I don’t really know where to begin.

is there a way to export chart or table window title, even if it’s a calculated one?
With mode “picture”, no problem, but I want it also for mode “data” or “export excel” to be able to modify value in excel file if needed. I’ve made this code for export excel:

if (pasteMode = "image") then
      objExcelDoc.Sheets(sheetName).PasteSpecial "Bitmap"
elseif (pasteMode = "excel") then

      'Format cells and pastespecial
      xlDown = -4121
      xlToRight = -4161

      Set objExcel = CreateObject("Excel.Application")
      objExcel.Workbooks.Open("c:\temp\Flashtemp.xls")
      objExcel.Visible = True
      objExcel.DisplayAlerts = false
      objExcel.ActiveSheet.Range("A1",objExcel.ActiveSheet.Range("A1").End(xlDown).End(xlToRight)).select
      objExcel.ActiveSheet.Range("A1",objExcel.ActiveSheet.Range("A1").End(xlDown).End(xlToRight)).copy
      objExcelDoc.Sheets(sheetName).Range(sheetRange).Select   'Select field where data should be pasted

      objExcelDoc.Sheets(sheetName).PasteSpecial (xlPasteAllUsingSourceTheme)   'PasteSpecial to be allowed to modify table on Excel with same QlikView Format
      objExcel.Workbooks("Flashtemp.xls").Close
   else
      objExcelDoc.Sheets(sheetName).Paste
end if

Thanks for your help

Regards

Laurent

Could you please help me with a sample .vbs file which I can use to export a table object data using external tasks.

I tried writing using the options you have advised, but I couldn’t get it.

Thanks in advance for your help.

Regards,
Murali

I found out that is error occurs if the function “copyObjectsToExcelSheet” is not included …

Regards
Stefan

Have a look at the attached examples …

Let me know, if this helps!

Regards
Stefan

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

sub exportexcel

Dim aryExport(1,3)

aryExport(0,0) = "TB08"
aryExport(0,1) = "Sales per Region"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "TB16"
aryExport(1,1) = "Top Customers"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

end sub
Thanks
Mallikarjun

Ctrl + Shift + M
=> then select “Give System Access to Module Script”

Hope this works!

Regards
Stefan

ActiveX component can’t create object: ‘Excel.Application’

You have two possibilities to publish QlikView applications:

- The QlikView-file itself (for offline usage); in this case every user would need a local QlikView client
- If you are using the web client of QlikView you always need a QlikView Server/Webserver behind …

Hope this helps!

Regards
Stefan

Regards
Stefan

Thanks for your contribution. A real helper.

I have the issue that the QV objects that I send to Excel get moved to very strange locations in the sheet; hence scrambling my QV object. Any insights as to what can be causing this?

Regards,

Luis

ich hätte auch Interesse an einer Lösung für ein Diagramm (Pivottabelle).
Es soll ein Wert der im Umsatz 0 ist nicht angezeigt werden und somit die dahinter stehende Stückzahl mitgezählt werden.

Danke und viele Grüße
MW

Thanks for your reply.

I found that excel would show the path to the image file on the export so I ended up doing a search across all cells for the string and changed the value and cell background.

That way i could still have the images in qlikview but the excel report would still make sense

Regards
Lee

no unfortunately I do not know any possibility to transfer the images within tables also as images to Excel.
What I would do if Excel-export is needed:

- Instead of using images use cell-background to highlight special rows/cells

Hope this helps …

Regards
Stefan

I want to publish Qlikview Apps on Google Sites, is that posible ????. Actually users can only access via Acces Point.
Thanks for help
Regards,

Thanks for the great solution! I’ve been trying to work out how to do this for a while now.

Do you know if there is a way to transfer chart traffic light images along with the data? Currently I am left with a worksheet showing the path in the cell

Thanks

Lee

Certainly this option is only available for linked objects:

Context Menu
Linked Objects
Unlink This Object

Regards
Stefan

Regards
Stefan

Have a look at one of the comments below:
http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/comment-page-1/#comment-24865

Regards
Stefan

1) Excel is not installed ?? ==> Install Excel
2) The security settings do not allow to create an instance of Excel ==> check the security settings of your macro-code!!!

Regards
Stefan

1) Create a data island with the formulas for the tables, then

pseudo-code:

a) Select first record in the data island containing fist formula
b) copy the object containg the selected formula to excel
c) select the second record in the data island contaning the second formula
d) copy the object containg the selected formula to excel
e) and so on …

2) Second possibility would be to change the expression/formula by macro.
This would result into the following pseudo code

a) Set the expression to formula A
b) copy the object to Excel containing formula A
c) change the expression to formula B
d) copy the object to Excel containing formula B
e) and so on …

Hope this helps …

Regards
Stefan

Regards
Stefan

We´re experiencing a little problem here.

When I use my QV for developing, your code works fine.

But when we deploy the application to our server, we´re getting this error message:
“Macro parse failed.
exportToExcel_variant1
Error: ActiveX component can’t create object: ‘Excel.Application’

Any ideas how to solve this?

Like mallikarjun I am aslo getting “Type mismatch: ‘copyObjectsToExcelSheet’” on the line:
“Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)” when I debug. I am using the code as follows. Let me know if you need more information. Thanks – Tony

Dim aryExport(3,3)  

aryExport(0,0) = "objCH01"
aryExport(0,1) = "LOB Charts"
aryExport(0,2) = "A1"
aryExport(0,3) = "image" 

aryExport(1,0) = "objCH03"
aryExport(1,1) = "LOB Charts"
aryExport(1,2) = "H1"
aryExport(1,3) = "image" 

aryExport(2,0) = "objTB01"
aryExport(2,1) = "Functional View"
aryExport(2,2) = "A1"
aryExport(2,3) = "data" 

aryExport(3,0) = "objCH02"
aryExport(3,1) = "Functional View"
aryExport(3,2) = "H1"
aryExport(3,3) = "image" 

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

This is fantastic.
Is there a way to specify a field name that a Chart uses. I want to re-use the same chart on an excel sheet but based on specific field content. For example, I want to put 4 charts on one sheet, each chart refers to a specific KPI Name?

Regards
Stefan

Do you have a macro that does the same but for word?

Thanks!

K

hmmmm, interesting …
Could you send me an example, so I could try to reproduce this …

Regards
Stefan

I get an error on “Call objSource.CopyTableToClipboard(true) ‘// default & fallback” in the copyObjectsToExceSheet sub when trying to export multiple straight tables that resides in a container object.

If I try to export the first two tables it will successfully export the first one ONLY if that first table are active, and it creates the second sheet in excel. If the second table are active, it fails before et manages to export the first one.

Hope you understand what I am trying to explain.

As a newcomer to Qlikview I wanted to thank you for this little tool.
Makes life so much easier!

Regards….

Date(MonthEnd(JoinDate, -6), ‘MMM YYYY’).
I have 7 columns(Expressions) like this. When I export tables to Excel the date format is been changed as ‘MM-YYYY’. But I need to retain the actual format.

I tried using the Text method but the result is same.

Text(Date(MonthEnd(SelectedDate,-6), ‘MMM YYYY’))

Could you please let me know Is there any option to retain the date format when we export to Excel.

Regards,
jagan.

Thank you so much for this! I’m not a developer yet I was able to follow these instructions and transfer multiple objects to different sheet in excel.

I was wondering if you have anything similar for copying multiple items into word?

Many thanks

Ciara

Dim aryExport(2,3)

line 270 –> objNewSheet.Name = left(sheetName,31)

The stupid thing is that it works when I run it via your .qvw, but my .qvw it actually creates the document but comes up with this error while trying to remove the unused tabs. I’m running Excel 2010. Is there some setting you changed in your document properties? This is the only thing I can think of. Thanks again!

- Create three tables with dynamic expressions/dimensions like

//First table
if(Location = 'A', Location)
// Then use the expression price

// Second table
if(Location = 'B', Location)
// Then use the expression price

// Third table
if(Location = 'C', Location)
// Then use the expression price

Then you could the code as delivered out of the box with the three different objects.
… But maybe it is easier to write some custom code based on my code (really depending on how easy it is for your to write some macro code)

Hope this helps a little bit!
Regards
Stefan

could you please explain in detail what you are trying to achieve …

Thanks!
Regards
Stefan

I have just checked the link of the download at the end of the article and it works … could you please try it again, thanks!

Regards
Stefan

Your article is great!!!!
I am new making this and need more help, How can make it in a existing excel? this is the template. I tried several options but nothing works.

THANKS!!!!!

im working in alikview with data in sql server (longitude and latitude) and i want represent in google earth inside qlikview .

is there way to do that by kml file google maps.

i need the step how do that with script code .

thank you for support .

first of all thanks for your app.

I have a question: is it possible using your script do iterate on some field and export in excel?

I mean, i have a table like this:

LOCATION price
C 1
A 4
A 9
B 3
B 4
A 5
C 2

is it possible with some modify to your script to have as output 3 sheet of same document having following structure?

Sheet A
A 4
A 5
A 9
Sheet B
B 3
B 4
Sheet C
C 1
C 2

So, i need select field Location and iterate on it in the script, but i don’t find anything which help me.
thanks

Regards
Stefan

Load * Inline [
RIalGroup,Code
'Lower than 1',1
'between 1 and 2',2
'between 3 and 4',3
]

load
RIalGroup=case when (count=1 and count =2 and count<3) then'3'
end,
@1,
@2,
@3
from Azadeh.qvd (qvd);

Thank you for your attention

thank you for your solution … I hope I will have enough time this week to compare all these solutions

Regards
Stefan

Just to point out that I have also seen this issue in QV 10 SR 1. It caused a bit of head scratching the first time it reared it’s head! I presume it is only an issue with documents migrated from v9?

Cheers,
Steve

yes it should be really easy … all your tasks should be available again on SR2 again because both SR1 and SR2 are using the same repository …

But do not forget to backup the repository before updating

Regards
Stefan

thanks for adding that!

Stefan

Sorry for my poor english…
I would like how to migrate QlikView Server 10 SR1 to SR2 ? Is it very easy ? What’s happens with my tasks ?
Thanks for advance,

Pierrick

Nice series!