Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Embed a Dynamic Qlik Sense Visualization in MS Excel

isaaclin
New Contributor III

Embed a Dynamic Qlik Sense Visualization in MS Excel

I will use Qlik Sense Desktop version for this demo, but I am sure you can also use Enterprise version if you have the authorization to the Dev Hub.

The first step, open an excel file where you want your qlik visualization to be, make sure saved it as xlsm format (Macro Enabled file)

Step2, ALT + F11 to open Developer window in excel, and create a user form. Pick the Web Browser part from Toolbox and reshape the size to fit your user form

Capture.JPG

Step3, Go to  Dev Hub in QLik Sense

Capture.JPG

then go to Single Configurator, pick the App and the Sheet, then the URL will be generated in the box, Copy the URL and paste to a cell in the excel file.

Capture.JPG

Step4, Build some buttons in excel like below, the up and down buttons are controlling 12 period ,and 5 weeks(most periods have 4 weeks, some have 5 weeks)

Capture.JPG

Step5, Change the Cell where stores your URL info into a formula like below. the cell B2 and G2 refer to the period number and week number.

="http://localhost:4848/single?appid=C%3A%5CUsers%5Cilin%5CDocuments%5CQlik%5CSense%5CApps%5CCorperate...,"&Sheet1!B2&"&select=PdWeek,"&Sheet1!G2

Step6, return to VBA developer window, and paste the code below to Sheet1

Private Sub CommandButton2_Click()

Unload UserForm1

UserForm1.Show False

End Sub

and then go to the Userform code section, and paste the code below.

Private Sub UserForm_Initialize()

Me.StartUpPosition = 0

    Me.Top = Application.Top + 25

    Me.Left = Application.Left + Application.Width - Me.Width - 25

Me.WebBrowser1.Navigate (Sheet2.Range("A1"))

End Sub

Step7, Test your result. Basically, when you spin the button changing period and week number, the visualizations changes accordingly.

below is the screen I have on my side, it is fully interactive with Qlik. you see the filters update with my selections in excel.

Capture.JPG

I tested this method with enterprise version, and it worked too, but you need to key in user name and password to be able show the result, you are only required to key in credential once.

Above is the thought on embedding analytics to other tools, if you have different thoughts and comments, please share with me as well.

Many thanks

Isaac

Comments
nelrhaffouli
New Contributor

Hello Isaac,

Thank you for this very interesting and useful article. I tried to follow the instructions but didn't succeed.

I actually didn't know how to achieve step 4.

Would there be any other instructions that would help me achieve this step?

Thanks

Best regards

Naoual

nelrhaffouli
New Contributor

Also could you please let me know if the person I'm sharing the visualization with needs to have Qlik access?

My audience don't know and don't use Qlik, however they are interested by the visualizations

Thank you so much

isaaclin
New Contributor III

Hello Naoual

the users still need Qlik access to view the contents, unless the Qlik access is using the windows credential.

Thanks

Isaac

isaaclin
New Contributor III

Hello please check the video for step 4, I will try to build more videos about next steps. may take me some time to.

nelrhaffouli
New Contributor

Thank you very much Issac

I shared the link with one of my colleagues, but he was unable to login even with his windows credentials.

Would you have any hint about how to overcome this issue?

Thanks a lot for your help,

Version history
Revision #:
1 of 1
Last update:
‎06-01-2016 01:44 PM
Updated by: